Advanced Excel Introduction

On this Introduction to Microsoft Excel training course, students will learn how to create and edit basic worksheets and workbooks. This course is designed for students who want to gain the necessary skills to create, edit, format, and print basic Microsoft Excel 2016 worksheets.

Microsoft Excel is one of the most used software applications of all time. Hundreds of millions of people around the world use Microsoft Excel. You can use Excel to enter all sorts of data and perform financial, mathematical or statistical calculations. 1 Range: A range in Excel is a collection of two or more cells. This chapter gives an. The list below is a collection of advanced Excel tips to make you an Excel superhero. Advanced Excel Formulas. Formulas are what drive an Excel spreadsheet. Formulas provide the muscle for a spreadsheet to analyse, manipulate, convert and look up data with incredible speed and power. An advanced Excel user would need advanced formula skills.

Target Audience: Students who have little or no familiarity with Microsoft Excel or more experienced Excel users who want to learn the topics covered in this course in the new interface.

The Microsoft Excel Course: Advanced Excel Training: You’re here because you want to build sophisticated financial models and learn Excel. And you’re here because you want to take your Excel skills to the next level. Well then, you’ve come to the right place. Welcome to our Advanced Excel course!

Microsoft Excel Introduction – Objectives

  • Create basic worksheets using Microsoft Excel 2016.
  • Perform calculations in an Excel worksheet.
  • Modify an Excel worksheet.
  • Modify the appearance of data within a worksheet.
  • Manage Excel workbooks.
  • Print the content of an Excel worksheet.
  • Learn how to use handy features new in 2016.

Outline

  1. Creating a Microsoft Excel Workbook
    1. Starting Microsoft Excel
    2. Creating a Workbook
    3. Saving a Workbook
    4. The Status Bar
    5. Adding and Deleting Worksheets
    6. Copying and Moving Worksheets
    7. Changing the Order of Worksheets
    8. Splitting the Worksheet Window
    9. Closing a Workbook
    10. Exercise: Creating a Microsoft Excel Workbook
  2. The Ribbon
    1. Tabs
    2. Groups
    3. Commands
    4. Exercise: Exploring the Ribbon
  3. The Backstage View (The File Menu)
    1. Introduction to the Backstage View
    2. Opening a Workbook
    3. Exercise: Open a Workbook
    4. New Workbooks and Excel Templates
    5. Exercise: Select, Open and Save a Template Agenda
    6. Printing Worksheets
    7. Exercise: Print a Worksheet
    8. Adding Your Name to Microsoft Excel
    9. Managing Workbook Versions
  4. The Quick Access Toolbar
    1. Adding Common Commands
    2. Adding Additional Commands with the Customize Dialog Box
    3. Adding Ribbon Commands or Groups
    4. Placement
    5. Exercise: Customize the Quick Access Toolbar
  5. Entering Data in Microsoft Excel Worksheets
    1. Entering Text
    2. Expand Data across Columns
    3. Adding and Deleting Cells
    4. Adding an Outline
    5. Adding a Hyperlink
    6. Add WordArt to a Worksheet
    7. Using AutoComplete
    8. Exercise: Entering Text and Using AutoComplete
    9. Entering Numbers and Dates
    10. Using the Fill Handle
    11. Exercise: Entering Numbers and Dates
  6. Formatting Microsoft Excel Worksheets
    1. Selecting Ranges of Cells
    2. Hiding Worksheets
    3. Adding Color to Worksheet Tabs
    4. Adding Themes to Workbooks
    5. Exercise: Customize a Workbook Using Tab Colors and Themes
    6. Adding a Watermark
    7. The Font Group
    8. Exercise: Working with Font Group Commands
    9. The Alignment Group
    10. Exercise: Working with Alignment Group Commands
    11. The Number Group
    12. Exercise: Working with Number Group Commands
  7. Using Formulas in Microsoft Excel
    1. Math Operators and the Order of Operations
    2. Entering Formulas
    3. AutoSum (and Other Common Auto-Formulas)
    4. Copying Formulas and Functions
      1. Displaying Formulas
    5. Relative, Absolute, and Mixed Cell References
    6. Exercise: Working with Formulas
  8. Working with Rows and Columns
    1. Inserting Rows and Columns
    2. Deleting Rows and Columns
    3. Transposing Rows and Columns
    4. Setting Row Height and Column Width
    5. Hiding and Unhiding Rows and Columns
    6. Freezing Panes
    7. Exercise: Working with Rows and Columns
  9. Editing Worksheets
    1. Find
    2. Find and Replace
    3. Exercise: Using Find and Replace
    4. Managing Comments
      1. Adding Comments
      2. Working with Comments
  10. Finalizing Microsoft Excel Worksheets
    1. Setting Margins
    2. Setting Page Orientation
    3. Setting the Print Area
    4. Print Scaling (Fit Sheet on One Page)
    5. Printing Headings on Each Page/Repeating Headers and Footers
    6. Headers and Footers
  11. Microsoft Excel Features that Were New in 2013
    1. One Workbook Per Window Feature
    2. Using Flash Fill
    3. Exercise: Using Flash Fill
  12. Features New in 2016
    1. Tell Me
    2. Smart Lookup

