Learning Excel: Conditional formatting with formulas

Conditional formatting

Big orders need brewing in good time. Isla wants to spot them at once, in red, without going through the list herself. And when a new one comes in tomorrow, it should light up by itself.

A rule with a formula

Conditional formatting colours the cells that meet a rule. The rule is a formula that gives TRUE or FALSE.

You write it for the first cell of the range, here E2: =E2>=300. Excel shifts it along to every other cell, just like filling down: for E3 it becomes E3>=300.

	A	B	C	D	E	F	G	I
1	Quarter	Beer	Customer	Packaging	Litres	Price	Revenue	Result
2	Q1	Wise Owl	Crown Inn	Keg	300	£2.40	£720.00
3	Q1	Tripel Hoot	Bay Tree Bistro	Bottle	80	£4.50	£360.00
4	Q1	Night Owl	Curlew Inn	Keg	150	£3.20	£480.00
5	Q1	Wise Owl	Festival Hall	Keg	600	£2.20	£1,320.00
6	Q1	Barn Owl	Bay Tree Bistro	Bottle	60	£4.00	£240.00
7	Q2	Wise Owl	Crown Inn	Keg	350	£2.40	£840.00
8	Q2	Tripel Hoot	Crown Inn	Bottle	90	£4.50	£405.00
9	Q2	Night Owl	Bay Tree Bistro	Bottle	120	£3.40	£408.00
10	Q2	Wise Owl	Curlew Inn	Keg	200	£2.40	£480.00
11	Q2	Barn Owl	Festival Hall	Keg	250	£3.60	£900.00
12	Q3	Wise Owl	Festival Hall	Keg	800	£2.20	£1,760.00
13	Q3	Tripel Hoot	Bay Tree Bistro	Bottle	110	£4.50	£495.00
14	Q3	Barn Owl	Crown Inn	Keg	180	£3.80	£684.00
15	Q3	Wise Owl	Curlew Inn	Keg	250	£2.40	£600.00
16	Q3	Night Owl	Crown Inn	Keg	100	£3.20	£320.00
17	Q4	Night Owl	Festival Hall	Keg	400	£3.00	£1,200.00
18	Q4	Tripel Hoot	Curlew Inn	Bottle	70	£4.50	£315.00
19	Q4	Wise Owl	Crown Inn	Keg	320	£2.40	£768.00
20	Q4	Barn Owl	Bay Tree Bistro	Bottle	90	£4.00	£360.00
21	Q4	Tripel Hoot	Festival Hall	Keg	300	£4.20	£1,260.00
22

Colouring whole rows

Put a $ in front of the column: =$E2>=300. Every cell in the row then looks at column E, while the row still shifts along.

That way the whole row of every big order lights up, from A to G.

	A	B	C	D	E	F	G	I
1	Quarter	Beer	Customer	Packaging	Litres	Price	Revenue	Result
2	Q1	Wise Owl	Crown Inn	Keg	300	£2.40	£720.00
3	Q1	Tripel Hoot	Bay Tree Bistro	Bottle	80	£4.50	£360.00
4	Q1	Night Owl	Curlew Inn	Keg	150	£3.20	£480.00
5	Q1	Wise Owl	Festival Hall	Keg	600	£2.20	£1,320.00
6	Q1	Barn Owl	Bay Tree Bistro	Bottle	60	£4.00	£240.00
7	Q2	Wise Owl	Crown Inn	Keg	350	£2.40	£840.00
8	Q2	Tripel Hoot	Crown Inn	Bottle	90	£4.50	£405.00
9	Q2	Night Owl	Bay Tree Bistro	Bottle	120	£3.40	£408.00
10	Q2	Wise Owl	Curlew Inn	Keg	200	£2.40	£480.00
11	Q2	Barn Owl	Festival Hall	Keg	250	£3.60	£900.00
12	Q3	Wise Owl	Festival Hall	Keg	800	£2.20	£1,760.00
13	Q3	Tripel Hoot	Bay Tree Bistro	Bottle	110	£4.50	£495.00
14	Q3	Barn Owl	Crown Inn	Keg	180	£3.80	£684.00
15	Q3	Wise Owl	Curlew Inn	Keg	250	£2.40	£600.00
16	Q3	Night Owl	Crown Inn	Keg	100	£3.20	£320.00
17	Q4	Night Owl	Festival Hall	Keg	400	£3.00	£1,200.00
18	Q4	Tripel Hoot	Curlew Inn	Bottle	70	£4.50	£315.00
19	Q4	Wise Owl	Crown Inn	Keg	320	£2.40	£768.00
20	Q4	Barn Owl	Bay Tree Bistro	Bottle	90	£4.00	£360.00
21	Q4	Tripel Hoot	Festival Hall	Keg	300	£4.20	£1,260.00
22

Ready for more?

The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.

Start the lesson