Microsoft Excel Advanced
The goal of this course is to provide you with the knowledge required to use advanced functions and formulas, analyse complex data using PivotTables and PivotCharts, perform What-If Analysis, audit and troubleshoot formulas, validate and consolidate data, import and transform external data using Power Query, and automate repetitive tasks using macros.
Complete Course Syllabus
Advanced Functions and Formulas
- Review of essential Excel functions and formulas
- Using COUNT, COUNTA, COUNTIF, COUNTBLANK, SUMIF and SUBTOTAL
- Combining IF, OR and AND functions to create complex logical tests
- Using IFERROR to handle formula errors
- Using VLOOKUP with exact and approximate matches
- Using INDEX and MATCH together for flexible lookups
- Using XLOOKUP to search for and retrieve matching data
- Using INDIRECT, ROW, COLUMN and OFFSET functions
Data Analysis and What-If Analysis
- Calculating loan repayments using the PMT function
- Using Goal Seek to determine the input required to achieve a specific result
- Creating and comparing scenarios using Scenario Manager
- Using Solver to find optimal solutions subject to constraints
- Consolidating data from multiple ranges or worksheets
Advanced PivotTables and PivotCharts
- Creating and analysing PivotTables from worksheet data
- Adding, removing and rearranging fields
- Changing PivotTable data sources and working with external data
- Using statistical calculations and summary functions
- Displaying running totals, percentages of grand totals and differences between values
- Applying filters, slicers and timelines
- Grouping dates and numerical values, and creating custom groups
- Creating and formatting PivotCharts
- Using the GETPIVOTDATA function to retrieve values from PivotTables
Formula Auditing and Troubleshooting
- Tracing precedent and dependent cells
- Identifying and checking formula errors
- Using the Watch Window to monitor selected cells
- Evaluating nested formulas step by step
Advanced Data Validation
- Applying number and list validation rules
- Restricting the type of data users can enter
- Creating input messages and error alerts
- Applying conditional data validation rules
- Identifying and troubleshooting data validation errors
Data Consolidation and Outlining
- Consolidating data by position, category or formula
- Grouping rows and columns to create outlines
- Expanding and collapsing grouped data
- Copying data from outlined worksheets
- Hiding and removing outlines
Importing and Transforming Data with Power Query
- Introduction to Power Query and its role in data preparation
- Importing data from text and CSV files, Access databases, websites and other Excel workbooks
- Selecting relevant columns and filtering data during import
- Removing unnecessary rows and columns
- Splitting columns and changing data types
- Understanding and managing applied steps
- Refreshing imported data when the source changes
Introduction to Macros
- Understanding macro security and enabling macros safely
- Recording and running a macro to automate repetitive tasks
- Viewing and understanding the VBA code generated by a recorded macro
- Making simple modifications to recorded macro code
- Deleting unwanted macros
- Assigning a macro to a worksheet button
More Excel Courses
Microsoft Excel Introduction
Master workbook navigation, efficient data handling, and essential formulas. Create professional-looking spreadsheets, design clear charts, and produce polished print-ready reports.
Microsoft Excel Intermediate 1
Develop practical Excel skills, enabling users to work more efficiently with spreadsheets, organise and analyse data, and present information clearly. Delegates will learn to use Excel Tables, conditional formatting, sorting and filtering, formulas and charts.
Microsoft Excel Intermediate 2
Building on the skills developed in Excel Intermediate 1, this course introduces more advanced formulas, workbook techniques and data analysis tools. Delegates will learn to use logical and lookup functions, work with named ranges and linked workbooks, apply advanced filtering techniques, enhance charts and gain an introduction to PivotTables.