Creating top/bottom and other rules to highlight cells based on their values.
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 x Rules
- Select your data, go to the Home tab --> Conditional Formatting --> Top/Bottom Rules
- Choose the Top 10 or Bottom 10 rules that you need
- Change “10” to the number (or percentage) of records you wish to highlight
- Change the format if desired
- Update a value and watch the highlights update to reflect it’s inclusion
Creating Highlight Cells Rules
- Select the range of data you wish to apply the rule to
- Go to the Home tab --> Conditional Formatting --> Highlight Cells Rules --> Greater Than...
- Set address value to a target cell and apply Yellow Fill with Dark Yellow Text
- Change the target cell you chose to a new value
- Notice that the formatted range will highlight all values greater than the target cell
- Select any cell to which the rule is applied
- Go to the Home tab --> Conditional Formatting --> Manage Rules
- Select the rule and choose Edit
- Modify the rule as desired
Hints & tips
- Conditional formatting rules can be overlapped to make more complex rules
Lesson notes are only available for subscribers.