# Advanced Excel Course

This course is for anyone who needs to learn MS Advanced 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