excel tutorialConditional Formatting
Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell’s value. This chapter teaches you how to highlight interesting cells using Highlight Cells Rules, how to get more insight in how your values are distributed using Top/Bottom Rules, how to visualize data using Data Bars, Color Scales, and Icon Sets, and how to create your own rule.
Highlight Cells Rules
If you want to highlight cells that are greater than a certain value, execute the following steps.
1. Select the cells.
2. Click on Home, Conditional Formatting, Highlight Cells Rules, Greater Than...
2. To highlight cells that are greater than 80, enter the value 80 and select any formatting style.
3. Click OK.
Result:
This conditional formatting "sticks" to the cells and changes dynamically depending on the cell's value.
4. Change the value of cell A1 to 79, and the value of cell A2 to 85.
Result:
Note: in a similar way, you can highlight cells that are less than a value, between a low and high value, equal to a value, etc.
Top/Bottom Rules
If you want to get more insight in how your values are distributed, you can use the Top/Bottom Rules. For example, to highlight the cells that are above the average of the cells, execute the following steps.
1. Select the cells.
1. First, clear the previous conditional formatting rule. Click on Home, Conditional Formatting, Clear Rules, Clear Rules from Selected Cells.
2. Next, click on Conditional Formatting, Top/Bottom Rules, Above Average...
3. Select any formatting style.
Result:
That's pretty amazing! Excel automatically calculates the average (61.4) and formats the cells that are above this average!
Note: in a similar way, you can highlight the highest or lowest cells, the top % cells, etc. The sky is the limit!
Data Bars, Color Scales, Icon Sets
Data Bars, Color Scales and Icon Sets provide a visual representation of how the cells compare to the other cells in your worksheet.
1. First, clear the previous conditional formatting rule. To achieve this, select the cells and click on Home, Conditional Formatting, Clear Rules, Clear Rules from Selected Cells.
2. Next, click on Conditional Formatting, Data Bars, Solid Fill, Blue Data Bar.
Result:
Note: You can apply all sorts of visual representations by clicking on one of the other Data Bars, Color Scales or Icon Sets options.
New Rule
If the highlight rules are not sufficient, you can always create your own rule. For example, when you want to highlight all blank cells.
1. Select the cells.
2. Next, click on Home, Conditional Formatting, New Rule...
3. Click on 'Format only cells that contain' (in most cases you will need this rule type).
4. Select Blanks from the first listbox.
4. Select any formatting style.
Result:
Did you like this free Excel tutorial? Show your appreciation, vote for us.