About this lesson
Learn to create top/bottom and highlight cell rules, as well as to apply icon sets and data bars.
Multiple versions of this lesson are available, choose the appropriate version for you:
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.Conditional Formats with Built-In Rules.xlsx
10.8 KB Conditional Formats with Built-In Rules - Completed.xlsx
11.5 KB Conditional Formats with Built-In Rules - Extra Practice.xlsx
Creating top/bottom and highlight cell rules, as well as applying icon sets and data bars.
When to use
Conditional formatting is used to highlight key pieces of data that meets certain conditions. It reacts dynamically, updating when the underlying data is changed.
Creating Top/Bottom Rules
- Select C6:C11, go to the Home tab > Conditional Formatting > Top/Bottom Rules > Top 10
- Change the 10 to 2, and change the format to “Green Fill with Dark Green Text”
- Click OK and the top 2 items will be highlighted
- Go back to Conditional Formatting > Top/Bottom Rules > Bottom 10
- Change the 10 to 2, and change the format to “Light Red Fill with Dark Red Text”
- Now the bottom 2 items will be highlighted
- Change C9 to 10,000 and notice the formats update
Creating Highlight Cells Rules
- Select D6:D11
- Go to the Home tab > Conditional Formatting > Highlight Cells Rules > Greater Than...
- Set address value to $D$4 and apply Yellow Fill with Dark Yellow Text
- Change D4 to 25,000 and notice only one cell is highlighted
Creating Icon Sets
- Select E6:E11 and go to the Home tab > Conditional Formatting > Icon Sets
- Pick the stars
- Notice the top 2 will have full stars, one will have a half star and the others empty stars
Creating Data Bars
- Select E6:E11 and go to the Home tab > Conditional Formatting > Data Bars
- Choose your favourite colour
- Notice that the bars show the numbers in proportion to the others in the selected range
Lesson notes are only available for subscribers.