Scheduled courses are held regularly in Glasgow & Edinburgh, on-site courses by arrangement across Scotland.

See our upcoming schedule for this course in Glasgow or Edinburgh

This Intermediate Microsoft Excel training course is meant for students who want to advance their skill set by learning to work with advanced formulas, lists, and illustrations. Students will also work with charts and advanced formatting including styles.

Target Audience: Students who have basic skills with Microsoft Excel who want to learn intermediate-level skills or students who want to learn the topics covered in this course in the new interface.

Microsoft Excel Intermediate – Objectives

  • Learn to use formulas and functions.
  • Create and modify charts.
  • Convert, sort, filter, and manage lists.
  • Insert and modify illustrations in a worksheet.
  • Learn to work with tables.
  • Learn to use conditional formatting and styles.

Class Outline

  1. Advanced Formulas
    1. Using Named Ranges in Formulas
      1. Naming a Single Cell
      2. Naming a Range of Cells
      3. Naming Multiple Single Cells Quickly
    2. Exercise: Using Named Ranges in Formulas
    3. Using Formulas That Span Multiple Worksheets
    4. Exercise: Entering a Formula Using Data in Multiple Worksheets
    5. Using the IF Function
      1. Using AND/OR Functions
      2. Using the SUMIF, AVERAGEIF, and COUNTIF Functions
    6. Exercise: Using the IF Function
    7. Using the PMT Function
    8. Exercise: Using the PMT Function
    9. Using the LOOKUP Function
    10. Using the VLOOKUP Function
    11. Exercise: Using the VLOOKUP Function
    12. Using the HLOOKUP Function
    13. Using the CONCATENATE Function
    14. Exercise: Using the CONCATENATE Function
    15. Using the TRANSPOSE Function
    16. Using the PROPER, UPPER, and LOWER Functions
      1. The UPPER Function
      2. The LOWER function
      3. The TRIM Function
    17. Exercise: Using the PROPER Function
    18. Using the LEFT, RIGHT, and MID Functions
      1. The MID Function
    19. Exercise: Using the LEFT and RIGHT Functions
    20. Using Date Functions
      1. Using the NOW and TODAY Functions
    21. Exercise: Using the YEAR, MONTH, and DAY Functions
    22. Creating Scenarios
      1. Utilize the Watch Window
      2. Consolidate Data
      3. Enable Iterative Calculations
      4. What-If Analyses
      5. Use the Scenario Manager
      6. Use Financial Functions
  2. Working with Lists
    1. Converting a List to a Table
    2. Exercise: Converting a List to a Table
    3. Removing Duplicates from a List
    4. Exercise: Removing Duplicates from a List
    5. Sorting Data in a List
    6. Exercise: Sorting Data in a List
    7. Filtering Data in a List
    8. Exercise: Filtering Data in a List
    9. Adding Subtotals to a List
      1. Grouping and Ungrouping Data in a List
    10. Exercise: Adding Subtotals to a List
  3. Working with Illustrations
    1. Working with Clip Art
    2. Exercise: Working with Clip Art
    3. Using Shapes
    4. Exercise: Adding Shapes
    5. Working with SmartArt
  4. Visualizing Your Data
    1. Creating a Custom Chart Template
    2. Inserting Charts
    3. Exercise: Inserting Charts
    4. Exercise: Editing Charts
    5. Add and Format Objects
    6. Insert a Text Box
    7. Create a Custom Chart Template
  5. Working with Tables
    1. Format Data as a Table
    2. Move between Tables and Ranges
    3. Modify Tables
      1. Add and Remove Cells within a Table
    4. Define Titles
      1. Band Rows and Columns
      2. Remove Styles from Tables
    5. Exercise: Creating and Modifying a Table in Excel
  6. Advanced Formatting
    1. Applying Conditional Formatting
    2. Exercise: Using Conditional Formatting
    3. Working with Styles
      1. Applying Styles to Tables
      2. Applying Styles to Cells
    4. Exercise: Working with Styles
    5. Creating and Modifying Templates
      1. Modify a Custom Template
  7. Microsoft Excel Features that Were New in 2013
    1. New Functions in Excel 2013
    2. Exercise: Using the New Excel Functions
    3. Using New Chart Tools
    4. Exercise: Using the New Excel 2013 Chart Tools
    5. Using the Quick Analysis Tool
    6. Exercise: Using the Quick Analysis Tool
    7. Using the Chart Recommendation Feature
  8. Features New in 2016
    1. New Charts
      1. Treemap
      2. Sunburst
      3. Histogram
      4. Waterfall
      5. Box and Whisker
      6. Pareto

