Excel Advanced Essentials
Course Code
EXL2013
Payment Options
Upfront & Payment Plans
Delivery
Online & Correspondence
Duration
25 Hours
Excel 2013 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
Are you interested in this course?