Introduction

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.

If you want to know more…

Description of preset conditional formatting commands

Conditional formatting Excel

Figure 1. Conditional formatting options

See Figure 1 to understand the differences between various conditional formatting commands as applied to the same dataset.

  1. 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
  2. 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
  3. 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
  4. 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.
  5. 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.

Related posts

Test yourself...

Conditional Formatting Quiz

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

Share This