Scheduled courses are held regularly in Glasgow & Edinburgh, on-site courses by arrangement across Scotland.

See our upcoming schedule for this course in Glasgow or Edinburgh

This Advanced Microsoft Excel training course is designed for students to gain the skills necessary to use pivot tables, audit and analyze worksheet data, utilize data tools, collaborate with others, and create and manage macros.

Target Audience: Students who have intermediate skills with Microsoft Excel who want to learn more advanced skills or students who want to learn the topics covered in this course in the new interface.

Microsoft Excel Advanced – Objectives

  • Create pivot tables and charts.
  • Learn to trace precedents and dependents.
  • Convert text and validate and consolidate data.
  • Collaborate with others by protecting worksheets and workbooks.
  • Create, use, edit, and manage macros.
  • Import and export data.

Class Outline

  1. Using Pivot Tables
    1. Creating Pivot Tables
      1. Preparing Your Data
      2. Inserting a Pivot Table
    2. Inserting Slicers
    3. Working with Pivot Tables
      1. Grouping Data
      2. Using PowerPivot
      3. Managing Relationships
    4. Inserting Pivot Charts
    5. More Pivot Table Functionality
    6. Exercise: Working with Pivot Tables
  2. Auditing Worksheets
    1. Tracing Precedents
    2. Exercise: Tracing Precedents
    3. Tracing Dependents
    4. Exercise: Tracing Dependents
    5. Showing Formulas
  3. Data Tools
    1. Converting Text to Columns
    2. Exercise: Converting Text to Columns
    3. Linking to External Data
    4. Controlling Calculation Options
    5. Data Validation
    6. Exercise: Using Data Validation
    7. Consolidating Data
    8. Exercise: Consolidating Data
    9. Goal Seek
    10. Exercise: Using Goal Seek
  4. Working with Others
    1. Protecting Worksheets and Workbooks
      1. Password Protecting a Workbook
      2. Removing Workbook Metadata
    2. Exercise: Password Protecting a Workbook
      1. Password Protecting a Worksheet
    3. Exercise: Password Protecting a Worksheet
      1. Password Protecting Ranges in a Worksheet
    4. Exercise: Password Protecting Ranges in a Worksheet
    5. Tracking Changes
      1. Accepting or Rejecting Changes
      2. Setting Tracking Options
      3. Merging Multiple Workbooks
    6. Marking a Workbook as Final
    7. Exercise: Tracking Changes
  5. Recording and Using Macros
    1. Recording Macros
      1. Copy a Macro from Workbook to Workbook
    2. Exercise: Recording a Macro
    3. Running Macros
    4. Editing Macros
    5. Adding Macros to the Quick Access Toolbar
      1. Managing Macro Security
    6. Exercise: Adding a Macro to the Quick Access Toolbar
  6. Random Useful Items
    1. Sparklines
      1. Inserting Sparklines
      2. Customizing Sparklines
    2. Exercise: Inserting and Customizing Sparklines
    3. Preparing a Workbook for Internationalization and Accessibility
      1. Display Data in Multiple International Formats
      2. Modify Worksheets for Use with Accessibility Tools
      3. Use International Symbols
      4. Manage Multiple Options for +Body and +Heading Fonts
    4. Importing and Exporting Files
      1. Importing Text Files
    5. Exercise: Importing Text Files
      1. Exporting Worksheet Data to Microsoft Word
    6. Exercise: Copying Data from Excel to Word
      1. Exporting Excel Charts to Microsoft Word
    7. Exercise: Copying Charts from Excel to Word
  7. Microsoft Excel Features that Were New in 2013
    1. Using Slicers to Filter Data
    2. Exercise: Filtering Data with Slicers
    3. Creating a PivotTable Timeline
    4. Exercise: Creating a Timeline
    5. Creating a Standalone PivotChart
    6. Workspaces in Excel 2013
  8. Features New in 2016
    1. PivotTable Updates
    2. Ink Equations
    3. Multi-Select Option in Slicers
    4. Quick Shape Formatting
    5. Sharing with SharePoint or OneDrive

