Conditional Formatting On Microsoft Excel

You might have a worksheet with thousands of rows of data where identifying the patterns and trends would be extremely difficult from examining the raw information. This is where conditional formatting comes in. You might say that it is similar to sparklines and charts in the way you can visualize data and make worksheets easier to understand.

Understanding conditional formatting

You can use conditional formatting to automatically apply formattings such as icons, colours, and data bars to one or more cells depending upon the cell value. You will need to create a conditional formatting rule to do this. For instance, a conditional formatting rule could be: If the value is less than $1000, colour the cell yellow. You can apply this rule to be able to quickly see which cells contain lesser values than $1000.

To create a conditional formatting rule:

Let us take, for instance, that we have a worksheet containing all the data from the sales department and we want to find out if the sales executives met their monthly goal for last month. And, we will take the goal for the sales team as $2000/ month, so here we will create a conditional formatting rule for any cells that contain a value higher than $ 2000.

1. For the conditional formatting rule, first, select the desired cells.
2. Move to the Home tab, click on the Conditional Formatting command. You will see that a dropdown menu has appeared.
3. Then, over the conditional formatting type, move your mouse and select the desired rule from the menu that appears. Here we want to highlight the cells that are greater than $2000.
4. On the dialogue box that appears, enter the desired value(s) into the blank field. Here we will enter 2000 as our value.
5. From the drop-down menu, select a formatting style. Then click on OK.
6. Now, to all the selected cells, conditional formatting will be applied.

Conditional formatting presets

There are several predefined styles or presets on excel which you can use to apply conditional formatting to your data quickly. There are three categories of presets:

• Data Bars, much like bar graphs, are horizontal bars that are added to each cell.

• The colour of each cell is changed based on its value by the Colour Scales. Two or three-colour gradients are used for each colour scale. For instance, if you take the Green-Yellow-Red colour scale, green has the highest values, yellow has the average ones and the lowest values are red.

• Based on its value, each cell is added with a specific icon using the Icon Sets.

To use preset conditional formatting:

1. For the conditional formatting rule, select the desired cells.
   
2. Then, just click on the Conditional Formatting command and a dropdown menu appears.
3. Move the mouse over the desired preset, then choose a preset style from the menu that appears. Over the desired preset, hover your mouse and choose the preset style from the menu that appears.
   
4. Conditional formatting will be applied.

Removing conditional formatting

To remove conditional formatting:

1. First, click on the Conditional Formatting command and a dropdown menu appears.
2. Over Clear Rules, move the mouse and choose the rules that you want to clear. For instance to remove all the conditional formatting rules from the worksheet select Clear Rules from the Entire Sheet.
   
3. The conditional formatting will be removed.

To edit or delete individual rules, click on Manage Rules. If you have applied multiple rules to a worksheet this feature comes in real handy.