Advanced Excel Course

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