Scheduled courses are held regularly in Glasgow & Edinburgh, on-site courses by arrangement across Scotland.

See our upcoming schedule for this course in Glasgow or Edinburgh

This Introduction to Excel VBA training course covers:

Introduction to Macros

  • What is a Macro?
  • Recording a Macro
  • Running a Macro
  • Relative References
  • Assigning a Macro to a Button on a Worksheet
  • Assigning a Macro to a Button on a Toolbar
  • Button Image Editor

Editing Macros

  • The Visual Basic Editor
  • The Project Explorer
  • The Code Window
  • Procedures
  • Inserting Comments
  • Printing a Visual Basic Module

The Range Object

  • What is the Range Object?
  • The Cells Property
  • The Range Property
  • Flexible Ranges
  • Range Contents

Workbooks and Sheets

  • Activating a Workbook
  • Activating a Worksheet

Debugging and Error Trapping

  • What are Errors?
  • Types of Error
  • Break Mode
  • Instant Watch

Objects, Properties and Methods

  • Controlling Objects with their Properties
  • Performing Actions with Methods
  • Performing Multiple Actions on an Object

Variables and Constants Control Structures

  • Conditional Control Structures
  • Looping Control Structures
  • Non-Structured Statements
  • Subprocedures

Controls, Dialogue Boxes and Forms

  • Built-In Dialogue Boxes
  • Predefined Dialogue Boxes
  • Toolbars and Display

Scheduled courses are held regularly in Glasgow & Edinburgh, on-site courses by arrangement across Scotland.

See our upcoming schedule for this course in Glasgow or Edinburgh

This full two day training course covers:

Introduction to Macros

  • What is a Macro?
  • Recording a Macro
  • Running a Macro
  • Relative References
  • Assigning a Macro to a Button on a Worksheet
  • Assigning a Macro to a Button on a Toolbar
  • Button Image Editor

Editing Macros

  • The Visual Basic Editor
  • The Project Explorer
  • The Code Window
  • Procedures
  • Inserting Comments
  • Printing a Visual Basic Module

The Range Object

Advanced
  • What is the Range Object?
  • The Cells Property
  • The Range Property
  • Flexible Ranges
  • Range Contents

Workbooks and Sheets

  • Activating a Workbook
  • Activating a Worksheet
  • ThisWorkBook
  • Calling Worksheet Events
  • Calling Workbook Events

Debugging and Error Trapping

  • What are Errors?
  • Types of Error
  • Break Mode
  • Instant Watch
  • Stepping Through Code
  • Error Handling Worksheet and Workbook Events
  • Creating Line Labels for Error Handling
  • Writing Error Handling Code to Trap Errors
  • Error Handling to Resolve Errors
  • Resuming after Resolved Errors

Objects, Properties and Methods

  • Controlling Objects with their Properties
  • Performing Actions with Methods
  • Performing Multiple Actions on an Object

Variables and Constants Control Structures

  • Conditional Control Structures
  • Looping Control Structures
  • Non-Structured Statements
  • Subprocedures

Controls, Dialogue Boxes and Forms

  • Built-In Dialogue Boxes
  • Predefined Dialogue Boxes
  • Custom Forms
  • Worksheet Forms
  • Setting the Properties of a Control
  • User-Defined Forms
  • Adding Form Controls
  • Form Control Properties
  • Control and Dialogue Box Events
  • Displaying and Dismissing a User Form
  • Handling Form Controls

Toolbars and Display

  • Customising the Quick Access Toolbar
  • Customising the Ribbon
  • Using Worksheet Buttons

Function Procedures

  • Function Procedures
  • User-Defined Functions
  • Function Wizard
  • Excel Functions

Add-in Applications

Advanced Excel Introduction Examples

  • What is an Add-in?
  • Creating an Add-In
  • Working with Excel Add-Ins

Ms Excel Introduction

Scheduled courses are held regularly in Glasgow & Edinburgh, on-site courses by arrangement across Scotland.

Advanced Excel Introduction Tutorial

See our upcoming schedule for this course in Glasgow or Edinburgh

⇐ ⇐ Playstation Live Support
⇒ ⇒ Format Sd Exfat