Microsoft Excel Advanced

Course Overview

Microsoft Excel Advanced

Understand where to use and how to create a range of powerful functions and how to automate tasks using macros. Learn how to analyse data with pivot tables and 'What-If' analysis tools. 

Course Content

Module 1 - Naming Cells and Ranges  

• Creating named ranges
• Using names in formulas
• Editing and deleting named cells and ranges 

Module 2 - Powerful Formulae and Functions 

• Statistical functions (COUNTIF and SUMIF)
• Logical functions (IF, IFERROR, AND and OR)
• Lookup and reference functions (VLOOKUP, HLOOKUP, INDEX and MATCH)
• Date calculations and functions
• Math and trig functions (ROUND and INT)
• Text functions  

Module 3 - Formula Auditing  

• Tracing dependents and precedents
• Formula error checking
• Formula evaluation techniques  

Module 4 - Protection  

• Protecting files
• Protecting worksheets
• Protecting cells 

Module 5 - 'What-If' Analysis and Forecast Sheets  

• Creating and editing scenarios
• Scenario summary reports
• Data tables
• Goal seek and solver
• Predicting values with forecast sheet  

Module 6 - Data Validation 

• Validating text, values, dates and lists
• Validation input messages
• Error message alerts
• Locating invalid data  

Module 7 - Analyse Data with Pivot Tables 

• Creating a pivot table
• Drilling down on pivot data 
• Sorting, grouping and filtering pivot data
• Filter and timeline slicers
• Summary values and calculations
• Formatting pivot tables
• Pivot chart reports   

Module 8 - Automating Spreadsheets with Macros  

• Recording and running macros
• Deleting a macro
• Editing macros
• Macro virus protection