Introduction

What is conditional formatting?

Conditional formatting is a way of automatically highlighting cells in Microsoft Excel based on cell values. This highlighting might be done through different formatting tools, including – but not limited to – colors, bars, and icons.

Why do you need it?

Conditional formatting is a great way to quickly visualize your data. It helps you focus attention on specific cells in a large data set, notice trends faster, and find complex relationships between different rows or columns in a database.

Quick example

Sample task

In Figure 1 we have a list of 25 students and their test scores. You can see how conditional formatting helps you quickly – in just 3 clicks – identify the students with scores below 40. Of course, as with most other things in Excel, you can accomplish this task in a number of different ways (e.g., writing a formula in a separate column, or applying a filter). But if you’re looking for a quick and visual approach, conditional formatting is the way to go.

conditional formatting Excel

Figure1. Conditional formatting example

How to do it

Here is how you can duplicate the example mentioned above:

  1. Select the range of cells to which you want conditional formatting applied
  2. Go to menu Home >> Conditional Formatting (in Styles) >> Highlight cell rules >> Less than… (see Figure 2)
  3. In “Format cells that are LESS THAN:” field, enter “40”
  4. Press “OK
conditional formatting

Figure 2. Conditional formatting menu

If you want to know more…

Navigating to Conditional Formatting Toolbar

In general, after you selected the relevant range of cells, you can navigate to the relevant toolbar in several different ways:

  1. In the Ribbon
    • By clicking through the Ribbon menu items: Home >> Conditional Formatting (in Styles) (as in the example above)
    • By using shortcuts: Press Alt + H + L (please refer to “Shortcuts” section below)
  2. In the Quick Analysis toolbar (6 preset commands only)
    • By clicking on Quick Analysis icon in the lower right corner of the selected range (quick analysis icon)
    • By using shortcut Ctrl + Q + Tab (please refer to “Shortcuts” section below)

Different Conditional Formatting approaches

Now that we are done with the technicalities, let’s consider different approaches to setting up conditional formatting on your Excel worksheet:

  1. Using preset commands (menu options Highlight Cells Rules through Icon Sets)
    • Advantages
      • A little easier to understand at first. You can quickly figure out what each of those commands means by just clicking on it. Most of them show what they do when you just hover your mouse over them
      • Faster in some situations. Specifically, if you often use the same basic type of formatting – let’s say, highlighting top 10% of the dataset, – you can remember the shortcut and save time navigating through redundant settings
    • Disadvantages
      • Those commands just use some predetermined settings and only let you adjust a few of them. If you need to customize more, you’ll have to do it by editing Formatting Rule
      • A slower option if you’re not sure which command to use. Sometimes you lose time browsing through them, only to find out that you need to do it as a Formatting Rule anyway

For a more detailed description of preset conditional formatting commands, please refer to a separate post.

  1. Adding new conditional formatting rules (menu option New rule…)
    • Advantages
      • Most flexible approach. With Formatting Rules, you can set up virtually any conditional formatting trick you can think of (, including the preset commands discussed above)
      • Quick when you understand it. The structure of the Formatting Rule window is pretty straightforward, so, as soon as you figure it out, it’s very easy to use
    • Disadvantages
      • Some people can find it a little overwhelming at the beginning. But this impression will surely go away very quickly

For a more detailed description of conditional formatting rules, please refer to a separate post.

In general, we would recommend using preset commands for routine conditional formatting tasks that you repeat frequently enough to remember exactly which command you need to use. For any ad-hoc conditional formatting needs, it’s easier just to go straight to the Formatting Rule window and to set up exactly what you need. A good strategy would also be to start with a preset command, and then go to the Rules Manager (Home >> Conditional Formatting (in Styles) >> Manage Rules…) to adjust some settings.

Conditional Formatting Shortcuts

  • Alt + H + L: open Conditional formatting settings in the Ribbon. Then you can either
    • Continue using shortcuts by following the letter suggestions on the ribbon. Look for a letter in the lower right corner of each Ribbon icon. This letter is also underlined in the item title. E.g., letter H in “Highlight Cells Rules”:Conditional formatting shortcut
    • Or just navigate with direction keys (up / down / left / right) on the keyboardWhen you found the item you need, press Enter
  • Ctrl + Q + Tab: open Conditional formatting settings in the Quick Analysis toolbar. To select the conditional formatting option you need, navigate with direction keys (up / down / left / right) on the keyboard

Related posts

Test yourself...

Conditional Formatting Quiz

Take a quick quiz to understand how well you know Excel Conditional Formatting:

Share This