Microsoft Excel Level 3 (Advanced)
(2013, 2016, 2019, 2021, 365)

The Macros section introduces users to the VBA Editor by having them copy macros between workbooks. If you would like more thorough training in VBA we offer an Excel VBA Complete course.

Excel has many new features that can be used to quickly analyze large amounts of financial information. Power Pivot enables users to combine data from multiple sources; Power View allows users to generate professional looking maps that indicate the location of data. Excel’s use of the VBA language enables users to create simple yet effective commands that enhance functionality, improve performance and perform repetitive tasks for you.

In this course you will combine, analyze, and display data using Excel's powerful features, and learn the basics of how to use macros to further extend Excel's capabilities.

Section 1: Enhancing Workbooks

Section 1.1: Customizing Workbooks

Comments

Hyperlinks

Watermarks

Background Pictures

Section 1.2: Preparing a Workbook for Multiple Audiences

Add Alternative Text to Objects

Modify Worksheets Using the Accessibility Checker

Manage Fonts

Section 1.3: Creating and Using Templates

Create a Template

Modify a Template

Section 2: Working with Multiple Workbooks

Section 2.1: Consolidating Data

Data Consolidation

The Consolidate Dialog Box

Consolidation Functions

Section 2.2: Linking Cells in Workbooks

Understand External References

Link Individual Cells

Link Groups of Cells

Section 2.3: Merging Workbooks

The Compare and Merge Workbooks Feature

Removing Duplicates

Transposing Data

Section 3: Protecting Workbooks and Worksheets

Protecting a Workbook

Protecting a Worksheet

Unprotecting Workbooks and Worksheets

Section 4: Outlining, Grouping, and Subtotalling

Grouping & Outlining

Subtotalling

Section 5: Importing and Exporting Excel Data

Section 5.1: Exporting Excel Data

The Export Process

Section 5.2: Importing a Delimited Text File

The Import Process

The Get External Data Group

Delimited Text Files

Methods of Importing Text Files

Section 5.3: Integrating Excel Data with the Web

The File Publishing Process

Publish as Web Page Dialog Box

Section 5.4: Creating a Web Query

Web Queries

The New Web Query Dialog Box

Section 6: Analyzing Data with Pivot Tables, Slicers, and Pivot Charts

Section 6.1: Creating a Pivot Table

Pivot Tables

The Create Pivot Table Dialog Box

The Pivot Table Fields Pane

Summarize Data in a Pivot Table

The “Show Values As” Functionality of a Pivot Table

Create a Calculated Field

Refresh the Pivot Table Data

Add Style to a Pivot Table

Work with Subtotals and Grand Totals

Introduction to PowerPivot

Section 6.2: Filtering Pivot Table Data

Use the Field Headers

Use the Pivot Table Dialog Box

Use Slicers

Section 6.3: Analyzing Data with Pivot Charts

Create Pivot Charts

Filter with Pivot Charts

Section 7: Automating Worksheet Functionality

Section 7.1: Updating Workbook Properties

Workbook Properties

Section 7.2: Creating and Editing a Macro (Overview)

What Are Macros?

The Record Macro Dialog Box

Name Macros

Reviewing the Visual Basic for Applications Window

Copying Macros Between Workbooks

Macro Security Settings

Difference Between a Relative and Absolute Macro

Section 7.3: Applying Conditional Formatting

What Is Conditional Formatting?

The Conditional Formatting Rules Manager Dialog Box

Clear Rules

Section 7.4: Adding Data Validation Criteria

Data Validation

The Data Validation Dialog Box