Advanced Excel Course
Or log in to access your purchased courses
This course is for anyone who needs to learn Advanced MS Excel. It follows on from our Intermediate MS Excel course.
If you prefer to attend a live session, visit the MS Excel Advanced Courses Calendar
Modules
Downloads and Course introduction
Ultimate Excel Tool
Lessons
Tips and Tricks- Advanced
Lessons
- Tips and Tricks- covered in Intermediate course Free Preview
- View multiple cells/ sheets/ spreadsheets at the same time
- Adding cells by moving sheets around
- Useful for the aggregation trick
- Aggregation Exercise 1
- Custom Format a word into a number cell e.g. 100 tons
- Custom Format numbers into thousands or millions
- Custom Format Exercise
- Tables- Critical for Excel use going forward
- Tables Exercises
Filtering and Sorting
Lessons
Tools for text
Lessons
Pivot Tables
Lessons
- Pivot Tables- covered in Intermediate course Free Preview
- Protect the source data of a pivot table
- Add a calculation INSIDE a Pivot Table
- EXERCISE: CREATING A CALCULATION WITHIN A PIVOT
- Conditional Formatting within a Pivot
- Get information out of a Pivot into a cell
- Switching off the GETPIVOTDATA
- EXERCISE: USING GETPIVOTDATA
- EXERCISE: EXTRACT A UNIQUE LISTING FROM A DATABASE
- EXERCISE: LEAVE REGISTER
- EXERCISE: RECONCILE TWO LISTS
- EXERCISE: RECONCILE PURCHASE ORDERS WITH INVOICES
- EXERCISE: MOVABLE ASSET MANAGEMENT
- EXERCISE: CALL CENTRE LOGS
- Add up ITEMs within a Pivot Table Field
- EXERCISE: CREATING A CALCULATED ITEM
Function and Formula Basics
Lessons
- Function and Formula- covered in Intermediate course Free Preview
- Function Wizard- why you should use it
- Trace where a formula comes from and goes to (even across sheets)
- Using Absolute and Relative Referencing ($ signs)
- Shortcut to add $ signs to cell references
- Cell Referencing Examples
- How to (safely) build a nested IF formula
- IF this AND this AND this is True
- IF examples 1
- IF examples 2
Formula Tips and Tricks
Lessons
Text Functions
Lessons
Key Lookup Functions
Lessons
- Key Lookup functions- covered in Intermediate course Free Preview
- Vlookup approximate match (True instead of False)
- VLOOKUP Examples 1- categorise, bands
- VLOOKUP Examples 2- categorise, bands
- Vlookup partial text from single cell- fuzzy lookup
- VLOOKUP Examples- fuzzy logic
- Automatic Sorting in Excel Exercise
- Handling Error Messages- Remove the impact of #NA, #DIV/0! and more
- OFFSET
- OFFSET- Simple Example
- OFFSET- Time Lag Example
- OFFSET- Allow Insert Rows Example
- OFFSET- Monthly Management Reporting Example
- OFFSET- Other Examples
- MATCH and INDEX as an alternate VLOOKUP?
- Lookup across rows and columns (at the same time)
- Lookup to the left instead of right (like VLOOKUP)
- Get Excel to find the columns you want to bring back from a lookup
- MATCH, INDEX, HLOOKUP and OFFSET Combined
- Lookup on different sheets
- INDIRECT Example to summarise divisions across sheets
- Transpose rows to columns but keep them linked
SUM, COUNT, AVERAGE IFS
Lessons
- Sum, count or average if certain cells match
- SUMIF et al Examples 1
- SUMIF et al Examples 2
- Sum, count or average cells e.g. bigger than
- SUMIFS et al Examples- Bigger or less than
- Sum, count or average with many conditions
- SUMIFS et al Examples- multiple conditions
- Sum, count or average with partial matches
- SUMIFS et al Examples- partial matches
Date functions and issues
Lessons
- Date Functions- covered in Intermediate course Free Preview
- How Excel Handles Times and Dates
- Create a VALID Excel date with a day, month and year number
- Calculate the proper end of month
- When is the next workday e.g. 30 days from invoice date
- How many working days between 2 dates
- How many months/years between two dates
- Force Excel to format a date to your version
- Date Examples 1
- Date Examples 2
- Date Examples 3
- Date Examples 4
Conditional Formatting
Lessons
- Conditional Formatting- covered in Intermediate course Free Preview
- Conditional Formatting- Linking to different cells
- Conditional Formatting- Exercises 1
- Conditional Formatting- Exercises 2
- Control what is entered in an Excel cell- Data Validation
- Control the inputs in a cell based on another cell
- Data Validation Example
Charting In Excel
Lessons
- Charting- covered in Intermediate course Free Preview
- Paste a linked picture of data onto a chart (or anywhere)
- Sparklines for trend analysis
- Sparklines Exercise
- Copy the format of one chart to another chart
- Remove Zeros from chart labels
- Combine a column with a line chart (or other combinations)
- Stop charts stretching when the column width changes
- Customise the series marker to your own image
- Add linked commentary directly to the chart
- Add commentary to the axis
- Add commentary to labels and call outs
- Add commentary to labels for older versions of Excel
- Hiding a series with NA()
- Chart Examples 1- Conditional bar charts, Chart builder
- Chart Examples 2- Combo Charts
- Chart Examples 3- Add Commentary
- Create a waterfall chart (pre Excel 2016)
- Create a waterfall chart (Excel 2016 and higher)
- Understand and use XY charts e.g. Risk Register
- Chart Examples 4- Waterfall Chart and Risk Register
- Change the default colour scheme in charts
- Create a chart template for re-use
- Add a trendline to a chart
- Benefit of using a table for chart data
- Link Excel chart to PowerPoint or Word
Protecting spreadsheet information and formulas
Lessons
- How to protect an Excel Spreadsheet from opening
- Allow Spreadsheet to be viewed but not changed (read only)
- Protect only certain cells/ formula from everything except input changes
- Lock cells but allow colour formatting or comments
- Lock Cells but allow insert or delete of columns/ rows
- Lock Cells but allow column/ row size change
- Protect cells but allow filter/ pivot table/ sort
- Hide formulas in Excel
- Hide and protect a sheet in a spreadsheet
- Allow only certain values into a cell
- Protect cells with different passwords to allow levels of authority
- Trick: Allow user to only see their information in Excel
- Trick: Protect rows and columns without protecting sheet
- Some Useful Tools when protecting spreadsheets
- Protection Examples
