Learn how to use Conditional Formatting to identify cells that are of interest.
For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using the file) to quickly identify any cell with a value greater than $500.
Join the 'Excel at Work Insiders' group today!
100% free. Join now and unlock offers exclusive to this group!
Receive tips and guidance to help you SAVE TIME, WORK SMARTER and IMPROVE YOUR UNDERSTANDING of Microsoft® Office.
Applying Conditional formatting
Excel can apply a different font, style, pattern and border to cells with content that fall outside or within the limits you specify.
This lets you quickly spot areas of interest or concern.
If a cell contains a formula, the value of the formula will be calculated, and the conditional formatting then tested against that value.
Setting a condition with Highlight Cell Rules
The Highlight Cells Rules option displays seven pre-set criteria that can be applied to a cell or group of cells.
To apply one of the pre-set Rules:
1. Select the cell, or group of cells, for which you want to apply conditional formatting.
2. From the Home tab, in the Styles group click on Conditional Formatting. A drop down menu is displayed. Select Highlight Cell Rules from the menu.
3. Select a condition from the drop-down menu, or select More Rules to create a new condition.
4. In the dialog box enter the condition to be matched. If the condition depends on the value of another cell, you can select the cell by clicking on it. In the example below the format will only be applied to cells containing a value “Greater Than” 500.
5. From the option box on the right select the format to be applied if the condition is met. There are a range of pre-set formats to use, or you can create your own by selecting Custom Format from the drop-down list.
6. Click OK. If you require another condition click Conditional Formatting again and create another rule.
Identifying duplicate or unique rows of data
To identify rows with duplicate or unique data:
1. From the Conditional Formatting menu select Highlight Cell Rules and then select Duplicate Values.
2. From the Duplicate Values dialog box select if you would like Duplicate or Unique values formatted. Click OK.
If you found this post helpful please ‘Like’ us!
If you enjoyed this you may also enjoy
- Finding and removing duplicate data in Excel
- Link cells in the same or different Excel worksheets
- Find, modify and break links to an Excel workbook
- 5 Reasons why your Excel filter may not be working