Excel Advanced Training Overview
This online, hands-on Advanced Excel training course teaches attendees advanced Excel skills, including how to calculate, analyze, and automate data. Attendees learn how to make these tasks more efficient than if they had used traditional calculation methods.
Location and Pricing
This course is taught as a private, live online class for teams of 3 or more. All our courses are hands-on, instructor-led, and tailored to fit your group’s goals and needs. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for online corporate training, please contact us.
In addition, some courses are available as live, instructor-led training from one of our partners.
Objectives
- Create 3D formulas for linking worksheets
- Outline goals with conditional formatting
- Automate data
- Create aggregate calculations
- Link worksheets via lookups
- Organize data to create summary reports
Prerequisites
All attendees must have prior knowledge of Excel basic formulas.
Outline
Expand All | Collapse All
Introduction to Formula Linking
- What is formula linking?
- Setting up formula linking
- Why use formula linking?
Formula Linking
- Linking spreadsheets (traditional)
- Linking spreadsheets (3D formulas)
- Auditing linked spreadsheets
Introduction to Conditional Formatting
- Why use conditional formatting
- Setting up conditional formatting
Conditional Formatting
- Applying conditional formatting to numbers or text
- Formatting cells using data bars or icon sets
- Using conditional formatting to perform KPI analysis
Automating Analyzed Data
- 2 Step Method to sort, subtotal and filter data
Recording Macros
- Revealing the record macro button
- Recording steps (no programming required)
- Creating macro buttons on the QAT
- Creating macro buttons on the spreadsheet
- Saving a macro
Lookups
- What is Lookup?
- Why use Lookup?
- Demo of VLookup
- Demo of XLookup (Excel 365)
Aggregate Functions (IF Statements)
- Creating an IF function
- Building a nested IF statement
- Overview of various aggregate functions ie: SumIF(S) CountIF(S)
PivotTables
- What are PivotTables?
- Why use PivotTables?
- Creating PivotTables
- Calculating in PivotTables
- Analyzing with slicers
- Analyzing with timelines
Conclusion
Training Materials
All Excel training attendees receive comprehensive courseware.
Software Requirements
- Excel 2016, 2019, 365, or later
- Related lab files that Accelebrate will provide
