Create Dynamic Spreadsheets with Conditional Formatting

Conditional formatting is a built-in Excel feature that—with proper implementation—can provide very useful and responsive feedback to users. By providing dynamic visuals, users will be able to gain instant insights into data and dashboards.

Conditional Formatting Overview

This post within the Spreadsheet Design series continues the focus on Layout in Excel. Today’s topic of conditional formatting was briefly touched upon in the Building a Custom Listbox in Excel with Formulas and Data Validation with Excel Formulas exercises. This post will dive deeper into conditional formatting features and provide an overview of the many options that are available.

Conditional formatting options can be accessed through (1) Home > (2) Conditional Formatting in the ribbon menu. The values you intend to format—either a group of cells or a single cell—should be selected before accessing the menu.

Path to the conditional formatting menu.
Path to the conditional formatting menu.

Note that there are some backwards compatibility issues with conditional formatting. The 2007 version of excel implemented many improvements to the feature. These improvements included the addition of icons, gradients, and more complex test options. The most valuable improvement, however, is the ability to apply conditional formatting to cells outside of the test range. For example, you could create a conditional format to test if cell A1 was equal to 1, which if true, would change the fill color of cell D2 green. Prior to 2007, you would only be able to change the fill color of cell A1. This improvement provides opportunities for more dynamic spreadsheets that can respond to various changes made by the user.

The Condition Formatting menu contains 5 primary styles, and 3 other options. The following will walk through each menu item.

Highlight Cell Rules

2016-10-28-2

The Highlight Cell Rules menu provides automatic formatting for values that a meet a single specific condition set by the spreadsheet developer. Rules can be applied to either a single value, or a range of values. The rules let you either specify a static value that will not change, or reference another cell for a value that can potentially change.

Greater Than

2016-10-28-4
Greater Than

The Greater Than option applies a format when a cell value is greater than a specified value. You can enter a test value in the field, or use the cell selection tool to select a test value contained in a cell.
2016-10-28-3

Less Than

2016-10-28-6
Less Than

The Less Than option applies a format when a cell value is less than the specified value. In this example, the rule is referencing a test value in cell F12, which contains the value 50.

2016-10-28-5

Between

2016-10-28-6
Between

The Between option applies a format when cells are between two specific values. This dialog box is slightly different, as it contains an input field for two test values. You can either enter a specific test value or reference a value within a cell.

2016-10-28-7

Equal To

2016-10-28-10
Equal To

The Equal To option applies a format to a cell with an exact numeric value. The dialog box requests a specific number or cell reference with a specific number.

2016-10-28-9

Text That Contains

2016-10-28-12
Text That Contains

The Text That Contains option applies a format to a cell with an exact text value. The dialog box requests a specific string of text or cell reference with specific text. You can specify numbers with this option, providing similar results to Equal To.

2016-10-28-11

A Date Occurring

2016-10-28-14
A Date Occurring

The A Date Occurring option tests dates against a specific parameter. This test changes the format of the example, requiring dates to be listed. The default value in the dialog box is “Yeterday”, but you may also test for Today, Tomorrow, In the last 7 days, last week, this week, next week, last month, this month, next month.

2016-10-28-13

Duplicate Values

2016-10-28-16
Duplicate Values

The Duplicate Values option tests whether a value occurs more than once in a range. There is also an option to test if a value is unique in a range. A dropdown list is shown providing the options Duplicate or Unique.

2016-10-28-15

Top/Bottom Rules

2016-10-28-49
Top/Bottom Rules

Top/Bottom Rules apply conditional formatting to a range based on relative values within that range. Unlike the Highlight Cell Rules, these rules provide dynamic formatting to data based on how each data point relates to the data group. This is very helpful for spreadsheets such as financial reports, which can change on a month-to-month basis and tend to show a mix of good or bad results. These rules can make it easy to highlight good or bad figures without making any significant changes to formatting.

Top 10 Items

2016-10-28-18
Top 10 Items

The Top 10 Items option will apply formatting to the highest 10 values in a group of data. The dialog does allow you to specify more than or less than 10 values, but the default is 10.

2016-10-28-17

Top 10%

2016-10-28-20
Top 10%

The Top 10% option will apply formatting to the highest 10% of the values in a group of data. The dialog does allow you to specify more than or less than 10 values, but the default is 10. To clarify: this rule will not format the top 10% of relative value (e.g. does not test for 90 if the range is 1 to 100), but the individual elements that are the highest 10% in the distribution.

2016-10-28-19

Bottom 10 Items

2016-10-28-22
Bottom 10 Items

The Bottom 10 Items option is the inverse of the Top 10 Items. The dialog does allow you to specify more than or less than 10 values. The default is 10.

2016-10-28-21

Bottom 10%

2016-10-28-24
Bottom 10%

The Bottom 10% option is the inverse of the Top 10%. The dialog does allow you to specify more than or less than 10 values, but the default is 10. This will test the bottom 10% of the distribution of numbers, not by value.

2016-10-28-23

Above Average

2016-10-28-26
Above Average

The Above Average option will format values which are above the average of the selected range. The dialog box does not provide any options outside of basic formatting.

2016-10-28-25

Below Average

2016-10-28-28
Below Average

The Below Average option is the inverse of the Above Average option and will format values which are below the average of the selected range. The dialog box does not provide any options outside of basic formatting options.

2016-10-28-27

Data Bars

2016-10-28-29
Data bar menu

General Options

Bars with gradient.
Bars with gradient.
Bars without gradient.
Bars without gradient.

