Register Now      202-747-5000

Advanced Microsoft Excel

365 | 2019 | 2016
1 Day, 9:00-4:00

Course Overview

This instructor-led course, through hands-on exploration, will teach students the skills needed to work with large lists of data, PivotTables, outlining, named ranges, conditional formatting, macros, data validation, and protection. Students will also learn how to write additional formulas that use the VLOOKUP function.

Course Objectives

  • Work with larges lists of data, apply filters, and use tables
  • Create PivotTables to analyze and summarize data
  • Perform What-If Analysis on a range of data
  • Automate tasks with conditional formatting and macros
  • Use the VLOOKUP function to locate values from a table or range
  • Apply data validation and protection

Course Outline

Working with Large Lists of Data

  • Design Concepts for Structuring Large Lists
  • Header Row (Fields)
  • Data Range (Records)
  • Filtering Lists
  • Applying Filters
  • Applying Top 10 and Custom Filters
  • Working with Advanced Filters
  • Criteria Range
  • Applying Advanced Filters
  • Converting Lists of Data to Excel Tables
  • Creating and Naming Tables
  • Using Table Elements and Features

Creating PivotTables to Analyze Data

  • Creating PivotTables
  • Adding and Removing Fields
  • Adjusting the PivotTable Field List View
  • Grouping Data and Rearranging Fields
  • Using Report Filters
  • Changing Value Field Settings
  • Refreshing Data in PivotTables
  • Adding Slicers to Visually Filter
  • Creating PivotCharts from PivotTables

Using Data Analysis Tools

  • Creating Scenarios to Perform What-if Analysis
  • Showing and Editing Scenarios
  • Summarizing Scenarios
  • Using the Subtotal Feature
  • Outlining Worksheets
  • Creating Manual and Automatic Outlines
  • Showing or Hiding Details
  • Ungrouping Columns and Rows
  • Working with Custom Views
  • Creating Custom Views
  • Showing Custom Views
  • Deleting Custom Views

Automating Tasks with Conditional Formatting and Macros

  • Applying Conditional Formatting
  • Applying Preset Conditional Formatting Rules
  • Editing Conditional Formatting Rules
  • Applying Conditional Formatting to Rows
  • Creating Macros Using the Macro Recorder
  • Using Relative vs. Absolute References
  • Running Macros
  • Editing Macros
  • Deleting Macros
  • Adding Macro Buttons to the Quick Access Toolbar

Using VLOOKUP to Locate Values

  • Examining the VLOOKUP Function Syntax
  • Creating Lookup Tables
  • Creating Named Ranges
  • Writing VLOOKUP Formulas
  • Understanding Common Formula Errors
  • Using the IFERROR Function to Hide Errors

Setting Data Validation and Protection

  • Validating Data Entry
  • Applying Data Validation
  • Testing Data Validation
  • Understanding Worksheet Protection
  • Unlocking Cells
  • Protecting Worksheets
  • Unprotecting Worksheets



Public Course at Softek Private Course at Your Office Private Course at Softek
$275 per student $850 flat fee up to 8 students $1000 flat fee up to 8 students
or 1 voucher $950 flat fee up to 12 students $1100 flat fee up to 12 students
10% discount when 3+ attend $50 per additional student $50 per additional student

Private Group Training

Private group courses are cost effective when you have a group of students who need the same course. Pricing is based on the maximum number of students anticipated per day per course. Volume discounts of 5% to 20% are available when five or more training days are scheduled. For classes conducted at your office, you are responsible for the classroom setup, including student computers, instructor station, LCD projector, and software installation. For courses conducted at Softek, we take care of classroom setup.


Softek can easily customize a training course that covers the specific topics you require and integrate examples relevant to your work environment. Course customization is billed at $125 per hour and covers time needed to meet, discuss, prepare, and incorporate any specific examples into the training program. Once we have a better idea of what you have in mind, we will be glad to provide a specific price quote for the customization. There is a 2-hour minimum fee for customization.

Why Choose Softek?

  • Live, in-person training
  • Instructors with 10+ years of experience
  • Detailed training guide
  • Certificate of completion
  • Money-back guarantee