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.
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.
How to do it
Here is how you can duplicate the example mentioned above:
- Select the range of cells to which you want conditional formatting applied
- Go to menu Home >> Conditional Formatting (in Styles) >> Highlight cell rules >> Less than… (see Figure 2)
- In “Format cells that are LESS THAN:” field, enter “40”
- Press “OK”
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:
- 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)
- In the Quick Analysis toolbar (6 preset commands only)
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:
- Using preset commands (menu options Highlight Cells Rules through Icon Sets)
- 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
- 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
- Adding new conditional formatting rules (menu option New rule…)
- 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
- Some people can find it a little overwhelming at the beginning. But this impression will surely go away very quickly
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”:
- 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
Conditional Formatting Quiz
0 of 3 questions completed
Take a quick quiz to understand how well you know Excel Conditional Formatting:
You have already completed the quiz before. Hence you can not start it again.
Quiz is loading...
You must sign in or sign up to start the quiz.
You have to finish following quiz, to start this quiz:
Time has elapsed
You have reached 0 of 0 points, (0)
Question 1 of 3
Please select all relevant situations when conditional formatting can be potentially usedCorrect
You might consider reviewing conditional formatting section on our site
Question 2 of 3
What conditional formatting command would you use for highlighting the rows for all female students in the table below?
Name Test Score Gender Cohort Major Home state Student 1 80 male 1 Major A MA Student 2 38 female 3 Major A PA Student 3 64 male 4 Major B GA Student 4 63 female 1 Major A NJ Student 5 26 male 5 Major B TXCorrect
You might consider reviewing conditional formatting formulas section on our site
Question 3 of 3