Excel Advanced Essentials

This advanced Microsoft Excel training course includes information on:

• Advanced formulas and functions
• Named ranges
• Resolving formula errors
• Consolidating data, removing duplicates, configuring data validation, and transposing data
• Using outlining, grouping, and subtotal tools
• Working with scenarios
• Using data analysis tools (such as Solver and Goal Seek)
• Creating and using PivotTables and PivotCharts
• Using PowerPivot to integrate data from several different sources
• Recording, editing, and using macros

Course Outline

Module 1: Advanced Formula Tasks

• Understanding Relative and Absolute Cell References
• Using Multiple Cell References
• Using 3D References
• Using Array Formulas
• Review Questions

Module 2: Working with Named Ranges

• Understanding Named Ranges
• Defining Named Ranges
• Editing Named Ranges
• Deleting Named Ranges
• Using Named Ranges in Formulas
• Review Questions

Module 3: Using Advanced Functions

• Using the PMT Function
• Using the FV Function
• Using VLOOKUP and HLOOKUP Functions
• Using IF, AND, OR Functions
• Review Questions

Module 4: Resolving Formula Errors

• Tracing Formula Precedents and Dependents
• Showing Formulas
• Evaluating Formulas
• Setting Error Checking Options
• Using Error Option Buttons
• Running an Error Check
• Review Questions

Module 5: Managing Data

• Consolidating Information
• Removing Duplicates
• Configuring Data Validation
• Transposing Data
• Converting Text to Columns
• Review Questions

Module 6: Outlining and Grouping Data

• Outlining Data
• Showing and Hiding Outline Details
• Grouping Data
• Creating Subtotals
• Removing Outlining and Grouping
• Review Questions

Module 7: Working with Scenarios

• Creating Scenarios
• Loading Scenarios
• Merging Scenarios
• Editing Scenarios
• Creating a Scenario Summary Report
• Deleting Scenarios
• Review Questions

Module 8: Using Solver

• Setting up the Worksheet
• Running Solver
• Generating Reports and Scenarios with Solver
• Modifying Constraints
• Setting Solver Options
• Review Questions

Module 9: Analyzing Data

• Enabling the Analysis ToolPak
• Using Data Analysis Tools
• Using Goal Seek
• Using Data Tables
• Review Questions

Module 10: Advanced PivotTable Features

• Creating a Basic PivotTable
• Creating a Basic PivotChart
• Using the PivotTable Fields Pane
• Adding Calculated Fields
• Sorting Pivoted Data
• Filtering Pivoted Data
• Review Questions

Module 11: Using PowerPivot

• Enabling PowerPivot
• Importing Access Data
• Importing Excel Data
• Integrating Data with Relationships
• Creating a PivotTable with PowerPivot Data
• Review Questions

Module 12: Using Macros

• Recording a Macro
• Writing a Macro using the Visual Basic Editor
• Editing a Macro
• Running a Macro
• Review Questions