Conditional formatting is an excellent way to quickly visualize your data in Excel spreadsheet. Using conditional formatting allows to highlight values which are duplicates, mark the highest or the lowest values in given data range and more.
Conditional formatting is located in Home tab on the Ribbon in Styles group:
- Highlight cells rules
- Top/Bottom rules
- Creating own rules for conditional formatting
- How to clear conditional formatting?
- Examples for conditional formatting
Highlight cells rules
Conditional formatting changes how look cells in our spreadsheet based on criteria of the specific condition (rule). If conditions match, cell range is formatted; if conditions don’t match cells are not formatted. There are many built-in rules to be used:
Greater than, less than, between, equal to – these rules allow to format cells, which meet specific numeric rules.
Text that Contains – this rule formats cells, which include given character(s) or text strings, eg. words.
A Date Occuring – this rule allows you to format cells which contain date occuring in a specific period from today, eg. yesterday, last week or month, next week or month.
Duplicate Values – thanks to this rule you can format duplicate or unique values in given data range.
Top/Bottom Rules
Except of basic Highlight Cell Rules you can use additional ones listed in Top/Bottom Rules area:
Top/Bottom rules are used to format cells, which meet some statistical conditions in relation to other cells in given data range. Examples: above/below average, top/bottom 10 items, or top/bottom 10%.
By default, Excel allows to find and format top/bottom 10 elements, however this can be adjusted by manual input of desired elements number:
Similarly, you can also change percentage of default top/bottom 10%:
Important note:
All Top/Bottom rules can be applied only to cells, which contain numeric data.
Creating own rules for conditional formatting
Excel provides lot of rules to apply for conditional formatting, however there is also an option to create a new, custom rule. For this purpose, use New Rule command:
By using New Rule you can set up your own formatting such as: font size, type or color, cell fill color, borders style or data format:
Moreover, you can also create new rule of conditional formatting which is based on formula, which gives TRUE or FALSE result. In such case, go to Use a formula to determine which cells to format and enter necessary formula in formula bar highlighted below in yellow:
How to clear and edit conditional formatting?
If you want to clear previously chosen rules, use Clear Rules command:
It is possible to clear rules from entire spreadsheet, but also only from selected cells or table/Pivot Table.
To edit already set up rules, please use Manage Rules command:
Examples for conditional formatting
- Highlight duplicates and unique values
- Hide 0 values
- Highlight top 3 and bottom 3 items
- Examples file download
Highlight duplicates and unique values
If you want to find and format duplicate values, select data range and choose Duplicate Values rule:
Confirm by clicking OK and you can see that all duplicates in column A have been highlighted in chosen way:
Similarly you can format unique values – the only difference is to choose type of cells to be formatted to Unique instead of Duplicate:
Hide 0 values
To find 0 values in given data range select Equal to rule:
By default, there is no formatting option which will “hide” some cells, so in this case we need to use Custom Format:
To make cells invisible in the spreadsheet, font color should be set to white:
After this change you can notice, that all cells including 0 have been formatted as desired and they are not visible in the spreadsheet:
Highlight top 3 and bottom 3 items
In this case we will find and format top 3 and bottom 3 items within data range. To do that, we need to use Top 10 Items and Bottom 10 Items and modify it.
Top 3 items:
Bottom 3 items: