INTERMEDIATE MS EXCEL
THE EXCEL MC
_svg.png)
Objective
Students will understand how to copy formulas correctly without breaking them.
At Intermediate Level?
“At intermediate level, we stop just typing formulas and start controlling how Excel behaves.
One of the most important skills is understanding cell references.”Step 1 – Show Relative Reference
-
In cell A1 type: 100
-
In cell A2 type: 200
-
In cell B1 type:
=A1+A2
4. Press Enter.
Now copy B1 down to B2.
Class please tell me:
“What changed in the formula?"
Let me show the class the formula bar.
Let me Explain:
-
Excel automatically adjusted the references.
-
This is called a relative reference.
-
Excel assumes you want the formula to move with the cells.
֎ If you need more help after class, click below:
MS Excel: Working with Relative vs Absolute Cell References Explained
Step 2 – Introduce Absolute References
Now:
-
In cell D1 type: 0.20 (this will represent VAT 20%)
-
In cell C1 type:
=A1*D1
Copy it down.
Explain what happens — the VAT reference moves.
Now let's correct it:
In C1 type:
=A1*$D$1
Explain clearly:
“The dollar signs lock the column and the row.
No matter where I copy this formula, it will always use D1.”Explain the Rule Clearly
-
$D$1 → Fully locked
-
D$1 → Row locked
-
$D1 → Column locked
Let me give you a tip:
“If your totals are wrong after copying, check your dollar signs.”
֎ If you need more help after class, click below:
MS Excel: Working with Relative vs Absolute Cell References Explained
Student Practice
Create a small table: Create the 3 column heads bullet pointed below
-
Salary
-
Increase Rate
-
New Salary
Put increase rate in one cell only.
Use absolute reference in formula.
Copy down.-
2.1 The IF Function: Objective
Use logic to return different results based on conditions.
What exsactly does the IF function do?
“The IF function allows Excel to make decisions.”
Structure:
=IF(logical_test, value_if_true, value_if_false)
Step-by-Step Example
-
In A1 type: 1200
-
In B1 type:
=IF(A1>1000,"Over Budget","OK")
Let me Explain:
-
Excel checks the condition.
-
If true → returns first result.
-
If false → returns second result.
"Let's do a change and see what happens"
Let's change A1 to 800 to demonstrate.
Real Council Example
Budget monitoring:
| Department | Budget | Actual | Status |
In Status column type:
=IF(C2>B2,"Over Budget","Within Budget")
2.2 COUNTIF
Let me Explain:
“COUNTIF counts how many cells meet a condition.”
Example:
=COUNTIF(A2:A20,"Open")
Let me Explain:
-
First argument → range to check
-
Second argument → condition
֎ If you need more help after class, click below:
2.3 SUMIF
Let me Explain:
“SUMIF adds numbers only when a condition is met.”
Example:
=SUMIF(A2:A20,"Housing",B2:B20)
Let me Explain:
-
Where to look
-
What condition
-
What to add
֎ If you need more help after class, click below:
Student Practice
Using a dataset:
-
Count how many Open requests
-
Total cost for Roads department
-
Show IF statement for overspend
-
Objective
Learners will:
-
Control what users can enter.
What is a Data Validation
“Data validation prevents mistakes before they happen.”
Step 1 – Using Data Validation
-
Create a list:
-
Open
-
Closed
-
In Progress
-
Escalated
-
-
Select the cells where status will be entered.
-
Click Data tab
-
Click Data Validation
-
Choose List
-
Select your source range
-
Click OK
Let me Explain:
“Now users cannot type random values.”
Let me demonstrate trying to type something invalid.
֎ If you need more help after class, click below:
MS Excel: Working with Data Validation & Drop-Down Lists Explained
Student Practice
Create a leave tracker:
-
Employee Name
-
Leave Type (drop-down)
-
Status (drop-down)
-
4.1 Convert to Table
“Tables are one of Excel’s most powerful features.”
Step 1 – Tables
-
Click inside dataset.
-
Press Ctrl + T
-
Confirm headers.
-
Click OK.
Let me Explain benefits:
-
Automatic filters
-
Expands automatically
-
Structured references
-
Easier PivotTables
Step 2 – Filtering
Click dropdown arrow in header.
Let me Demonstrate:
-
Filter by Housing
-
Filter by Open status
-
Clear filter
Let me Explain:
“Filtering does not delete data — it only hides it.”
Step 3 – Sorting
Let me Demonstrate:
-
Sort A–Z
-
Sort by multiple columns
Let me Explain multi-level sorting clearly.
Student Practice
Filter:
-
Show only Roads department
-
Show only Open items
-
Sort by highest cost
-
Objective
Highlight important information automatically.
What is Conditional Formatting
“Conditional formatting makes patterns visible instantly.”
Example 1 – Highlight Over Budget
-
Select Actual Spend column.
-
Home → Conditional Formatting
-
Highlight Cells Rules → Greater Than
-
Enter budget reference.
Example 2 – Data Bars
-
Select Resolution Days.
-
Conditional Formatting → Data Bars
Let me Explain:
“Longer bars = longer resolution time.”
Example 3 – Icon Sets
Use traffic lights for performance.
Explain how to adjust rule settings.
֎ If you need more help after class, click below:
Student Practice
-
Highlight overdue items.
-
Add data bars to costs.
-
Apply traffic lights to performance score.
-
Objective
(This is the core intermediate topic.)
What does a Pivot Tables do?
Summarise large data quickly.
Step 1 – Insert PivotTable
-
Click inside dataset.
-
Insert → PivotTable.
-
Click OK.
Step 2 – Build First Report
Drag:
-
Department → Rows
-
Cost → Values
Let me Explain:
“This instantly summarises thousands of rows.”
Step 3 – Change Value Settings
Right-click → Value Field Settings → Change to Count.
Let me Explain difference between Sum and Count.
Step 4 – Add Filters
Drag Status to Filters.
Let me demonstrate filtering the PivotTable.
Step 5 – Create PivotChart
PivotTable Analyse → PivotChart.
Let me Explain:
“Pivot Charts are dynamic and update with filters.”
֎ If you need more help after class, click below:
Student Practice
Create:
-
Total cost by department
-
Count of requests by status
-
Monthly summary (group dates)
-
7.1 VLOOKUP (or XLOOKUP if available)
VLOOKUP Structure
=VLOOKUP(lookup_value, table_array, column_number, FALSE)
Let me Explain each argument clearly.
Example
Enter Staff ID → Return Department.
֎ If you need more help after class, click below:
If Using XLOOKUP
=XLOOKUP(A2,A:A,C:C,"Not Found")
Let me Explain why it’s easier.
֎ If you need more help after class, click below:
Student Practice
-
Create employee lookup sheet.
-
Freeze Panes
View → Freeze Panes → Freeze Top Row.
Let me Explain why useful.
Protect Sheet
Review → Protect Sheet.
Let me Explain how to unlock input cells first.
֎ If you need more help after class, click below:
MS Excel: Working with Protecting Worksheets and Workbooks Explained
Print Setup
Page Layout:
-
Set Print Area
-
Fit to One Page
-
Repeat Header Row
FINAL PRACTICAL TASK
Students must build:
-
Data table (as Table)
-
SUMIF or COUNTIF
-
IF statement
-
Conditional formatting
-
PivotTable summary
-
One chart
-
Drop-down validation
-
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.
-
