Excel: con­di­tion­al format­ting – what is it?

Since Microsoft 2007, Excel, the popular spread­sheet pro­cessing software, has included con­di­tion­al format­ting. This type of format­ting lets you visualise large and complex data sets, allowing you to spot data trends and missing data more easily and quickly. The cells to be dis­tin­guished depend on pre-specified fixed con­di­tions. The advantage of con­di­tion­al format­ting which is used across a range of different Office programs is that only cells ful­filling certain criteria are high­lighted or otherwise visually rep­res­en­ted.

Con­di­tion­al format­ting: popular areas of ap­plic­a­tion

There are three main areas of ap­plic­a­tion for using con­di­tion­al format­ting in Excel:

1. Visu­al­isa­tion of values: All numbers in a defined cell range are con­sidered in relation to one another. With this type of con­di­tion­al format­ting, the ap­pear­ance of the cells is based on the size of the values. If values are negative, larger, or smaller than the other numbers, they are high­lighted.
2. Checking data: Depending on data volume, assessing each in­di­vidu­al cell for de­vi­ations is a time-consuming process. That’s where con­di­tion­al format­ting can be useful. By defining certain con­di­tions, only those cells are high­lighted that you are looking for.
3. Filter double and distinct values: If you are looking to create a list of distinct values, you can use con­di­tion­al format­ting formulas in Excel and co. You can filter defined values by masking double values. Al­tern­at­ively, you could also delete duplicate values.

Excel: con­di­tion­al format­ting based on values

Con­di­tion­al Excel format­ting is also useful when comparing a list of different values. In just a few clicks, you can compare in­di­vidu­al values with one another. A defined cell range can be analysed and visu­al­ised with colours. For example, maximum values are marked in green, whilst minimum values are high­lighted in red. Al­tern­at­ively, data beams or symbols like darts can be used to represent pre­defined data and results.

Close

Close

Con­di­tion­al format­ting based on rules

Spe­cify­ing con­di­tion­al format­ting based on defined rules makes it a little more flexible. Because this type of format­ting is based on threshold values, only certain cell divisions are con­sidered. Depending on your needs, you can format defined or upper/lower values. This includes pre­defined format­ting 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…

Close

If instead of working with numbers you tend to work with text entries and want to highlight cells con­tain­ing certain terms, click on “Highlight Cells Rules” > “Text” and enter the text you want to filter by. Click “OK” and the in­form­a­tion will be high­lighted.

Close

Con­di­tion­al format­ting using formulas

For even more flex­ib­il­ity using con­di­tion­al format­ting, Excel users can apply in­di­vidu­al formulas. Instead of relying on pre­defined Excel formulas, you can specify their own rules. This allows for unlimited pos­sib­il­it­ies when it comes to checking your data. The right formula can, for example, be used to compare a range of numbers to a reference table, as shown in this YouTube video.

To display this video, third-party cookies are required. You can access and change your cookie settings here.

To create a per­son­al­ised 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.”

Close

Close

User examples for con­di­tion­al format­ting in Excel

The following examples show con­di­tion­al format­ting applied to a company’s profits in column B using data bars. Coloured arrows in column C show the gains and losses compared to the previous month.

Close

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

• “Con­di­tion­al format­ting”
• “Data bars”
• “Fill with…” (make a selection)

Now, select cells C3 to C13 and click on:

• “Con­di­tion­al format­ting”
• “Icon Sets”
• “Dir­ec­tions”

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 “Con­di­tion­al format­ting.” Select “Manage Rules…” and “Edit Rules.”

Close

Close

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

Close

The dialogue for the con­di­tion­al format­ting 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.

Close

The above is a simple example il­lus­trat­ing the pos­sib­il­it­ies of using Excel’s “Con­di­tion­al Format­ting” feature. Generally, the more confident you are using Excel formulas and functions, the more ef­fect­ively and flexibly you will be able to use these features.