EXCEL · 2 Days

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.

Duration: 2 Days From £550/day (up to 8 learners) 1-to-1 or Groups of up to 8 Learners (Larger by Arrangement) M365 (Desktop & Web) · Office 2021/2024 Certificate · Manuals · 1-Year Support

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
Copied to Clipboard ✓

Ready to Share