Conditional Formatting in Excel

One of Excel’s biggest utilities is its capacity to reduce the amount of time you need to analyse the data on a table, enabling you to know the parameters and/or regularities of the information in it.

In Excel, conditional formatting allows us to assign a format to a cell that runs under one or several rules (previously stablished).

There are also some pre-defined cell formats that can be found in the Conditional Formatting tab.

Conditionalformatinginexcel1

For this example, we will set a format for cells with values above the average and another format for those values below the average. To do so we must first choose New Rule from the menu shown in the picture above.

Once the New Formatting Rule window comes up on the screen, we’ll select Format only values that are above or below average.

 Conditionalformatinginexcel2

We will also have the option to choose whether to apply the format to values below average or above.

After this, we must select the format we want to apply to our cell, by clicking on Format, which will bring up the following window on your screen:

Conditionalformatinginexcel3

Here we can choose the fill color (Fill tab) of those cells that meet the criteria previously specified on the rules.

The Border tab will allow you to choose the color for the cell border, and the Font tab will let you choose and modify the Font type, size and style for the contents of the cell in question, like Bold or Italic, to name a few.

After making our formatting choices we click OK, and then OK again for the other window that will still be open when we close the one for New Formatting Rule.

Finally, we have applied the conditional formatting to the cells which contents are above or below average of a certain range.

Conditionalformatinginexcel4

Note: The example was set to show the values above average.