Excel Training: Conditional Formatting
As its name suggests, conditional formatting is a formatting that applies to cells under condition.
It allows to modify:
- The background color of the cell
- The text formatting
- The borders
- The data format
Add a conditional formatting
To apply a conditional formatting to the following table (lesson-8.xlsx), select the range of cells containing the numbers and click on "Conditional Formatting" from the "Home" tab.
The objective of this first conditional formatting will be to color in green all the numbers greater than 48000. So choose the rule "Greater Than":
Then enter the value 48000, the desired color and validate:
The formatting is then applied to the cells.
Note that if you later modify the numbers contained in this range of cells, the formatting will be automatically updated:
Modify a conditional formatting
To modify the conditional formatting that we just added, click on "Manage Rules":
The rules manager is then displayed:
From this window, you can:
- Add new rules
- Delete rules
- Change the order of the rules
- Modify the cell range of each rule
- Modify the formatting of each rule
Now double-click on the rule to modify it (or click on the "Edit Rule" button).
From this window, you can modify the rule to apply it for example to the first 15% of the selection:
Then click on the "Format" button to modify the formatting:
And validate:
Shades, bars and icons
Special and ready-to-use conditional formats are available in "Conditional Formatting".
Example of a "Data Bars" conditional formatting:
Example of a "Color Scales" conditional formatting:
Example of a "Icon Sets" conditional formatting:
