Conditional formatting is a feature from Excel that allows you to automatically format a cell based on its contents. For example a cell could show a red fill colour if its contents are below a certain amount, or green if it’s over a certain amount, and automatically update if the value in the cell changes.
Conditional formatting allows you to change nearly everything about a cell, from borders and fill colours through to font colours and styles. You can also display icons such as arrows or traffic lights which display red, yellow and green depending on the contents of the cell. Excel provides several pre-set conditional formats that you can use, or you can use a formula to create your own.
.png)
Select the cell or cells you want to apply conditional formatting to and click the Conditional formatting dropdown list on the Home tab. Hover your pointer over the “Highlight Cells Rules” and a list of number based conditions will appear, such as Greater Than, and Less than. These rules are simple rules that will apply a format if the conditions you set are matched.
.png)
Select one of the options, such as Greater Than, and then enter the number you test the data in the cells with, such as 200000. Next click the dropdown format list and select one of the suggested formats or the custom format option to set your own. When you click OK, using my example, any cells over 200000 will be formatted the way you choose.
.png)
To use icons, such as arrows or traffic lights that automatically change as your data changes, click the conditional formatting dropdown list and select the “Icon sets” option. Here you have several options to choose from. Most sets have three icons that are automatically applied to the low, middle and high values in your range.
.png)
Once you have select the icons to use, you can change the way they work by clicking the “Manage Rules” option on the Conditional Formatting dropdown list. Double click the rule you want to edit. You can hide the values in the cells and display only the icons by ticking the “Show icon only” checkbox, and you can also edit the ranges each icon is applied to.
