Microsoft Excel Advanced Training

This course is suggested for experienced Excel users who want to move into more advanced territory — troubleshooting large or complex workbooks, automating repetitive tasks, collaborating on shared workbook data, and building more sophisticated formulas to analyze large, complex datasets.

Upon successful completion of this course, students will be able to:

• Link and consolidate data across multiple worksheets and workbooks instead of tracking it manually in one place
• Collaborate on a shared workbook and protect worksheets and workbooks from unwanted changes
• Use data validation to prevent bad entries before they happen, and use macros to automate repetitive tasks
• Use lookup functions and dynamic arrays to retrieve data automatically, and trace and evaluate formulas to track down errors
• Run what-if analysis and forecast future trends from existing data instead of estimating by hand
• Visualize trends inline with Sparklines and plot geographic data using 3D Maps

Referencing Data in Other Worksheets or Workbooks

Using links and external references between worksheets and workbooks · Using 3-D references to calculate across identically structured worksheets · Consolidating data from multiple worksheets into one summary

Hands-on exercise: Students use a 3-D formula to total and average quarterly sales figures stored across separate quarterly worksheets, then consolidate quarterly quantities sold into a single summary workbook.

Working with Lookup Functions and Auditing Formulas

Lookup functions, including LOOKUP, VLOOKUP, HLOOKUP, MATCH, INDEX, and TRANSPOSE · Dynamic arrays and dynamic array functions · Tracing precedent and dependent cells · Watching and evaluating formulas to find and fix errors

Hands-on exercise: Students use lookup functions to retrieve employee details from a large employee list, use dynamic array functions to pull department-level pay data and model a proposed salary increase, trace precedent and dependent cells to visually confirm a formula is pulling from the right source, and use the Watch Window to see how a deleted value ripples into formula errors elsewhere in the workbook.

Secure and Share Workbooks

Sharing a workbook to collaborate with others · Protecting worksheets and workbooks from unwanted changes

Hands-on exercise: Students add comments to a shared salary workbook to collaborate with other managers, check it for accessibility issues, export it as a PDF for recipients who may not use Excel, then hide sensitive formulas and protect both the worksheet and the overall workbook structure.

Validate Data and Automate Worksheets

Using data validation to control what can be entered into a cell · Finding invalid data and formulas containing errors · Recording and editing macros to automate repetitive tasks

Hands-on exercise: Students apply data validation rules to a regional expense workbook to prevent entry errors, circle and correct invalid data already in the sheet, record a macro to apply identical formatting across several regional worksheets at once, and edit that macro directly in the Visual Basic Editor to refine it.

How to Use Sparklines and Map Data

Creating Sparklines to show a data trend within a single cell · Mapping geographic data using 3D Maps

Hands-on exercise: Students add Sparklines to a regional sales PivotTable to show each state's sales trend at a glance, then build a 3D Map to plot regional sales visually over time instead of relying on a crowded chart.

Using Forecasting Tools in Excel

Using data tables to model potential outcomes · Using Scenario Manager to compare potential outcomes · Using Goal Seek to work backward from a target result · Forecasting data trends with the Forecast Sheet feature

Hands-on exercise: Students build data tables to model what-if outcomes for sales and expenses under different rate assumptions, create and compare named scenarios for a proposed advertising campaign, use Goal Seek to calculate the break-even point for a product, and use the Forecast Sheet feature to project a full year of sales from two years of historical data.

Students should have attended, or have experience with the topics covered in, the Microsoft Excel Introduction and Microsoft Excel Intermediate Training courses.

Advanced Excel topics — macros, dynamic arrays, tracing formula errors — tend to break in ways that are specific to the exact workbook in front of you, which is exactly where a recorded video runs out of usefulness. In this course, an instructor can look at what's actually happening in your formula or macro and help you fix it on the spot, instead of leaving you to guess which step of a tutorial you missed.

Students also retain up to one year of access to a recording of the class and six months of post-class instructor support, which matters most at this level, since these techniques are usually applied to a real, messy, organization-specific workbook well after the class ends.