Conditional Formatting in Excel

Conditional formatting in Excel automatically changes how a cell looks based on its value. For example, it can colour failing marks red, highlight sales above ₹1,00,000 in green, or shade duplicates. You set it from Home ▸ Conditional Formatting. The formatting updates on its own whenever the data changes, so your sheet always draws attention to the numbers that matter.

What it does

Instead of manually colouring cells, you give Excel a rule ("if the value is below 33, make it red"), and Excel applies it to every cell in the range. Change a number, and the colour updates instantly. It turns a plain table into something you can read at a glance.

Highlight cells by a rule

1. Select the range.
2. Go to Home ▸ Conditional Formatting ▸ Highlight Cells Rules.
3. Choose a rule: Greater Than, Less Than, Between, Equal To, Text that Contains.
4. Enter the value (say 100000) and pick a colour.
5. Click OK.

Now every cell over 100000 is coloured automatically.

Colour scales and data bars

• Colour Scales shade cells from one colour to another based on value (say green for high, red for low). Great for spotting highs and lows across a column.
• Data Bars draw a little bar inside each cell, longer for bigger values, like a mini chart.
• Icon Sets add small icons (arrows, traffic lights) based on value.

Find these under Conditional Formatting, below the Highlight Cells Rules.

Highlight duplicates

1. Select the range.
2. Conditional Formatting ▸ Highlight Cells Rules ▸ Duplicate Values.
3. Pick a colour and click OK.

Every repeated value is now coloured, so you can spot double entries quickly.

Top/bottom rules

Under Top/Bottom Rules you can instantly highlight the Top 10, Top 10%, Above Average, and so on, without a formula. Useful for spotting your best sales or weakest scores.

Worked example (a marks sheet)

Marks in column B. You want fails in red and distinctions in green:

1. Select B2:B30.
2. Highlight Cells Rules ▸ Less Than ▸ 33 ▸ red fill.
3. Add another rule: Highlight Cells Rules ▸ Greater Than ▸ 90 ▸ green fill.

Now failing and top students stand out instantly, and it updates if marks change.

Manage or remove rules

Go to Conditional Formatting ▸ Manage Rules to edit or delete rules. To clear all formatting from a range, use Clear Rules ▸ Clear Rules from Selected Cells.

Pro tips

• Apply the rule to the whole column range at once so new entries get formatted too.
• Combine two or three rules (like fail-red, pass-normal, distinction-green) for a clear picture.
• Use Colour Scales on a whole table to see patterns without reading every number.

Common mistakes

• Selecting only part of the data. Then some cells don't get formatted. Select the full range first.
• Too many colours. A rainbow is hard to read. Two or three meaningful colours are plenty.
• Forgetting to manage old rules. Overlapping rules can conflict; use Manage Rules to tidy them.

Key takeaways

• Conditional formatting styles cells automatically based on their value.
• Use Highlight Cells Rules for greater/less than, between, duplicates.
• Colour Scales, Data Bars and Icon Sets show patterns visually.
• Rules update automatically when the data changes.

Practice task

Take a column of 20 sales figures. Highlight all values above ₹50,000 in green, and apply a Data Bar to the whole column. Then add a rule to highlight any duplicate figures.

Turn plain data into clear reports in the ADCA program at HCI.