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, 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

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.).

Customization

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.