Register Now      202-747-5000
Road Map To Learning

Choose Your Learning Adventure:

Select from our standard “off-the-shelf” classes or build a course to target your learning goals. Ready to learn new skills? Contact Softek today!

Advanced Microsoft Excel

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, conditional formatting, macros, data validation, and worksheet protection. Students will also learn about the benefits of using dynamic arrays to perform automatic calculations and how to write dynamic array functions.

Course Objectives

  • Work with large lists of data, apply filters, and use tables
  • Create PivotTables to analyze and summarize data
  • Understand dynamic array functions
  • Automate tasks with conditional formatting and macros
  • Use the XLOOKUP function to search for values in a range of cells
  • Apply data validation and worksheet 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
  • Grouping Data and Rearranging Fields
  • Using Report Filters
  • Changing Value Field Settings
  • Refreshing Data in PivotTables

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

Working with Dynamic Array Functions

  • Understanding Arrays
  • Working with Dynamic Array Functions
  • Using the SEQUENCE Function
  • Using the XLOOKUP Function
  • Using the UNIQUE Function
  • Using the SORT Function
  • Using the FILTER Function

Setting Data Validation and Protection

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

Appendix: 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


Cost and Delivery Format Options

We offer the above listed class on our public schedule and as a private group class in the following delivery formats:

Public Class: Live Virtual
Per student $325
Private Group Class: Live Virtual
1-4 students $700
5-8 students $850
9-12 students* $950
*$50 per additional student
Private Group Class: At Your Location
1-4 students $950
5-8 students $1100
9-12 students* $1200
*$50 per additional student

Private Group Training

Private group classes are cost effective when you have a group of students who need the same course. Volume discounts of 5% to 20% are available when five or more training days are scheduled. For classes conducted at your location, you are responsible for the classroom setup which should include:

  • A computer for each student (with the appropriate software installed), and
  • An instructor workstation connected to a visual display (e.g., projector, smartboard, etc.).


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 $150 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.

Why Choose Softek?

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