Excel: conditional formatting – what is it?

Conditional Excel formatting is also useful when comparing a list of different values. In just a few clicks, you can compare individual values with one another. A defined cell range can be analyzed and visualized with colors. For example, maximum values are marked in green, whilst minimum values are highlighted in red. Alternatively, data beams or symbols like darts can be used to represent predefined data and results.

Specifying conditional formatting based on defined rules makes it a little more flexible. Because this type of formatting is based on threshold values, only certain cell divisions are considered. Depending on your needs, you can format defined or upper/lower values. This includes predefined formatting rules for the top 10 upper/lower or 10% of elements. By clicking on “Highlight Cells Rules,” you can also choose between:

• Larger than…
• Smaller than…
• Between…
• Equal to…
• Text…
• Date…
• Double values…

If instead of working with numbers you tend to work with text entries and want to highlight cells containing certain terms, click on “Highlight Cells Rules” > “Text” and enter the text you want to filter by. Click “OK” and the information will be highlighted.

To create a personalized rule using formulas, click on “New Rule…” and choose “Use a formula to determine which cells to format.” Enter the formula in the input box, select your desired format, and click “OK.”

The following examples show conditional formatting applied to a company’s profits in column B using data bars. Colored arrows in column C show the gains and losses compared to the previous month.

In order to add the data bars in column B, select cells B2 to B13 and click on the following:

• “Conditional formatting”
• “Data bars”
• “Fill with…” (make a selection)

Now, select cells C3 to C13 and click on:

• “Conditional formatting”
• “Icon Sets”
• “Directions”

If, for example, you want to show an upward trend in column C only when profits increase by at least €5 compared to the previous month, the rules need to be edited. Mark cells C3 to C13 and click on “Conditional formatting.” Select “Manage Rules…” and “Edit Rules.”

Now, change the “>” value for the green arrow from “0” to “5” and click “OK.”

The dialog for the conditional formatting manager will pop up again. Click “OK” to confirm your chosen changes for the selected cell range. The upward trend (green arrow) in cell C7 will no longer be shown because the profit increase of at least $5 was not achieved from May to June.

The above is a simple example illustrating the possibilities of using Excel’s “Conditional Formatting” feature. Generally, the more confident you are using Excel formulas and functions, the more effectively and flexibly you will be able to use these features.