What are preset conditional formatting commands?
Preset conditional formatting commands are Excel shortcuts that help you quickly apply standard types of conditional formatting without the need to adjust too many settings.
Why do we need preset conditional formatting commands?
As discussed in our “Conditional Formatting 101” post, conditional formatting might be a great way to quickly visualize your data in Excel. And the easiest approach to learning conditional formatting is by using the preset conditional formatting commands that Excel offers.
On the one hand, this is a less flexible approach than creating conditional formatting rules from scratch. But, on the other hand, preset commands speed up some routine formatting tasks considerably – especially when you know exactly which command you’re looking for.
Description of preset conditional formatting commands
See Figure 1 to understand the differences between various conditional formatting commands as applied to the same dataset.
- Highlight Cells Rules
- Greater Than… / Less Than… / Between… / Equal To… – highlight the cells based on the number values that they contain. Just set appropriate number limits, and you are good to go. Excel suggests average of the selected dataset as a default option
- Text that Contains… – highlight the text cells that contain a specific word or phrase
- A Date Occurring… – highlight the date cells based on how far the date is from the current date (Yesterday, Tomorrow, Last month, etc.)
- Duplicate Values… – highlight only the cells with duplicate or unique values as compared to other cells in the selected range
- Top/Bottom Rules
- Top 10 Items… / Top 10 %… / Bottom 10 Items… / Bottom 10 %… – highlight the cells that contain the highest / the lowest numbers in a data set. Despite what the titles suggest, you are not limited to 10 items / % only, and can set whatever limit you prefer
- Above Average… / Below average… – highlight all cells with values above or below average for the selected range
- Data Bars – an amazing feature that builds a horizontal bar in each cell on top of the actual value. Excel automatically assigns the maximum value in the range to be represented by the longest bar. If maximum value changes later, the length of all bars in the dataset updates accordingly
- Color Scales – creates a heatmap
by filling each cell with different shades of two or three colors. Each shade represents the value of the cell relative to other cells in a range.For example, one of the formatting options would paint the largest number in green, the lowest number in red. The numbers higher than average would be light green or yellow, everything lower would be orange or light red.
- Icon Sets – split all numbers from a data set into 3 – 5 equal buckets and assign an icon to each of the buckets.For example, if you apply ‘3 Flags’ format to a set of positive numbers below 100, everything below 33 will get a red flag, above 67 – a green one, and numbers in between will be flagged yellow.
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