ADVANCED MS EXCEL
THE EXCEL MC
_svg.png)
1.1 Relative vs Absolute References (Deep Understanding)
Deep Understanding
“Before we move into complex formulas, we need to fully understand how Excel handles cell references.
This is one of the biggest causes of formula errors at advanced level.”Step 1 – Show a Basic Formula
In cell C2 type:
=A2*B2
Copy it down.
Question for student:
“What happened when we copied the formula?”
Take a second to think
Do you know what happens, when we copied the formula down? Answer let me Explain: •Excel adjusts references automatically. •This is called a relative reference
Step 2 – Introduce Absolute Reference
Now place VAT rate (20%) in cell F1.
In D2 type:
=A2*$F$1
Let me Explain:
“The dollar signs lock the column and the row. This means no matter where I copy this formula, it will always look at F1.”
Copy down.
Let me Explain Clearly: (covered originally in basic MS Excel class)
-
$F$1 → Fully locked
-
F$1 → Row locked only
-
$F1 → Column locked only
If it does not work properly:
“If your formula breaks when copied, it usually means something needed locking.”
֎ If you need more help after class, click below:
MS Excel: Working with Relative vs Absolute Cell References Explained
Student Practice
Create a salary increase calculator:
-
Column A: Salary
-
Cell E1: Increase rate (5%)
-
Column B: New salary using absolute reference
-
2.1 Nested IF Statements
se logic to return different results based on conditions.
What are Nested IF statements
“In real reporting, we often need multiple conditions, not just one.
That’s where nested IF statements come in.”Example: Risk Rating
Criteria:
-
Over £10,000 → High
-
£5,000–£10,000 → Medium
-
Below £5,000 → Low
In C2 type:
=IF(B2>10000,"High",
IF(B2>5000,"Medium","Low"))
Let me Explain slowly:
-
First Excel checks if over 10,000.
-
If not, it checks if over 5,000.
-
If neither, it returns Low.
Important Point
“Excel reads IF statements from left to right. Always test the highest condition first.”
֎ If you need more help after class, click below:
MS Excel: Working with Nested IF, AND & OR Statements Explained
2.2 IFERROR
Create a lookup that produces #N/A.
Then wrap it:
=IFERROR(VLOOKUP(A2,A2:C20,2,FALSE),"Not Found")
Important tip:
“Professional spreadsheets never show error messages to managers.”
Let me Explain:
-
IFERROR replaces any error with a controlled message.
֎ If you need more help after class, click below:
2.3 SUMIFS (Multiple Criteria)
Dataset:
-
Column A: Department
-
Column B: Status
-
Column C: Cost
Type:
=SUMIFS(C:C, A:A, "Housing", B:B, "Open")
Let me Explain each argument clearly:
-
Sum range
-
Criteria range 1
-
Criteria 1
-
Criteria range 2
-
Criteria 2
Important tip:
“SUMIFS allows us to analyse data without creating a PivotTable.”
֎ If you need more help after class, click below:
-
3.1 XLOOKUP (Modern Excel)
Let me Explain:
“XLOOKUP replaces VLOOKUP and solves most of its limitations.”
Structure:
=XLOOKUP(lookup_value, lookup_array, return_array, "Not Found")
Example:
=XLOOKUP(A2, Staff!A:A, Staff!C:C, "Not Found")
Let me Explain:
-
It can look left or right.
-
It does not require column numbers.
-
It handles errors internally.
֎ If you need more help after class, click below:
3.2 INDEX + MATCH (Advanced Method)
Let me Explain:
“INDEX MATCH is more flexible and works in all Excel versions.”
=INDEX(C2:C20, MATCH(A2, A2:A20, 0))
Let's break it down clearly:
-
MATCH finds the position.
-
INDEX returns the value from that position.
֎ If you need more help after class, click below:
MS Excel: Working with INDEX + MATCH (Beginner to Advanced) Explained
-
Only found in the below versions of Excel
(Excel 365 / 2021+)
4.1 FILTER
Let me Explain:
“FILTER creates live reports that update automatically.”
=FILTER(A2:D100, B2:B100="Housing")
Let me Explain:
-
First argument = data
-
Second argument = condition
Change “Housing” to a cell reference to make dynamic.
֎ If you need more help after class, click below:
4.2 UNIQUE
=UNIQUE(A2:A100)
Let me explain what this does:
“This automatically extracts a list of unique departments.”
֎ If you need more help after class, click below:
4.3 SORT
=SORT(A2:D100,2,-1)
Let me Explain:
-
2 = second column
-
-1 = descending
֎ If you need more help after class, click below:
-
Advanced Pivottables explained
PivotTables are powerful on their own — but Advanced PivotTables take your data analysis to a professional level.
They allow you to perform deeper analysis, create dynamic reports, and build interactive dashboards with ease.
If you work in finance, operations, or reporting, advanced PivotTable skills are essential.
Step 1 – Create PivotTable
-
Click inside dataset
-
Insert → PivotTable
-
Click OK
Refresher:
“PivotTables summarise thousands of rows instantly.”
Step 2 – Build Summary
Drag:
-
Department → Rows
-
Cost → Values
Let me Explain:
“Values default to Sum. We can change this.”
Step 3 – Change Value Settings
Right-click → Value Field Settings → Show Values As → % of Grand Total
Let me Explain when to use percentages.
Step 4 – Group Dates
Drag Date into Rows.
Right-click date → Group → Select Months + Years.
Let me Explain:
“Grouping makes raw date data meaningful.”
Step 5 – Add Slicers
PivotTable Analyse → Insert Slicer → Select Department.
Let me Explain:
“Slicers create interactive dashboards.”
֎ If you need more help after class, click below:
-
Power Query Explained
Power Query is one of Excel’s most powerful tools for importing, cleaning, and transforming data. It allows you to automate repetitive data tasks and prepare large datasets quickly and accurately.
If you regularly copy, clean, or combine data, Power Query can save you hours of manual work.
Step 1 – Import Data
Data → Get Data → From Workbook
Let me explain:
“Power Query allows us to clean data once and refresh it forever.”
Home → Remove Rows → Remove Blank Rows
Step 3 – Remove Duplicates
Select column → Remove Duplicates
Step 4 – Split Column
Transform → Split Column → By Delimiter
Step 5 – Close & Load
Let me Explain:
“Now when the raw file changes, we simply press Refresh.”
Demonstrate Data → Refresh All.
֎ If you need more help after class, click below:
7.1 What does What-If Analysis?
Excel’s What-If Analysis tools let you explore different scenarios and see how changes to your data affect outcomes. It’s a powerful feature for budgeting, forecasting, and decision-making in finance, operations, and project management.
Instead of manually recalculating every possibility, What-If Analysis lets Excel do it automatically.
Goal Seek
Data → What-If Analysis → Goal Seek
Example:
-
Set cell: Profit
-
To value: 0
-
By changing: Tax rate
Let me Explain:
“Goal Seek works backwards to find required inputs.”
7.2 Data Tables
Create column of interest rates.
Use Data Table tool.
Let me Explain clearly:
-
Row input cell
-
Column input cell
֎ If you need more help after class, click below:
-
What is a Dashboard for
An Excel Dashboard is a visual summary of key information, designed to present data clearly and quickly. Instead of scrolling through rows of numbers, a dashboard displays charts, totals, and performance indicators all in one place.
Dashboards help turn data into decisions. “A dashboard is not about decoration — it is about clarity.”
Structure to Teach
-
Raw Data sheet
-
Calculation sheet
-
Pivot sheet
-
Dashboard sheet
Add:
-
KPI cells (large font)
-
PivotCharts
-
Slicers
-
Conditional formatting
-
Dynamic titles
Example dynamic title:
="Performance Report - "&B1
֎ If you need more help after class, click below:
-
What is a Macro used for
Recording a Macro in Excel means capturing a series of actions so they can be replayed automatically later.
It is used to automate repetitive tasks, such as formatting reports, applying filters, or generating standard layouts, saving time and ensuring consistency.
Record Macro
Developer → Record Macro
Perform:
-
Format headings
-
Apply filters
-
Adjust widths
Stop recording.
Assign Button
Insert → Shape → Right Click → Assign Macro
Let me Explain:
“Macros save time but require responsible use.”
֎ If you need more help after class, click below:
-
FINAL PRACTICAL TASK - Student Practice
Students must create:
-
Cleaned dataset (Power Query)
-
Automated calculations (SUMIFS)
-
Lookup system
-
PivotTable summary
-
Interactive dashboard
-
Optional macro button
As part of the Advanced class, our objective is to ensure you have a clear understanding of all the learning covered. The final assessment will help us identify any areas where you may need further support. A dedicated 30-minute follow-up session will then be held to review the sections you found most challenging and provide targeted guidance to strengthen your understanding.
-
Please Enter Your Details below & Upload Your Spreadsheet
Next Steps
-
Enter your personal details
-
Save your spreadsheet under your first and surname if you have not already
-
Upload your spreadsheet below using "Upload Spreadsheet"
-
Your trainer will email or WhatsApp your results within 48 working hours, along with guidance to support you in any areas where improvement is needed.
-
