Excel Training: Advanced Schedule
We can also perform this training at your location or at one of the cities listed below:
Excel Training: Advanced Overview
This advanced level class focuses on how to create macros, collaborate with others, audit and analyze worksheet data, incorporate multiple data sources, and import and export data.
Excel Training: Advanced Course Prerequisites
- Have attended the Excel Intermediate course, or have equivalent knowledge of the topics covered in the Excel Intermediate course.
Excel Training: Advanced Course Outline
-
Unit 1: Advanced functions
-
Topic A: Logical functions
- Using the IF function
- Using OR, AND, and NOT as nested functions
- Using nested IF functions
- Using the IFERROR function
-
Topic B: Math and statistical functions
- Using SUMIF
- Using COUNTIF
- Using AVERAGEIF
- Using SUMIFS, COUNTIFS, and AVERAGEIFS
- Using ROUND
-
Topic C: Financial functions
- Using the PMT function
-
Topic D: Displaying and printing formulas
- Showing, printing, and hiding formulas
-
Topic A: Logical functions
-
Unit 2: Lookups and data tables
-
Topic A: Using lookup functions
- Examining VLOOKUP
- Using VLOOKUP to find an exact match
- Using VLOOKUP to find an approximate match
- Using HLOOKUP for exact matches
- Using HLOOKUP for approximate matches
-
Topic B: Using MATCH and INDEX
- Using the MATCH function
- Using the INDEX function
-
Topic C: Creating data tables
- Creating a one-variable data table
- Creating a two-variable data table
-
Topic A: Using lookup functions
-
Unit 3: Advanced list management
-
Topic A: Validating cell entries
- Observing data validation
- Setting up data validation
-
Topic B: Exploring database functions
- Examining the structure of database functions
- Using the DSUM function
-
Topic A: Validating cell entries
-
Unit 4: PivotTables and PivotCharts
-
Topic A: Working with PivotTables
- Creating a PivotTable
- Adding fields to a PivotTable
-
Topic B: Rearranging PivotTables
- Moving fields
- Hiding and showing details
- Refreshing the data in a PivotTable
-
Topic C: Formatting PivotTables
- Formatting by using a Pivot style
- Changing field settings
-
Topic D: PivotCharts
- Creating a PivotChart
-
Topic A: Working with PivotTables
-
Unit 5: Exporting and importing
-
Topic A: Exporting and importing text files
- Exporting Excel data to a text file
- Importing data from a text file into a workbook
- Converting text to columns
- Removing duplicate records
-
Topic B: Exporting and importing XML data
- Using the XML Source task pane
- Importing XML data into a workbook
- Exporting data from a workbook to an XML data file
- Deleting an XML map
-
Topic C: Querying external databases
- Using Microsoft Query to get data from an external database
- Discussing the Web query feature
- Using a Web query to get data from the Web
-
Topic A: Exporting and importing text files
-
Unit 6: Analytical options
-
Topic A: Goal Seek and Solver
- Using Goal Seek to solve for a single variable
- Installing Solver and the Analysis ToolPak
- Using Solver to solve for multiple variables
-
Topic B: The Analysis ToolPak
- Using the Sampling analysis tool
-
Topic C: Scenarios
- Creating scenarios
- Switching among scenarios
- Merging scenarios from another worksheet
-
Topic D: Views
- Creating views
- Switching among views
-
Topic A: Goal Seek and Solver
-
Unit 7: Macros and custom functions
-
Topic A: Running and recording a macro
- Running a macro
- Recording a macro
- Assigning a macro to a button
-
Topic B: Working with VBA code
- Observing a VBA code module
- Editing VBA code
-
Topic C: Creating functions
- Creating a custom function
-
Topic A: Running and recording a macro
-
Unit 8: Conditional formatting and SmartArt
-
Topic A: Conditional formatting with graphics
- Creating data bars
- Using color scales
- Creating icon sets
-
Topic B: SmartArt graphics
- Inserting a SmartArt graphic
- Modifying a SmartArt graphic
-
Topic A: Conditional formatting with graphics