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.

Cheap domain names – buy yours now
  • Free website pro­tec­tion with SSL Wildcard included
  • Free private re­gis­tra­tion for greater privacy
  • Free Domain Connect for easy DNS setup

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.

Image: Excel: conditional formatting
Mark the cell range you wish to format, choose “Con­di­tion­al Format­ting,” and select from different options: “Data Bars“, “Icon Sets,” or “Com­bin­a­tion”.
Image: Conditional formatting: visualisation options in Excel
Data can be visu­al­ised in Microsoft Excel using colour scales, data bars, or sets of symbols.
Note

Earlier versions of Excel do not include multiple options for con­di­tion­al format­ting such as data bars or symbols. To make use of the latest features in Excel, you should use the latest version of the spread­sheet software. Excel is available as part of the IONOS Microsoft 365 Business package, which also includes Word, Power­Point, etc. and a personal domain.

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…
Image: Excel: conditional formatting based on rules
Select “Con­di­tion­al Format­ting” and “Highlight Cells Rules” and then choose “Top/Bottom Rules.”

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.

Image: Text-based formatting rule in Excel
Choose the option “Specific Text” and enter the text you wish to filter by.
Excel with Microsoft 365 Business and IONOS!
The Office you know, only better
  • Up to 50 GB Exchange email account
  • Outlook Web App and col­lab­or­a­tion tools
  • Expert support & setup service

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.

zfQ8uOBoIj8.jpg 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.”

Image: New rule for conditional formatting
Select “New Rule” in “Con­di­tion­al Format­ting” to create a per­son­al­ised rule.
Image: Excel dialogue “New Formatting Rule”
Choose the option “Use a formula to determine which cells to format” and enter your formula.

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.

Image: Conditional formatting: example in Excel
Using data bars and symbols to visualise your data.

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.”

Image: Excel menu: “Conditional Formatting“
Click on “Con­di­tion­al Format­ting“ and “Manage Rules” to make changes to existing formulas.
Image: Excel: Edit conditional formatting
Edit format­ting rules in Microsoft Excel.

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

Image: Excel: Edit formatting rules
Edit format­ting rules in Excel.

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.

Image: Conditional formatting: Excel example of amended rule
Amend the condition within the format­ting according to your needs.

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.

Google Workspace (formerly G Suite)
Working smarter, together
  • Familiar Google tools all in one place
  • Using Gmail with your domain from IONOS
  • Configure business Gmail for your domain
Go to Main Menu