Join my EXCEL'erator PROGRAM Move from Competent to Exceptional in Just 6 Weeks

Home
Training Options
All Training Options EXCEL'erator PROGRAM Corporate Training Solutions Shop - On Demand Training Videos Customised Live Group Training
Shop Testimonials
Free Training
EXCEL Masterclass Pre-recorded Training (Free)
All Microsoft 365 training

Live training with Eithne Dolan

Microsoft Excel Advanced Level 1

Let Excel do more of the work

I’ll take you from being a confident Excel user to using more powerful, dynamic and analytical features: formulas, lists and reports that respond automatically as your data changes.

View Course OutlineDownload PDF

Course outline: PDF, 2 pages

Group size
Recommended maximum: 10
For an interactive session
Delivery
Live virtual
Demonstrations, guided practice and supplied exercise files
Duration
6 hours
One 6-hour session or two 3-hour sessions

Is this course for you?

Does any of this sound familiar?

You’re comfortable using Excel, but your formulas are becoming more complicated, your reports take too long to update, or you know there are more efficient ways to analyse and present your data. Do you...

  • Regularly use Excel but feel you're only scratching the surface of what it can do?
  • Create increasingly complicated IF formulas to deal with multiple conditions?
  • Use several formulas where one better formula could potentially do the job?
  • Spend time manually sorting, filtering or extracting information for reports?
  • Want formulas that automatically return a changing list of results?
  • Need to clean, split or combine text from imported data?
  • Want to learn more about VLOOKUP & XLOOKUP?
  • Create drop-down lists that need to change depending on other selections?
  • Want Conditional Formatting to respond automatically to values, dates or exceptions?
  • Create PivotTables but aren't making full use of grouping, slicers, timelines or Pivot Charts?
  • Produce reports that have to be updated repeatedly as the underlying information changes?
  • Want to create powerful summaries without always building a traditional PivotTable?

If any of the above resonates, this course is for you.

What you’ll be able to do

  • Combine IF with AND, OR and calculations to handle multiple business rules.
  • Use modern text and dynamic-array functions to clean, split, filter, sort and return unique data.
  • Create dependable lookups with XLOOKUP and handle records that are not found.
  • Build dynamic Data Validation lists and formula-driven Conditional Formatting.
  • Create, analyse and present information with advanced PivotTables, Pivot Charts, slicers and timelines.
  • Create formula-based summaries using GROUPBY and PIVOTBY where available.
  • Understand which skills progress into Microsoft Excel Advanced: Level 2.

Your course outline

Seven modules. Real-world application.

The aim isn’t to make your formulas more complicated: it’s to show you how the right high impact features make your spreadsheets more dynamic, efficient and easier to maintain.

01Advanced Logical and Criteria Formulas
  • Review IF, SUMIF and COUNTIF in a business context
  • Combine IF with AND/OR
  • Include calculations inside IF
  • Use nested IF or IFS for a manageable number of conditions
  • Use SUMIFS and COUNTIFS with multiple criteria
  • Handle expected errors with IFERROR
02Modern Text and Date Functions
  • Clean text with TRIM
  • Extract text with LEFT, RIGHT, MID and LEN
  • Combine text with CONCAT and TEXTJOIN
  • Split text with TEXTSPLIT
  • Return text with TEXTBEFORE and TEXTAFTER
  • Extract DAY, MONTH and YEAR
  • Use TODAY, NOW, DATEDIF and WORKDAY
  • Display dates as text and convert text to dates
  • Convert time to decimal values
03Dynamic Array Functions
  • Understand spill ranges and the #SPILL! error
  • Create dynamic lists with SORT and SORTBY
  • Return unique values with UNIQUE
  • Filter records with FILTER
  • Return selected rows or columns using TAKE, DROP, CHOOSEROWS and CHOOSECOLS
  • Combine functions to create a dynamic report
04XLOOKUP, VLOOKUP and Dynamic Data Validation
  • Review an exact-match XLOOKUP
  • Return information from either side of the lookup field
  • Return multiple columns
  • Use the if-not-found argument
  • Use XLOOKUP with Excel Tables
  • Compare XLOOKUP with VLOOKUP
  • Create dynamic source lists using SORT and UNIQUE
  • Create dependent drop-down lists
  • Use named formulas or spill references where appropriate
  • Circle invalid data and copy validation safely
05Formula-driven Conditional Formatting and Protection
  • Create rules using formulas
  • Use relative, absolute and mixed references correctly
  • Highlight an entire row based on a value
  • Highlight exceptions, deadlines and duplicates dynamically
  • Manage rule order and Stop If True
  • Lock formula cells while keeping input cells editable
  • Protect selected ranges
  • Control permitted user actions
  • Protect workbook structure
06Presenting & Reporting Data
  • Create a PivotTable from an Excel Table
  • Build two- and three-dimensional reports
  • Change summary calculations and number formats
  • Group dates and numeric data
  • Control report layout, subtotals and grand totals
  • Use drill-down and Show Values As
  • Create and understand calculated fields
  • Create and format a PivotChart
  • Use slicers and timelines
  • Connect slicers to multiple PivotTables
  • Duplicate PivotTables for alternative views
  • Use report filters and Generate Report Filter Pages
  • Refresh reports when source data changes
07Formula-Based Summary Reports
  • Use GROUPBY to create a one-axis grouped summary
  • Use PIVOTBY to create a two-axis formula summary
  • Apply SUM, AVERAGE or COUNT as an aggregation

What you’ll learn to do

This course goes beyond individual Excel features. You’ll work with realistic examples rather than learning functions in isolation.

Build more powerful formulas

  • Combine IF with AND and OR
  • Work with multiple conditions
  • Use SUMIFS and COUNTIFS
  • Combine functions to solve realistic business problems

Create dynamic results

  • SORT, SORTBY, UNIQUE and FILTER
  • TAKE, DROP, CHOOSEROWS and CHOOSECOLS
  • Understand spill ranges and #SPILL! errors
  • Results that update as your data changes

Smarter lists and formatting

  • Dynamic and dependent drop-down lists
  • Highlight an entire row based on a value
  • Flag approaching deadlines and exceptions
  • Control rule priority

Interactive reports

  • Group dates and numeric data
  • Pivot Charts, slicers and timelines
  • Connect one slicer to multiple PivotTables
  • Formula summaries with GROUPBY and PIVOTBY

Ready to go even further?

Microsoft Excel Advanced Level 2 continues from this course with more sophisticated dynamic-array formulas, VSTACK and HSTACK, advanced XLOOKUP techniques, formula auditing and an introduction to Power Query.

Before you join: you should be confident with the skills covered in Microsoft Excel Intermediate, including Excel Tables, relative and absolute references, IF, SUMIF, COUNTIF, basic XLOOKUP, Data Validation, Conditional Formatting and charts.

Let’s make Excel work for you

Book a free 20-minute consultation with me to discuss your team, your Excel experience and your training goals.

Book a Free Consultation