Frequently, you need to highlight certain cells in a spreadsheet to draw attention or highlight certain pieces of information. You can do this manually, but wouldn’t it be nice to apply formatting to cells automatically? And have it applied even if the cell values change? Conditional formatting can be used to apply formatting to a cell based on certain conditions. Please note that this tip is just the “tip” of the iceberg when it comes to conditional formatting!
Here is a sample selection of cells. If I want every cell that is negative to have red font, I can do that manually.
But what happens if the numbers change? You have to format each cell manually. If you setup conditional formatting for the Balance column, the red font would update automatically. Here is how to start (Excel 2010, but the process is similar in earlier versions):
Select the cells you want to format. On the Home tab, there is a button for Conditional Formatting in the Styles section.
Some default choices appear, like Data Bars and Color Scales, which are created based on the data. If you want to have control, choose New Rule… select a Rule Type, for this example we will choose “Format only cells that contain”. Choose Cell Value less than and type “0” (that’s a zero) in the last box. Click on the Format… button to choose what you want to happen if the condition is true. For our example above, the screen should look like this:
Now if the values change, the formatting will change automatically. To edit an existing rule, select the range of cells and go back to the Conditional Formatting button, but this time choose Manage Rules… Now you can edit any of the rules you have setup.