Since Microsoft 2007, Excel, the popular spread­sheet pro­cess­ing software, has included con­di­tion­al for­mat­ting. This type of for­mat­ting lets you visualize 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 for­mat­ting which is used across a range of different Office programs is that only cells ful­fill­ing certain criteria are high­light­ed or otherwise visually rep­re­sent­ed.

$1 Domain Names – Grab your favorite one
  • Simple reg­is­tra­tion
  • Premium TLDs at great prices
  • 24/7 personal con­sul­tant included
  • Free privacy pro­tec­tion for eligible domains

Con­di­tion­al for­mat­ting: popular areas of ap­pli­ca­tion

There are three main areas of ap­pli­ca­tion for using con­di­tion­al for­mat­ting in Excel:

  1. Vi­su­al­iza­tion of values: All numbers in a defined cell range are con­sid­ered in relation to one another. With this type of con­di­tion­al for­mat­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­light­ed.
  2. Checking data: Depending on data volume, assessing each in­di­vid­ual cell for de­vi­a­tions is a time-consuming process. That’s where con­di­tion­al for­mat­ting can be useful. By defining certain con­di­tions, only those cells are high­light­ed 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 for­mat­ting formulas in Excel and co. You can filter defined values by masking double values. Al­ter­na­tive­ly, you could also delete duplicate values.

Excel: con­di­tion­al for­mat­ting based on values

Con­di­tion­al Excel for­mat­ting is also useful when comparing a list of different values. In just a few clicks, you can compare in­di­vid­ual values with one another. A defined cell range can be analyzed and vi­su­al­ized with colors. For example, maximum values are marked in green, whilst minimum values are high­light­ed in red. Al­ter­na­tive­ly, data beams or symbols like darts can be used to represent pre­de­fined data and results.

Image: Excel: conditional formatting
Mark the cell range you wish to format, choose “Con­di­tion­al For­mat­ting,” and select from different options: “Data Bars“, “Icon Sets,” or “Com­bi­na­tion”.
Image: Conditional formatting: visualization options in Excel
Data can be vi­su­al­ized in Microsoft Excel using color scales, data bars, or sets of symbols.
Note

Earlier versions of Excel do not include multiple options for con­di­tion­al for­mat­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, Pow­er­Point, etc. and a personal domain.

Con­di­tion­al for­mat­ting based on rules

Spec­i­fy­ing con­di­tion­al for­mat­ting based on defined rules makes it a little more flexible. Because this type of for­mat­ting is based on threshold values, only certain cell divisions are con­sid­ered. Depending on your needs, you can format defined or upper/lower values. This includes pre­de­fined for­mat­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 For­mat­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­for­ma­tion will be high­light­ed.

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 and IONOS!
The Office you know, only better
  • Up to 50 GB Exchange email account
  • Outlook Web App and col­lab­o­ra­tion tools
  • Expert support & setup service

Con­di­tion­al for­mat­ting using formulas

For even more flex­i­bil­i­ty using con­di­tion­al for­mat­ting, Excel users can apply in­di­vid­ual formulas. Instead of relying on pre­de­fined Excel formulas, you can specify their own rules. This allows for unlimited pos­si­bil­i­ties 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­ized 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 For­mat­ting” to create a per­son­al­ized rule.
Image: Excel dialog “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 for­mat­ting in Excel

The following examples show con­di­tion­al for­mat­ting 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.

Image: Conditional formatting: example in Excel
Using data bars and symbols to visualize 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 for­mat­ting”
  • “Data bars”
  • “Fill with…” (make a selection)

Now, select cells C3 to C13 and click on:

  • “Con­di­tion­al for­mat­ting”
  • “Icon Sets”
  • “Di­rec­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 for­mat­ting.” Select “Manage Rules…” and “Edit Rules.”

Image: Excel menu: “Conditional Formatting“
Click on “Con­di­tion­al For­mat­ting“ and “Manage Rules” to make changes to existing formulas.
Image: Excel: Edit conditional formatting
Edit for­mat­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 for­mat­ting rules in Excel.

The dialog for the con­di­tion­al for­mat­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 for­mat­ting according to your needs.

The above is a simple example il­lus­trat­ing the pos­si­bil­i­ties of using Excel’s “Con­di­tion­al For­mat­ting” feature. Generally, the more confident you are using Excel formulas and functions, the more ef­fec­tive­ly and flexibly you will be able to use these features.

Google Workspace (Formerly G Suite)
Work smarter, together.
  • All your favorite Google pro­duc­tiv­i­ty tools
  • Business Gmail for your domain
  • Using Gmail with your domain from IONOS
Go to Main Menu