Top Excel Conditional Formatting Tips
After spending hours, days, or weeks in Excel searching through data, creating PivotTables and PowerPivots to find the answers you want those answers to POP OUT in your reports. Here are 26 great Excel formatting tips to help you create awesome reports for your consulting clients.
Conditional formatting in Excel applies formatting to cells when the cell’s content meets specific conditions, such as the content being greater than a number you specify.
These top 26 best tips on Excel conditional formatting begin with the basics tips and progress to advanced tips, like using conditional formatting to find errors, using wildcard matches to find items in lists, and creating dynamic heat maps. These tips come from research on top-ranked Excel websites and judged by 30 years of using Excel to analyze strategic and operational performance.
“In the beginner’s mind there are many possibilities, but in the experts, there are few.”
Shunryu Suzuki (1905-1971)
Conditional Formatting Basic and Tutorial
1. Conditional formatting tutorial basics
Conditional formatting is a great tool for visually highlighting cells that contain values meeting conditions you specify, for example,
- Highlight dates in this month or dates within a range
- Highlight cells containing specific words
- Highlight the Top 10 or Bottom 10 values in a list
Highlighting by conditional formatting can be with font color, font style, solid or shaded cell background colors, icons, or data bars (shaded horizontal columns).
You set it up once and let it go. It’s like an automated detective. All you need to do is glance at the results once in a while to see what the detective has found.
Click here for a free tutorial on conditional formatting from Microsoft Support
The basic steps for applying a conditional format are,
1. Select the range of cells to receive a conditional format.
2. On the Home tab, in the Styles group, select Conditional Formatting. A menu displays formatting options.
3. Choose one of the top five menu options for predefined conditional formats.
3. Choose New Rule to manually create a new formula to identify cells to be formatted and specify the format.
3. Choose Manage Rules to modify, change the priority, or delete an existing rule.
2. Managing existing rules
Manage rules when you need to modify the priority in which formats are applied, edit the conditional formula, or change the format.
To manage an existing rule,
1. On the Home ribbon, in the Styles group, choose Conditional Formatting, then select Manage Rules. The Conditional Formatting Rules Manager dialog box will display.
2. In Show Formatting Rules For select where the rules are located.
3. Choose New Rule, Edit Rule, or Delete Rule as needed. Change the priority in which rules are applied by selecting a rule, click on its name, then click the up or down arrow at the top of the list to move the rule up or down.
3. Using predefined conditional formatting rules
Excel has predefined rules that cover the most frequent criteria and formatting used in conditional formatting. To apply these built-in rules, select the cells you want to have conditional formatting, then, on the Home ribbon, in the Styles group, select Conditional Formatting to see your choices of predefined conditional formatting.
4. Use predefined rules for simple conditions
Predefined conditional formats work well for simple conditions,
Highlight Cells Rules
Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring (relative date reference), Duplicate Values
Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, Below Average
Gradient Fill and Solid Fill for column charts in the cell background based on the distribution of values
Gradient Color fills in the cell background based on the distribution of values
Icon sets, such as colored arrows or colored stoplights, based on the distribution of values
If predefined rules will not work for you, see Tip 8 and try using a New Formatting Rule and select Rule Types.
5. Predefined rules can also apply custom formatting
Predefined rules are quick to apply because you can choose predefined conditions and predefined formats. This Between dialog box enables you to choose upper and lower limits for a numeric range.
However, using predefined conditional formats does not limit you to just the formats in the drop-down list. For example, the pre-defined formats shown in the drop-down list at right are not your only formats. To customize the format, choose Custom Format at the bottom of the list to create any format.
6. Finding and selecting cells and ranges with conditional formats
To find cells with conditional formatting, press F5, the Go To key, to display the Go To dialog box. Select the Special button, then select the Conditional Formats option and click Ok.
Cells containing conditional formats will be selected. At this point you can manage or delete the conditional formats using the tips in this list.
Tip: You can step through selected cells to see their formulas while maintaining the total selected range. To step through a selection cell-by-cell, press Tab to move forward or Shift+Tab to move backward.
7. Deleting conditional formatting
To delete conditional formatting select the range, table, or Pivot Table that has conditional formatting. (See Tip 6 above to learn how to select the range.)
Delete conditional formatting by selecting Clear Rules from the Conditional Formatting menu. You can clear Selected Cells, Entire Sheet, This Table, and This PivotTable.
Mid-Level Conditional Formatting Tips
8. Use Rule Types to customize predefined rules
Use Rules types if you need to add more conditions than what are available in predefined rules. To use Rule Types,
1. Select the cells or range of cells to be formatted.
2. On the Home ribbon, in the Styles group, select Conditional Formatting, and New Rule.
3. Select the Rule Type you want from the top list.
4. From the Format Style drop-down select the type of conditional formatting you want, for example, a color scale or an icon set. In this figure, Icon Set is selected.
5. The lower part of the Edit the Rule Description box changes so you can customize the Rule Type. In this case, it shows options for controlling icons. Choose Ok.
Use the last rule type, Use a Formula to Determine Which Cells to Format, if the top five Rule Types do not give you capability. You will need to use the “Use a Formula…” Rule Type if your conditional formatting includes a complex formula or has AND, OR, and NOT conditions.
9. Create dynamic conditional values by referencing a worksheet cell
Remember that any Value box with a range selector icon,
can contain a cell reference as well as a static value.
Click the range selector icon to select a cell that will contain the value you want as a condition.
Putting the conditional value in a worksheet cell enables you to change it easily as many of the advanced tips and download file show. For example, you may want to reference cells on the worksheet that contain dynamic values for Upper and Lower limits.
10. The order of rules in the Rules Manager prevents conflicts
Cells can have multiple conditional formatting rules. You can see all the rules for a cell(s) in the Rules Manager. Rules appear in the Rules Manager in a top to bottom order and conditions are tested and formats applied in this same order.
To display the Rules Manager, on the Home ribbon, in the Styles group, select Conditional Formatting, then click Manage Rules.
The Rules Manager tests rules starting with the topmost rule and working down. It applies formatting when a rules condition returns TRUE and continues down the list if there are no conflicts in formats. See Tip 11 if you want the Rules Manager to apply no other formats after the first TRUE condition.
Rules Manager stops if there is a conflict in applying formats. For example, there is no conflict if one rule changes the cell background color and the next changes the font. However, a conflict does occur if the first rule changes the font and a following rule attempts to also change the font. When that occurs the Rules Manager stops.
11. Prevent multiple conditional formats by stopping at the first TRUE condition
If you want the Rules Manager to stop when it meets the first TRUE condition, then select the Stop If True checkbox on the right side of the Rules Manager.
Use this, if a rule at the top of the list formats the font to bold, and a lower rule in the list formats the cell background to green. These formats do not conflict so the Rule Manager might apply both bold and green background to a cell meeting both conditions. However, if you never want cells with bold font to have a green background you should select Stop If True for the rule with bold font.
12. Using Absolute and Relative References in conditional formulas
When you create your own conditional formula (Tip 16) and apply it or copy it across a range, then you must consider Absolute and Relative References in the formula, just as you would when copying a formula across a range.
For example, in the following figure the range $C$5:$H$36 has a conditional format applied to it. The condition being tested is,
Create this formula as though it applies to the topmost cell in the Units Sold column, $G5, and will be copied down through the entire range that is receiving the conditional format. Because it is being copied across and down this range, you don’t want column G to change. To fix column G make it an Absolute reference, $G. However, the row must change as the test condition is copied down all rows, so the row number uses a Relative reference, 5 (with no $).
Here are a few simple rules for how to use Absolute and Relative reference in conditional formatting.
When the cell being tested for a condition is in a list,
The formula should use an Absolute column reference, $G5, to the topmost row containing the cell being tested. This fixes the column, $G, so the column reference will not change as the tests compare each cell in the range C5:H36.
When the cell being tested for a condition is in a row,
The formula should use an Absolute row reference, for example, P$35 (not shown in this example). This fixes the row reference so the conditional tests will always run against the fixed row, $35.
When you compare two cell references in a list that are in the same row,
Both cell references should use Absolute column references, for example, =$G5>=$I5, so the column references will not change, but the row references can change down the list.
When you reference a cell outside a list to be tested against,
The cell outside the list, for example, a lower limit in a conditional format, should use Absolute row and column references, $F$6, so neither column nor row reference changes for each cell in the list.
Many of the examples in the advanced tips section show the use of Relative and Absolute References.
13. Extending the number of conditional formats
Conditional formatting is limited to three conditional formats per cell. However, the cell also has its original format, before conditional formats are applied. So each cell can have four possible formats.
In addition to original and conditional formats, you can use custom cell formatting on numbers to apply color and numeric date formats unique to positive, negative, zero, or text values.
14. Using Conditional Formatting with PivotTables
You can apply conditional formatting to PivotTables, but you may be in for a surprise. What happens is that conditional formatting works for the range you apply it to, but if the refreshed PivotTable expands beyond the original formatted area the expanded area will not be formatted.
One solution to this is to format the extra area around the PivotTable to allow for possible expansion. If you do this, just remember that any conditional format applied to this expansion area, such as shading alternate rows, should appear with a correct format whether or not it is occupied with the PivotTable data. (You don’t want alternating rows highlighted where there is no data.)
15. Conditional Formatting using Icons and a dynamic value range
Icons of red, yellow, or green stoplights or arrows are excellent for use in executive reports to highlight KPIs needing attention. In this example predefined conditional formats are used to create colored arrow icons. The values used for upper and lower limits for the conditions are created with Data Validation lists.
The green up-arrow icon uses the upper-limit value stored in $G$2. The red down-arrow icon uses the lower-limit icon stored in $H$2. Notice that the Type is set to Number, not Percent.
16. Create custom conditional formatting for complex conditions
When you need conditional formats that have rules too complex for predefined conditional formats, then you need to create your own formulas.
Custom conditional formats may depend upon complex calculations based on cell values inside or outside the range being formatted. These custom formulas often contain AND, OR, and NOT logical conditions that are not available in predefined conditional formatting.
To create custom conditional formatting,
1. Select the cells you want formatted.
2. On the Home tab, in the Styles group, click on Conditional Formatting, then select New Rule to display the New Formatting Rule dialog box.
3. At the bottom of the Select a Rule Type list, select Use a Formula to Select Which Cells to Format.
4. In the Format Values Where this Formula is True, you must enter a formula that returns TRUE when the conditional format should be applied. The formula must result in TRUE for the formatting to be applied. For example, you might use a formula like this to test whether values in column B of a list are equal to the value in cell $B$5 outside the list,
5. Click Format and define the format you want applied when this condition is TRUE. Click Ok.
6. Click Ok.
Many formula examples are in following tips.
17. Testing conditional format formulas in the worksheet
Conditional formulas can be complex and difficult to test when entered directly into the Rule Manager. An easy solution is to build the formula in the worksheet so you can see whether it returns TRUE or FALSE correctly.
In this figure, the conditional format in I5 tests the Item entered in E5 against three text Items. If the formula finds a match, then the formula returns TRUE. The formulas in column I are there only to test the formula before entering the formula into the Excel’s Conditional Formatting.
The formula is,
This is an illustration. In most cases you would want to make the text dynamic.
Copying the formula from cell I5 into the Rule Description area of the New Formatting Rule or Edit Formatting Rule dialog box identifies which rows will receive conditional formatting.
18. Conditional Formatting using a fixed number
One of the simplest conditions tests a cell’s content against a static number. If the formula returns TRUE, then the row is formatted. This can be done with one of the Predefined Conditional Formats, but it is shown here as an easy example of using a custom formula.
The test formula in I5 is,
This formula was copied down I5:I26 to see if it returned the correct TRUE and FALSE for rows to receive conditional formatting. Once the formula returns valid results, the range to be conditionally formatting, C5:H36, is selected and the formula can be entered into the New Formatting Rule dialog box.
19. Conditional Formatting using a dynamic value chosen from a list
A very useful adaptation is to allow the user to select a comparison value from a list. In this example, Excel’s Data Validation is used to create a drop-down list in cell J4. The values for the Data Validation list are taken from L5:L8.
A Data Validation list can also provide values to a cell referenced by predefined conditional formatting. While this example is simple, it is shown as the base for further tips that go beyond predefined conditional formatting.
The test formula in I5 is,
$J$4 is the cell that receives a number from the Data Validation drop-down list.
This formula was copied into I5:I26 to see if it returned the correct TRUE and FALSE for rows to receive conditional formatting. Once the formula returns valid results, the range to be conditionally formatting, C5:H36, is selected. Then the formula is copied into the New Formatting Rule dialog box.
20. Conditional formatting to highlighting alternating rows
Allen Wyatt has created a nice formula in his blog that formats alternating rows. The formula is,
In this formula the ROW function returns the row number of the formula’s cell. MOD then finds the modulo, or the remainder when the row number is divided by 2. When this is 0, then the formula returns 1. Excel interprets 1 as TRUE so the row is formatted. If the formula returns 0, which Excel interprets as FALSE, the row is not formatted. The result is formatting on alternating rows.
21. Conditional Formatting using static text
AND, OR, and NOT functions can be used to create logical statements to narrowly define what you want formatted. In this example, the static text values of Kludget, Flibbit, and Flogjam are tested against the values in the Item column. This is the type of conditional formatting that cannot be done with a Predefined Conditional Format.
The conditional formula has the text values hard coded into the formula,
In most cases, you would want to make the text condition dynamic.
22. Conditional Formatting using multiple text values chosen from a list
This example shows how to choose one, two, or three Items for conditional formatting. If the formula returns TRUE, then the row is formatted. This is the type of conditional formatting that cannot be done with a Predefined Conditional Format.
The test formula in I5 is,
The values in $F$2, $G$2, and $H$2 are entered using a Data Validation drop-down list stored in K5:K12. The value “- None –“ is in the list so that a non-matching selection can be chosen when needed. Because “- None –“ begins with a hyphen/minus sign, you must precede it with an apostrophe (‘).
23. Conditional Formatting based on wildcard searches
Wildcard searches are a powerful feature to have when working with very large lists with many similar words or with lists where you need to find related items, such as similar product IDs. For these types of conditional formatting you want to use a SEARCH formula to find text or partial text matches.
The test formula used in I5 is,
SEARCH looks in E5 for text entered in F2. SEARCH returns the number of the character position if it is found in the word, but SEARCH returns an error if the text is not found in the word.
What makes this especially powerful is that the text in F2 can use the wildcards * to match any group of characters or ? to match against any single character.
If SEARCH does find a match, then it returns a number. ISERROR returns a FALSE because the number is not an error. That FALSE is then changed into a TRUE by NOT. That TRUE indicates the row should have conditional formatting.
If SEARCH does not find a match, then it returns an error. ISERROR then returns TRUE because SEARCH is an error. But, NOT turns the TRUE into a FALSE which is what we want because the text match was not found. <This sounds a little like the logic used to deduce the poisoned glass of wine in the movie Princess Bride.>
Download and open the example file and try the wildcards,
g*, ??p, *jam, *ph
While the logic of the formula is convoluted, the resulting ability to format with wildcards searches is powerful.
24. Conditional Formatting using a dynamic date range
Dynamic date ranges are excellent for highlighting just the time frames you want. Unlike using a data filter, you can still see the rest of the data while the range you are interested in is highlighted.
The formula tested in I5 and used in the conditional format is,
The Sales Date for each row is tested to be in the range between Date Start in $D$1 and Date End in $D$2. When the Sales Date is within the range the formula returns TRUE so the row is formatted.
TIP: When you allow users to enter a date range you can reduce errors by using Data Validation to limit the range of dates they can enter, as shown in this example. Another method of reducing errors is to use a slider or drop-down list for date selection.
25. Conditional Formatting to find errors and blanks
Any time you have manually entered data or data brought in from large, legacy data systems you face issues of missing data, errors, or incorrect data types. You can use conditional formatting to quickly find those errors and then fix them.
In this example any row that tests TRUE for a blank or error in specific cells is highlighted. The formula is,
Some of the functions you can use for testing are,
ISNA, ISBLANK, ISTEXT, ISNUMBER, ISERROR, and ISERR
Be aware that blanks are different than zeroes. Test for blanks with ISBLANK. Test for zero values with =0.
26. Conditional Formatting to create a dynamic heat map or choropleth map
Heat maps or choropleth maps use shading or colors in proportion to values. They are often used to show temperature patterns as well as census data such as population density or per-capita income.
This heat map is in two tabs in the example file. One tab has the “reveal” that shows the mathematics and scroll bars that make it dynamic. The other tab shows the finished heat map and scroll bars that allow you to move the hotspot. (It uses a binomial distribution (bell-shaped curve) of values at 20 points across the X- and Y-axis. Sliding the X or Y sliders moves the hotspot on the map.)