Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 3, Virtual 1
Calculated Columns and Conditional Formatting
You add two label columns on your own, check one of them against the Staff Directory, and then make a thousand sales readable at a glance with colour.
By the end of this class you can
- Add a calculated column and explain why it beats a typed one
- Add a Size label with nested IF that handles the missing revenue
- Add a Zone label with IFS and OR, and check it against another tab
- Apply a colour scale, data bars and an above-average highlight
- Manage and clear conditional formatting rules
In Live 1 you built Revenue in column M and saw why it must stay empty where Quantity is unknown. Today you add the two columns that let TechPlug group its sales: how big each sale is, and which zone of the country it came from.
What you do in this class
- Add Size in column N with a nested IF.
- Add Zone in column O with IFS, and check it against the Staff Directory tab.
- Apply three kinds of conditional formatting.
- Do the Try This boxes and the exercises.
Hand in: Sales Data with Revenue (M), Size (N) and Zone (O), and conditional formatting on M and F. Save it: Live 2 and the rest of the track start from this file.
1. Calculated Columns
Definition
A calculated column is a column where every cell holds the same formula, adjusted to its own row. Instead of storing an answer someone typed, it stores the rule for working it out.
Real-Life Scenario
A market woman's receipt book with a printed line at the bottom of every page: "Total = quantity x price". She never has to remember to calculate it, and nobody can type a wrong total, because the rule is printed on every page.
If someone corrects a Quantity next month, Revenue, Size and everything built on them update by themselves. A typed number would sit there, wrong, until somebody noticed.
Example 1: Size
A sale is High if its Revenue is above N1,000,000 and Low otherwise. Live 1 showed the trap: an empty Revenue is empty text, which Excel treats as bigger than any number. So the empty ones are caught first:
369 High, 656 Low and 25 Missing. Filter Size for Missing: the status bar must say 25 of 1,050, the same as the blank quantities.
Example 2: Zone, With IFS
TechPlug reports by geopolitical zone. Each branch belongs to one, and the Staff Directory tab lists it in its Zone column. Ten branches, five zones, so most tests use OR:
| Zone | Branches |
|---|---|
| South West | Lagos Island, Lagos Mainland, Ibadan |
| North West | Kano, Kaduna |
| South East | Enugu |
| North Central | Abuja, Jos |
| South South | Port Harcourt, Warri |
It is a long formula, so build it one pair at a time and press Enter after each to check it still works. Then check the whole column against the Staff Directory: filter Zone to South West and look at the Branch column. Only Lagos Island, Lagos Mainland and Ibadan should be left, exactly as the directory says.
This IFS has no final TRUE, "Other". If TechPlug opens a branch in Owerri and someone types it in, the row shows #N/A instead of quietly saying "Other". Sometimes an error is exactly what you want: it tells you the rule needs updating. In Week 4 you will replace this long formula with a lookup into the Staff Directory, which needs no updating at all.
Add a fourth column, P, called Channel: "B2B" for Wholesale or Corporate and "Shop" for Retail or Unknown. Then count each with the filter.
Check your answer
=IF(OR(K2="Wholesale",K2="Corporate"),"B2B","Shop"). 685 B2B and 365 Shop. Delete it afterwards so your columns match the lessons.
Picture the receipt book with three printed lines at the foot of every page: the total, a stamp saying Big or Small, and the zone the shop is in. Nobody writes them by hand, so they are never wrong.
2. Conditional Formatting
Definition
Conditional formatting changes how a cell looks (its fill, colour or a small bar inside it) according to its value. It does not change the value. It makes patterns visible before you read a single number.
Real-Life Scenario
A fuel gauge with a red zone. You do not read "12%": you see red and you know to stop for fuel. A traffic light does the same. Colour is faster than reading.
The Three Kinds You Will Use
| Kind | Shows | Best for |
|---|---|---|
| Colour scale | A shade from red (lowest) through yellow to green (highest) | Seeing high and low values across a column |
| Data bars | A bar inside the cell, longer for bigger values | Comparing sizes, like a mini bar chart |
| Highlight rules | A fill only on cells that pass a rule (above average, greater than, duplicate) | Picking out the exceptions |
Kind 1: Colour Scale on Revenue
Colour scale
The middle colour, yellow, sits at the median: half the sales are below it and half above. A sale of ten iPhones is deep green; a single mouse is deep red. The empty Revenue in row 22 gets no colour at all, because there is nothing to compare. Scroll down your sheet and the big sales jump out without reading a number.
Kind 2: Data Bars on Quantity
Data bars
The longest possible bar belongs to the largest quantity in the whole column, 10. Single units barely show.
Kind 3: Highlight Sales Above Average
Above average
361 of the 1,025 sales with a value are above average, fewer than half. That is normal for sales data: a few large sales pull the average up, so most sales sit below it. Remember it in Week 5 when you choose the numbers for a dashboard.
Apply the colour scale and the above-average rule to the same range and both show at once, which is hard to read. To see or remove rules: Home > Conditional Formatting > Manage Rules. To remove all of them: Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.
Highlight every sale where Quantity is greater than 8: Conditional Formatting, Highlight Cells Rules, Greater Than, type 8. How many cells light up? (Filter by colour to count them.)
Check your answer
92 sales. Data, Filter, then the Quantity arrow, Filter by Color.
Picture the fuel gauge, the traffic light and a ruler, painted onto your spreadsheet: shades for high and low, bars for size, red for the exceptions. Your eye finds the story before you start reading.
Practical Exercises
Exercise 1: Very Easy
Filter Zone for South South. Which two branches are left?
Exercise 2: Simple Application
Add a Units_Band column: Single for a Quantity of 1, Few for 2 to 5, Bulk for 6 or more, and Missing where Quantity is blank. Use IFS.
Exercise 3: Combination
Put a colour scale on Unit_Price. Which product is the greenest, and which the reddest?
Exercise 4: A Small Real-World Problem
A manager wants to see at a glance which sales are Corporate AND above average. Use Conditional Formatting, New Rule, "Use a formula to determine which cells to format", with =AND($K2="Corporate",$M2<>"",$M2>AVERAGE($M$2:$M$1051)), applied to A2:O1051. Explain why each $ is where it is, and why $M2<>"" is there.
Answers to the exercises
Exercise 1: Port Harcourt and Warri. Exercise 2: =IFS(ISBLANK(F2),"Missing",F2=1,"Single",F2<=5,"Few",TRUE,"Bulk"). Exercise 3: the iPhone 14 is greenest, the Logitech mouse reddest. Exercise 4: the column letters are locked so every cell in a row looks at K and M; the row is free so each row checks itself; the average range is fully locked. $M2<>"" stops empty Revenue counting as above average.
Checkpoint
Q1. Why does the Size formula test M2="" first?
An empty Revenue is empty text, which Excel treats as bigger than any number, so it would be called High.
Q2. Why does the Zone IFS have no TRUE at the end?
So a new, unknown branch shows #N/A instead of being silently labelled, which tells you to update the rule.
Q3. How do you check the Zone column is right without another formula?
Filter each zone and compare its branches with the Staff Directory tab.
Q4. Does conditional formatting change the values in the cells?
No. It only changes how they look.
Q5. Why are most sales below average?
A few very large sales pull the average up, so most ordinary sales fall below it.
Tick Before You Move On
- ☐ Revenue in M, Size in N, Zone in O, all filled to row 1,051
- ☐ Size shows 25 Missing, matching the blank quantities
- ☐ Zone matches the Staff Directory for every branch
- ☐ A colour scale, data bars and an above-average rule applied and understood
- ☐ I know how to manage and clear rules
- ☐ I saved the file: Live 2 starts from it
Quick Recap
A calculated column stores a rule, so every row is right and stays right. Catch empty Revenue first in any rule that compares it with a number. IFS with OR turns ten branches into five zones; another tab is how you check it. Colour scales show high and low, data bars show size, highlight rules show exceptions. Conditional formatting changes the look, never the value.
Next, Week 3, Live 2: SUMIF, COUNTIF and AVERAGEIF, and their S versions. "What did Port Harcourt sell?" and "How many wholesale sales did Kano make?" in one formula each.