Great Canadian Training Company color logo

Microsoft Excel Level 3 (Advanced)

Microsoft Excel Level 3 (Advanced)

(2013, 2016, 2019, 2021, 365)

Course Length:

6 hours (1 day)

Learning Objectives

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. You will:

 

  • Use Data Validation to restrict data entry and create drop-downs
  • Use outlining tools to group and subtotal your data
  • Protect worksheets and workbooks from accidental changes
  • Use conditional formatting to highlight cells based on rules
  • Analyze data with Pivot Tables
  • Record and delete macros
  • Link and consolidate data across multiple workbooks
  • Import and export data and run web queries

Target Student

This course is for users who are familiar with Excel and who wish to expand their knowledge and use of advanced data analysis and time-saving features.

Course Outline

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

A seal with “Guaranteed to Run” written on it. In the middle is a person giving a thumbs up, standing behind a checkmark

Microsoft Excel Level 3 (Advanced)

Upcoming Classes

All Public classes are held online
with a live instructor
Dates Price Qty
March 27, 2024
9:00 AM to 4:00 PM ET
$350.00 Online
April 24, 2024
9:00 AM to 4:00 PM ET
$350.00 Online
June 25, 2024
9:00 AM to 4:00 PM ET
$350.00 Online
July 22, 2024
9:00 AM to 4:00 PM ET
$350.00 Online
* Applicable taxes will be applied on checkout

I want to talk to someone about private or customized training


Have questions about this course? See below or reach out to our training coordinators.

Group of 4 or more? Consider private training

The Great Canadian Training & Consulting Company Advantage

When you train with us, your training will not just check a box. We make sure it is GREAT!

  • Experienced Trainers
  • Guaranteed to run classes
  • Learning Guarantee
  • Anytime after-training support for 30 days
  • Bilingual training, manuals & post-training support available

This course comes with

PDF Reference Guide

Take the Reference Guide anywhere and save the environment with an electronic copy of your training Reference Guide for use on any of your computers or devices


Use Your Own Files

Whether it's one-on-one or group training you can give us files to include in your training session and help you apply what you learn even more!


Printed Reference Guide

Instead of the PDF that comes with your course, purchase a printed paper Reference Guide for use during your class and future reference


Can I customize my course?
OF COURSE!

Tailored Training

Your Reference Guide will follow a standard course outline but your class can be tailored to focus on the subjects that are most important to you

Use Your Own Files

You can include your own files in your training session to make it more applicable to your day-to-day role.