Data bars will provide a “bar chart” visualization of where a specific number stands within a range. The default value will be zero to the maximum value of the range, or the minimum to the maximum of the range if the minimum value is below zero. The option initially provides two general formats: gradient and solid, along with several colors to choose from.

More Rules

2016-10-28-32

A More Rules option exists to provide additional customization.

Format Style

Jump between different formatting styles and rule sets.

Show Bar Only

Will make the numbers invisible, only showing the bar.

Value Range Table

The next 4 fields are within a small table to specify the minimum and maximum range of the fill visual. The first row requests Type. This will determine how the minimum and maximum ranges are calculated. Values are either Lowest Number, Number, Percent, Formula, Percentile, or Automatic. The next row contains the value fields. Depending on what you select as a Type, you may or may not need to select a value (Automatic and Lowest Number do not require values).

Bar Appearance

The Bar Appearance section provides good level of control over the visual formatting of the bar, including fill color, border style, border color, and context. There is also a button for “Negative Value and Axis” which applies special formatting to negative values.

2016-10-28-33

Color Scales

2016-10-28-34
Color scale menu

General Options

2016-10-28-35
Color Scales

Colors Scales provide various color shading options for a group of values. This format is another effective means of displaying dynamic numerical data and can denote large and small numbers in “hot” and “cool” colors.

More Rules

2016-10-28-36

A More Rules option exists to provide additional customization.

Format Style

Select either 2-Color Scale or 3-Color Scale

Value Table

The value table will show columns headings of Minimum and Maximum if a 2-Color Scale format style was selected. If a 3-Color Scale is selected, a third column for Midpoint will be shown between the existing columns. The first row is used to specify Type. The Type denotes how the value is treated, whether it be a number, percent, or formula. The Value field can be typed directly in, or reference a cell in the document. The Value field is not required if the type Lowest Value/Highest Value is selected. Colors can be selected to customize the color transition between low and high values.

Icon Sets

Icon set menu
Icon set menu

General Options

2016-10-28-38
Icon Sets

Icon sets give your data a nice visual touch by incorporating symbols into conditional formatting rules. These are particularly useful for dashboards and can give users a quick visual status update on tasks, performance, or results. In a previous exercise on data validation, the checkmark icon set was used for validation feedback.

More Rules

Conditional formatting menu.

A More Rules option exists to provide additional customization.

Format Style

Keep this as Icon Sets. There’s also a button to Reverse Icon Order, which will switch between the high/low value icons.

Icon Style

Provides an option to switch between all the icon sets in the original menu.

Value Table

The value table allows you to tailor the conditional format to show icons within specific constraints. Within the Icon column, you can mix and match different icons. In the value column, you can set operators and numeric constraints to which icons show depending on a range. This range can be specified by Number, Percent, Formula, or Percentile

Other Options

New Rule

2016-10-28-40

The New Formatting Rule dialog allows for the selection of even more specific formatting conditions and tests. Upon selecting the New Rule option, you will see a 2-Color Scale dialog box. Generally, the options in this menu provide a similar result than the options listed above. This menu, however, does provide some additional flexibility and options. The sections below highlight the selections in the Select a Rule Type listbox.

Format only cells that contain.

2016-10-28-41

This formatting dialog mimics the features of the Highlight Cell Rules, but provides a few additional options. For example, the first field allows for the testing of values if they are blank or contain errors. Likewise, you can select an option for Equal to or greater/less than (this is not an option in the main options). This dialog box can also be accessed by navigating to Highlight Cell Rules > More Rules.

Format only top or bottom ranked values

2016-10-28-42

This option is slightly more simple and mimics the features of the Top/Bottom Rules. Use this dialog box if you want to go beyond the preset formatting provided in the main menu. This dialog box can also be accessed by navigating to Top/Bottom Rules > More Rules.

Format only values that are above or below average

2016-10-28-43

This is another screen that mimics the features of the Top/Bottom Rules. The dropdown list provides the options above or below average, but also adds flexibility to include equal to the average, and up to 3 standard deviations above or below average. This is also a good method of adding borders and other custom formats to the rule.

Format only unique or duplicate values

2016-10-28-44

This mimics the last option in the Highlight Cell Rules menu. Select either “duplicate” or “unique”, then set formats. Additional flexibility is with formatting is provided when pressing the format button.

Use a formula to determine which cells to format

2016-10-28-45

2016-10-28-46
Formula condition is met.
2016-10-28-47
Formula condition is not met.

You may have seen Formula as a value test attribute in the previous rules. This feature allows for more complex criteria and calculations for your conditions. In the example, above, the formatting rule tests if there are less than 4 values in a list. Using the formula =COUNTIF($A$2:$A$5,1)<4, the sheet will highlight a specified range (in this example, the range is the same as the range in the formula, but it can be different), and apply formatting.

Clear Rules

This option can be used to clear all rules from selected cells, or the entire sheet. Select Clear Rules from Selected Cells or Clear Rules from Selected Sheet.

Manage Rules

2016-10-28-48

Clicking on Manage Rules will open the Conditional Formatting Rules Manager. By default, this dialog box will present all the rules currently associated with the cell. The Show formatting rules for dropdown menu offers the option to view all formulas on the active sheet or other sheets.

Within the dialog box, you can add a New Rule, Edit an existing rule, or Delete an existing rule.

Other Notes

It is possible to add multiple conditional formats to single cells or ranges. This can be done by either going through the steps for each format criteria, or using the Conditional Formatting Rules Manager. While it is advisable not to overdo it with formats, this is a good feature if there is a need to, for example, format the upper and lower quartile of a set of numbers.

Leave a Reply