Microsoft Excel Level 3 (Advanced)

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

Visual Basic for Application

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