This class covers advanced concepts in Microsoft Excel and is designed for those who are already comfortable with different types of formulas, formatting spreadsheets and text, and working with multiple worksheets. (One day, 6-hour class.)
The Excel 2013 Advanced class will propel you to the next level of Excel power users and increase the value you bring to your organization by enhancing your knowledge and broadening your understanding of advanced Excel functionalities.
Objectives
Students will exit this class feeling confident in their abilities to use many of the advanced features within Microsoft Excel, including nested functions, VLookups, HLookups, IF statements, PivotTables, Charts, and text manipulations.
Prerequisites
Students should have taken Excel 2013 Intermediate or possess equivalent skills. Must be familiar with functions, named ranges, charting, and working with multiple worksheets. Understanding of simple database tables is a plus.
- One day, 6-hour class
$429$379 ($50 discount)
Topics
Advanced Formulas and Functions
- Working with Functions
- Using the IF Function (and Other Related Functions)
- Using Table Lookup Functions (HLookup and VLookup)
- Using Text Functions and Concatenation
- Nested Functions
- Named Ranges and Defined Names (Scope)
- Data Transposition and Arrays
Analyzing Data
- Using Automatic Outlining
- Consolidation
- Inserting Subtotals
- Creating an Advanced Filter
- Using Database Functions
Working with Data (Local and External)
- Importing Data from a Text File
- Exporting Data
- Converting Text to Columns
- Using Flash Fill
- Retrieving Data from a Database
- Creating a Web Query
- Working with Hypertext Links
Creating and Working with PivotTables
- Understanding PivotTables
- Creating a PivotTable using Worksheet Data
- Creating a PivotTable using External Data
- Laying out a PivotTable on a Worksheet
- Modifying PivotTable Fields
- Using a Report Filter
- Refreshing a PivotTable
- Formatting a PivotTable
Enhanced PivotTables
- Working with Summary Functions
- Sorting Items in a PivotTable
- Creating a Slicer
- Grouping Data
- Applying Label and Value Filters
- Creating a Calculated Field
- Creating a Calculated Item
- Creating Charts from PivotTables
Introduction to VBA (Macros)
- Recording a Macro
- Saving a Macro-Enabled Workbook
- Executing a Macro
- Creating a Macro Button
- Working with Macro Buttons
Advanced Charting Techniques
- Multiple Series Charts
- Chart Repositioning
- Chart Layout (Title, Labels, etc.)
- Chart Backgrounds
