Advertisements

Use conditional formatting to highlight information

Applies to: Excel for Office 365, Excel for Office 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2016 for Mac.

Purpose of Conditional Formatting

Use conditional formatting to help you visually search and examine data, identify critical issues, and uncover patterns and trends.

Conditional formatting simplifies highlighting important cells or ranges of cells, focusing on unusual values, and visualising data by using data bars, colour scales, and icon sets that relate to certain variations in the data.

How does conditional formatting work?

A conditional format manipulates the form of cells according to your defined conditions. If the conditions are true, the cell range is formatted; if the conditions are false, the cell range remains unchanged. There are multiple default conditions, and you also have the freedom to format your own (including by using a formula that evaluates to True or False).

Conditional formatting of monthly record high temperature data for various locations, with colours that correspond to the values in an automated way (hotter values are more orange/red, while cooler values are more yellow/green)Conditional formatting with three color scale
Conditional formatting that uses cell background colours to highlight multiple product categories, a 3-arrow icon set to display cost trends (up, level, down), and data bars to indicate differences between product mark-ups.Cell backgrounds, icon sets, and data bars used as conditional formatting

How can you set conditional formatting?

You can configure conditional formatting to a range of cells (either a selection or a named range), an Excel table, and in Excel for Windows, even a PivotTable report. Note that there are a few extra considerations for conditional formatting in a PivotTable report – see the Apply conditional formatting in a PivotTable report section below on the Windows tab.

Windows

Apply conditional formatting in a PivotTable report

Conditional formatting normally operates the same way in a range of cells, an Excel table, or a PivotTable report. However, conditional formatting in a PivotTable report has some extra caveats:

Factor #1

  • There are certain conditional formats that are useless with fields in the Values area of a PivotTable report. For example, you can’t format such fields based on whether they include unique or duplicate values. These constraints are stated in the remaining sections of this article, where necessary.

Factor #2

  • If you edit the PivotTable report layout by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is intact, provided the fields in the underlying data are not removed.

Factor #3

  • The potential of the conditional format for fields in the Values area can be linked with the data hierarchy and is influenced by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows.

Note: In the data hierarchy, children do not inherit conditional formatting from the parent, and the parent does not inherit conditional formatting from the children.

Methods of setting conditional formatting

  • There are three methods for defining the conditional format of fields in the Values area: by selection, by corresponding field, and by value field.

The basic method of setting fields in the Values area is by selection. You can modify the scoping method to the related field or value field by using the Apply formatting rule to option button, the New Formatting Rule dialogue box, or the Edit Formatting Rule dialogue box.

MethodUse this method if you want to select
Scoping by selectionA contiguous set of fields in the Values area, like all of the product totals for one region.

A series of non-adjacent fields in the Values area, such as product totals for several regions across levels in the data hierarchy.
Scoping by value fieldAvoid making many non-contiguous selections.

Conditionally format a set of fields in the Values area for all levels in the hierarchy of data.

Include subtotals and grand totals.
Scoping by corresponding fieldAvoid making many non-contiguous selections.

Conditionally format a set of fields in the Values area for one level in the hierarchy of data.

Exclude subtotals.

When you conditionally format fields in the Values area for top, bottom, above average, or below average values, the rule is based on all visible values by default. However, when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

A column and its parent row field.

A row and its parent column field.

Use Quick Analysis to apply conditional formatting

Note: Quick Analysis is unavailable in Excel 2010 and previous versions.

Use the Quick Analysis button Quick Analysis button to relay defined conditional formatting to the chosen data. The Quick Analysis button emerges instantly when you select data.

  1. Choose the data that you want to conditionally format. The Quick Analysis button launches on the lower-right corner of the selection.Selected data with the Quick Analysis button
  1. Pick the Quick Analysis button Quick Analysis button, or press Ctrl+Q.
  1. In the pop-up window that displays, on the Formatting tab, hover your mouse across the various options to see a Live Preview on your data, and then select on the desired formatting option.
Formatting tab in the Quick Analysis gallery

Notes: 

  • The formatting options presented in the Formatting tab are based on the data you have chosen. If your selection contains just text, then the available options are Text, Duplicate, Unique, Equal To, and Clear. When the selection simply contains numbers, or both text and numbers, then the options are Data Bars, Colours, Icon Sets, Greater, Top 10%, and Clear.
  • Live preview will only be tailored for those formatting options that are compatible with your data. For example, if your chosen cells have no linked data and you press Duplicate, the live preview will not run.
  1. If the Text that Contains dialogue box displays, write your preferred formatting option and press OK.

If you’d like to watch a video that shows how to use Quick Analysis to apply conditional formatting, see Video: Use conditional formatting.

Download a sample workbook

You can download a sample workbook that is packed full of several examples of applying conditional formatting, both with standard rules like top and bottom, duplicates, Data Bars, Icon Sets and Colour Scales, as well as manually customising your personal rules.

Download: Conditional formatting examples in Excel

Format cells by using a two-colour scale

Colour scales are visual aids that enlighten you about data distribution and variation. A two-colour scale makes comparing a range of cells easier by using a gradation of two colours. The colour shading symbolises higher or lower values. For example, in a green and yellow colour scale, as shown below, you can define that higher value cells have a more green colour and lower value cells have a more yellow colour.

Tip: You can arrange cells that have this format by their colour – just use the context menu.

Formatting all cells with a two-color scale

Tip: If any cells in the selection include a formula that generates an error, the conditional formatting is missed out from those cells. To ensure that the conditional formatting is included in those cells, use an IS or IFERROR function to obtain a value other than an error value.

Quick formatting

  1. Pick one or more cells in a range, table, or PivotTable report.
  1. On the Home tab, in the Styles group, press the arrow beside Conditional Formatting, and then select Colour Scales.

Conditional Formatting

  1. Choose a two-colour scale.

Hover over the colour scale icons to check which icon is a two-colour scale. The top colour represents higher values, and the bottom colour symbolises lower values.

You can change the method of searching for fields in the Values area of a PivotTable report by using the Formatting Options button that emerges beside to a PivotTable field that has conditional formatting applied.

Advanced formatting

Step #1

  1. Pick one or more cells in a range, table, or PivotTable report.

Step #2

  1. On the Home tab, in the Styles group, choose the arrow by Conditional Formatting, and then press Manage Rules. The Conditional Formatting Rules Manager dialogue box pops up.Conditional Formatting Rules Manager dialog box

Step #3

  1. Do one of the following:

Create a conditional format

  • To create a conditional format, pick New Rule. The New Formatting Rule dialogue box appears.

Editing a conditional format

  • To edit a conditional format, do the following:

i. Ensure that the suitable worksheet, table, or PivotTable report is chosen in the Show formatting rules for list box.

ii. Optionally, adapt the range of cells by pressing Collapse Dialogue in the Applies to box to momentarily hide the dialogue box, by choosing the new cell range on the worksheet, and then by picking Expand Dialogue.

iii. Pick the rule, and then select Edit rule. The Edit Formatting Rule dialogue box displays.

Step #4

  1. Under Apply Rule To, to optionally alter the range for fields in the Values area of a PivotTable report by:
  • Selection: Press Selected cells.
  • All cells for a Value label: Pick All cells showing <Value label> values.
  • All cells for a Value label, excluding subtotals and the grand total: Select All cells showing <Value label> values for <Row Label>.

Step #5

  1. Under Select a Rule Type, choose Format all cells based on their values (default).

Step #6

  1. Under Edit the Rule Description, in the Format Style list box, choose 2-Colour Scale.

Step #7

  1. To confirm a type in the Type box for Minimum and Maximum, do one of the following:
  • Format lowest and highest values: Select Lowest Value and Highest Value. In this case, you do not type a Minimum and MaximumValue.
  • Format a number, date, or time value: Choose Number and then write a Minimum and MaximumValue.
  • Format a percentage Percent: Type a Minimum and MaximumValue.

Valid values run from 0 (zero) to 100. Avoid typing a percent sign.

Use a percentage when you want to visualise all values proportionally as the distribution of values is proportional.

Format a percentile

  • Format a percentile: Pick Percentile and then type a Minimum and MaximumValue. Valid percentiles range from 0 (zero) to 100.

Use a percentile when you want to visualise a group of high values (such as the top 20thpercentile) in one colour grade proportion and low values (such as the bottom 20th percentile) in another colour grade proportion, because they reflect extreme values that might distort the visualisation of your data.

Format a formula result

  • Format a formula result: Select Formula and then enter values for Minimum and Maximum.
  • The formula must return a number, date, or time value.
  • Start the formula with an equal sign (=).
  • Invalid formulas result in no formatting being applied.
  • It’s a good idea to test the formula to make sure that it doesn’t return an error value.

Notes: 

  • Make sure that the Minimum value is less than the Maximum value.
  • You can choose a different type for the Minimum and Maximum. For example, you can choose a number for Minimum a percentage for Maximum.

Step #8

  1. To select a Minimum and Maximum colour scale, pick Colour for each, and then decide a colour.

If you want to add new colours or produce your customised colour, click More Colours. The colour scale you pick is seen in the Preview box.

Format cells by using a three-colour scale

Colour scales are visual aids that enlighten you about data distribution and variation. A three-colour scale makes comparing a range of cells easier by using a gradation of three colours. The colour shading symbolises higher, middle, or lower values. For example, in a green, yellow, and red colour scale, you can define that higher value cells have a green colour, middle value cells have a yellow colour, and lower value cells have a red colour.

Quick formatting

Advanced formatting

  1. Pick one or more cells in a range, table, or PivotTable report.
  1. On the Home tab, in the Styles group, choose the arrow by Conditional Formatting, and then press Manage Rules. The Conditional Formatting Rules Manager dialogue box pops up.Conditional Formatting Rules Manager dialog box
  1. Do one of the following:
  • To create a conditional format, pick New Rule. The New Formatting Rule dialogue box appears.
  • To edit a conditional format, do the following:

i. Ensure that the suitable worksheet, table, or PivotTable report is chosen in the Show formatting rules for list box.

ii. Optionally, adapt the range of cells by pressing Collapse Dialogue in the Applies to box to momentarily hide the dialogue box, by choosing the new cell range on the worksheet, and then by picking Expand Dialogue.

iii. Pick the rule, and then select Edit rule. The Edit Formatting Rule dialogue box displays.

  1. Under Apply Rule To, to optionally alter the range for fields in the Values area of a PivotTable report by:
  • Selection: Press Selected cells.
  • All cells for a Value label: Pick All cells showing <Value label> values.
  • All cells for a Value label, excluding subtotals and the grand total: Select All cells showing <Value label> values for <Row Label>.
  1. Under Select a Rule Type, choose Format all cells based on their values (default).
  1. Under Edit the Rule Description, in the Format Style list box, choose 2-Colour Scale.
  1. To confirm a type in the Type box for Minimum and Maximum, do one of the following:
  • Format lowest and highest values: Select Lowest Value and Highest Value. In this case, you do not type a Minimum and MaximumValue.
  • Format a number, date, or time value: Choose Number and then write a Minimum and MaximumValue.
  • Format a percentage Percent: Type a Minimum and MaximumValue.

Valid values run from 0 (zero) to 100. Avoid typing a percent sign.

Use a percentage when you want to visualise all values proportionally as the distribution of values is proportional.

  • Format a percentile: Pick Percentile and then type a Minimum and MaximumValue. Valid percentiles range from 0 (zero) to 100.

Use a percentile when you want to visualise a group of high values (such as the top 20thpercentile) in one colour grade proportion and low values (such as the bottom 20th percentile) in another colour grade proportion, because they reflect extreme values that might distort the visualisation of your data.

  • Format a formula result: Select Formula and then enter values for Minimum and Maximum.
  • The formula must return a number, date, or time value.
  • Start the formula with an equal sign (=).
  • Invalid formulas result in no formatting being applied.
  • It’s a good idea to test the formula to make sure that it doesn’t return an error value.

Notes: 

  • Make sure that the Minimum value is less than the Maximum value.
  • You can choose a different type for the Minimum and Maximum. For example, you can choose a number for Minimum a percentage for Maximum.
  1. To select a Minimum and Maximum colour scale, pick Colour for each, and then decide a colour.
  1. Choose a three-colour scale.

Hover over the colour scale icons to check which icon is a two-colour scale. The top colour represents higher values, the centre colour represents middle values and the bottom colour symbolises lower values.

You can change the method of searching for fields in the Values area of a PivotTable report by using the Formatting Options button that emerges beside to a PivotTable field that has conditional formatting applied.

Format cells by using data bars

A data bar helps you see the value of a cell relative to other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.

The example shown here uses data bars to highlight dramatic positive and negative values. You can format data bars so that the data bar starts in the middle of the cell, and stretches to the left for negative values.

Data bars that highlight positive and negative values

Tip: If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value (such as 0 or “N/A”) instead of an error value.

Quick formatting

  1. Select one or more cells in a range, table, or PivotTable report.
  1. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Data Bars, and then select a data bar icon.
Conditional Formatting
  1. Select the command you want, such as Above Average or Below Average.
  1. Enter the values you want to use, and then select a format.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

Advanced formatting

  1. Select one or more cells in a range, table, or PivotTable report.
  1. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialogue box appears.
Conditional Formatting Rules Manager dialog box
  1. Do one of the following:
  • To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
  • To change a conditional format, do the following:

I. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

ii. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialogue.

iii. Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.

  1. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
  • Selection: Click Just these cells.
  • Corresponding field: Click All <value field> cells with the same fields.
  • Value field:    Click All <value field> cells.
  1. Under Select a Rule Type, click Format all cells based on their values.
  1. Under Edit the Rule Description, in the Format Style list box, select Data Bar.
  1. Select a Minimum and MaximumType. Do one of the following:
  • Set lowest and highest values:    Select Lowest Value and Highest Value.In this case, you do not enter a value for Minimum and Maximum.
  • Define a number, date, or time value:    Select Number and then enter a Minimum and MaximumValue.
  • Arrange a percentage:    Select Percent and then enter a value for Minimum and Maximum.Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.Use a percentage when you want to visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional.
  • Add a percentile:    Select Percentile and then enter a value for Minimum and Maximum.Valid percentiles are from 0 (zero) to 100. Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) in one data bar proportion and low values (such as the bottom 20th percentile) in another data bar proportion, because they represent extreme values that might skew the visualization of your data.
  • Format a formula result:     Select Formula, and then enter a value for Minimum and Maximum.
  • The formula has to return a number, date, or time value.
  • Start the formula with an equal sign (=).
  • Invalid formulas result in no formatting being applied.
  • It’s a good idea to test the formula to make sure that it doesn’t return an error value.

Notes: 

  • Make sure that the Minimum value is less than the Maximum value.
  • You can choose a different type for Minimum and Maximum. For example, you can choose a Minimum number and a Maximum percent.
  1. To choose a Minimum and Maximum colour scale, click Bar Colour.If you want to choose additional colours or create a custom colour, click More Colours. The bar colour you select is shown in the Preview box.
  1. For showing only the data bar and not the value in the cell, select Show Bar Only.
  1. To apply a solid border to data bars, select Solid Border in the Border list box and choose a colour for the border.
  1. In order to choose between a solid bar and a gradiated bar, choose Solid Fill or Gradient Fill in the Fill list box.
  1. Format negative bars by clicking Negative Value and Axis. Then, in the Negative Value and Axis Settings dialogue box, choose options for the negative bar fill and border colours. You can choose position settings and a colour for the axis. When you are finished selecting options, click OK.
  1. You can change the direction of bars by choosing a setting in the Bar Direction list box. This is set to Context by default, but you can choose between a left-to-right and a right-to-left direction, depending on how you want to present your data.

Format cells by using an icon set

Use an icon set to annotate and assign data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.

The example shown here works with several examples of conditional formatting icon sets.

Different icon sets for the same data

You can decide to reveal icons only for cells that meet a condition; for example, displaying a warning icon for those cells that fall short of a critical value and no icons for those that surpass it. To do this, you hide icons by clicking No Cell Icon from the icon drop-down list beside the icon when you are defining conditions. You can also customise your personal combination of icon sets; for example, a green “symbol” check mark, a yellow “traffic light”, and a red “flag.”

Tip: If any cells in the selection have a formula that yields an error, the conditional formatting is not set to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to capture a value (such as 0 or “N/A”) rather than an error value.

Quick formatting

  1. Pick the cells that you want to conditionally format.
  1. On the Home tab, in the Style group, press the arrow adjacent to Conditional Formatting, select Icon Set, and then click an icon set.
Conditional Formatting

You can edit the method of refining for fields in the Values area of a PivotTable report by applying the Apply formatting rule to option button.

Advanced formatting

  1. Choose the cells that you want to conditionally format.
  1. On the Home tab, in the Styles group, press the arrow next to Conditional Formatting, and then pick Manage Rules. The Conditional Formatting Rules Manager dialogue box appears.

Conditional Formatting Rules Manager dialog box

  1. Do one of the following:
  • To insert a conditional format, click New Rule. The New Formatting Rule dialogue box appears.
  • To alter a conditional format, do the following:

i. Ensure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

ii. Optionally, edit the range of cells by pressing Collapse Dialogue in the Applies to box to temporarily conceal the dialogue box, by picking the new range of cells on the worksheet, and then by selecting Expand Dialogue.

iii. Pick the rule, and then press Edit rule. The Edit Formatting Rule dialogue box emerges.

  1. Under Apply Rule To, to optionally edit the range for fields in the Values area of a PivotTable report by:
  • Selection: Click Just these cells.
  • Corresponding field: Click All <value field> cells with the same fields.
  • Value field: Click All <value field> cells.
  1. Under Select a Rule Type, select Format all cells based on their values.
  1. Under Edit the Rule Description, in the Format Style list box, press Icon Set.

a. Choose an icon set. The default is 3 Traffic Lights (Unrimmed). The number of icons and the default comparison operators and threshold values for each icon can vary for each icon set.

b. You can change the comparison operators and threshold values. The standard range of values for each icon are equal in size, but you can modify these to meet your unique requirements. Make sure that the thresholds are in a logical sequence of highest to lowest from top to bottom.

c. Do one of the following:

  • Format a number, date, or time value: Select Number.
  • Format a percentage: Select Percent.
  • Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.Use a percentage when you want to visualise all values proportionally, because using a percentage ensures that the distribution of values is proportional.
  • Format a percentile: Select Percentile.
  • Valid percentiles are from 0 (zero) to 100.Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) using a particular icon and low values (such as the bottom 20th percentile) using another icon, because they represent extreme values that might skew the visualization of your data.
  • Format a formula result: Select Formula, and then enter a formula in each Value box.
  • The formula must return a number, date, or time value.
  • Start the formula with an equal sign (=).
  • Invalid formulas result in no formatting being applied.
  • It’s a good idea to test the formula to make sure that it doesn’t return an error value.

d. To make the first icon represent lower values and the last icon represent higher values, select Reverse Icon Order.

e. To show only the icon and not the value in the cell, select Show Icon Only.

Notes: 

  • You may need to adjust the column width to accommodate the icon.
  • The size of the icon shown depends on the font size that is used in that cell. As the size of the font is increased, the size of the icon increases proportionally.

Format cells that contain text, number, or date or time values

To more easily find specific cells, you can format them by using a comparison operator. For example, in an inventory worksheet sorted by categories, you could highlight products with fewer than 10 items on hand in yellow. Or, in a retail store summary worksheet, you might identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal to “SouthEast.”

The examples shown here work with examples of built-in conditional formatting criteria, such as Greater Than, and Top %. This formats cities with a population greater than 2,000,000 with a green background and average high temperatures in the top 30% with orange.

Formatting shows cities with more than 2 million, and top 30% of high temperatures

Note: You cannot conditionally format fields in the Values area of a PivotTable report by text or by date, only by number.

Quick formatting

  1. Select one or more cells in a range, table, or PivotTable report.
  1. On the Home tab, in the Style group, pick the arrow next to Conditional Formatting, and then choose Highlight Cells Rules.

Conditional Formatting

  1. Confirm your preferred command, such as Between, Equal To Text that Contains, or A Date Occurring.
  1. Type the values you want to use, and then pick a format.

You can alter the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

If you’d like to watch videos of these techniques, see Video: Conditionally format text and Video: Conditionally format dates.

Advanced formatting

  1. Choose one or more cells in a range, table, or PivotTable report.
  1. On the Home tab, in the Styles group, select the arrow beside Conditional Formatting, and then choose Manage Rules. The Conditional Formatting Rules Manager dialogue box appears.Conditional Formatting Rules Manager dialog box
  1. Do one of the following:
  • To create a conditional format, pick New Rule. The New Formatting Rule dialogue box appears.
  • To edit a conditional format, do the following:

i. Check that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

ii. Optionally, change the cell range by picking Collapse Dialogue in the Applies to box to only hide the dialogue box for a moment, by clicking the new range of cells on the worksheet or on other worksheets, and then by choosing Expand Dialogue.

iii. Choose the rule, and then press Edit rule. The Edit Formatting Rule dialogue box launches.

  1. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
  • Selection: Click Just these cells.
  • Corresponding field: Click All <value field> cells with the same fields.
  • Value field: Click All <value field> cells.
  1. Under Select a Rule Type, click Format only cells that contain.
  1. Under Edit the Rule Description, in the Format only cells with list box, do one of the following:
  • Format by number, date, or time: Select Cell Value, select a comparison operator, and then enter a number, date, or time.

For example, select Between and then enter 100 and 200, or select Equal to and then enter 1/1/2009.

You can also enter a formula that returns a number, date, or time value.

  • If you enter a formula, start it with an equal sign (=).
  • Invalid formulas result in no formatting being applied.
  • It’s a good idea to test the formula to make sure that it doesn’t return an error value.
  • Format by text: Select Specific Text, choosing a comparison operator, and then enter text. For example, select Contains and then enter Silver, or select Starting with and then enter Tri.

Quotes are included in the search string, and you may use wildcard characters. The maximum length of a string is 255 characters.

You can also enter a formula that returns text.

  • If you enter a formula, start it with an equal sign (=).
  • Invalid formulas result in no formatting being applied.
  • It’s a good idea to test the formula to make sure that it doesn’t return an error value.

To see a video of this technique, see Video: Conditionally format text.

  • Format by date: Pick Dates Occurring and then select a date comparison.

For example, select Yesterday or Next week.

To see a video of this technique, see Video: Conditionally format dates.

Format cells with blanks or no blanks: Press Blanks or No Blanks.

  • A blank value is a cell that contains no data and is different from a cell that contains one or more spaces (spaces are considered as text).
  • Format cells with error or no error values: Choose Errors or No Errors.

Error values include: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, and #NULL!.

  1. To specify a format, click Format. The Format Cells dialogue box appears.
  1. Choose the number, font, border, or fill format you want to apply when the cell value reflects the condition, and then press OK. You can choose more than one format. The formats you select are shown in the Preview box.

Format only top or bottom ranked values

You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify. For example, you can find the top 5 selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department .

Quick formatting

  1. Select one or more cells in a range, table, or PivotTable report.
  1. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules.

Conditional Formatting

  1. Select the command you want, such as Top 10 items or Bottom 10 %.
  1. Enter the values you want to use, and then select a format.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

Advanced formatting

  1. Select one or more cells in a range, table, or PivotTable report.
  1. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialogue box appears.
Conditional Formatting Rules Manager dialog box
  1. Do one of the following:
  • To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
  • To change a conditional format, do the following:

i. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

ii. Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog.

iii. Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.

  1. Under Apply Rule To, to optionally change the scope fields in the Values area of a PivotTable report by:
  • Selection: Click Just these cells.
  • Corresponding field: Click All <value field> cells with the same fields.
  • Value field: Click All <value field> cells.
  1. Under Select a Rule Type, click Format only top or bottom ranked values.
  1. Under Edit the Rule Description, in the Format values that rank in the list box, select Top or Bottom.
  1. Do one of the following:
  • To specify a top or bottom number, enter a number and then clear the % of the selected range box. Valid values are 1 to 1000.
  • To specify a top or bottom percentage, enter a number and then select the % of the selected range box. Valid values are 1 to 100.
  1. Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.

By default, the conditional format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

  • A column and its parent row field, by selecting each Column group.
  • A row and its parent column field, by selecting each Row group.
  1. To specify a format, click Format. The Format Cells dialog box appears.
  1. Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK. You can choose more than one format. The formats you select are shown in the Preview box.

Format only values that are above or below average

You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating.

Quick formatting

  1. Select one or more cells in a range, table, or PivotTable report.
  1. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules.
Conditional Formatting
  1. Select the command you want, such as Above Average or Below Average.
  1. Enter the values you want to use, and then select a format.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

Advanced formatting

  1. Select one or more cells in a range, table, or PivotTable report.
  1. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

Conditional Formatting Rules Manager dialog box

  1. Do one of the following:
  • To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
  • To change a conditional format, do the following:

i. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

ii. Optionally, change the range of cells by clicking Collapse Dialogue in the Applies to box to temporarily hide the dialogue box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialogue.

iii. Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.

  1. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report by:
  • Selection: Click Just these cells.
  • Corresponding field: Click All <value field> cells with the same fields.
  • Value field: Click All <value field> cells.
  1. Under Select a Rule Type, click Format only values that are above or below average.
  1. Under Edit the Rule Description, in the Format values that are list box, do one of the following:
  • To format cells that are above or below the average for all of the cells in the range, select Above or Below.
  • To format cells that are above or below one, two, or three standard deviations for all of the cells in the range, select a standard deviation.
  1. Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.

By default, the conditionally format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

  • A column and its parent row field, by selecting each Column group.
  • A row and its parent column field, by selecting each Row group.
  1. Click Format to display the Format Cells dialog box.
  1. Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK. You can choose more than one format. The formats you select are displayed in the Preview box.

Format only unique or duplicate values

Note: You can’t conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.

In the example shown here, conditional formatting is used on the Instructor column to find instructors that are teaching more than one class (duplicate instructor names are highlighted in a pale red colour). Grade values that are found just once in the Grade column (unique values) are highlighted in a green color.

Values in column C that aren't unique are colored rose, unique values in column D are green

Quick formatting

  1. Select the cells that you want to conditionally format.
  1. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules.
Conditional Formatting
  1. Select Duplicate Values.
  1. Enter the values you want to use, and then select a format.

Advanced formatting

  1. Select the cells that you want to conditionally format.
  1. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialogue box appears.
Conditional Formatting Rules Manager dialog box
  1. Do one of the following:
  • To add a conditional format, click New Rule. The New Formatting Rule dialog box appears.
  • To change a conditional format, do the following:

i. Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box.

ii. Optionally, change the range of cells by clicking Collapse Dialogue in the Applies to box to temporarily hide the dialogue box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialogue.

iii. Select the rule, and then click Edit rule. The Edit Formatting Rule dialog box appears.

  1. Under Select a Rule Type, click Format only unique or duplicate values.
  1. Under Edit the Rule Description, in the Format all list box, select unique or duplicate.
  1. Click Format to display the Format Cells dialogue box.
  1. Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK.

You can choose more than one format. The formats you select are shown in the Preview box.

Set up your own conditional formatting rule

If none of the above options is what you’re looking for, you can create your own conditional formatting rule in a few simple steps:

  1. Select the cells that you want to format.
  1. On the Home tab, click Conditional Formatting > New Rule.
New formatting rule
  1. Create your rule and specify its format options, then click OK. If you don’t see the options that you want, you can use a formula to determine which cells to format – see the next section for steps).

Use a formula to determine which cells to format

If you don’t see the exact options you need when you create your own conditional formatting rule, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values in a selection to a result returned by a function or evaluate data in cells outside the selected range, which can be in another worksheet in the same workbook. Your formula must return True or False (1 or 0), but you can use conditional logic to string together a set of corresponding conditional formats, such as different colours for each of a small set of text values (for example, product category names).

Note: You can enter cell references in a formula by selecting cells directly on a worksheet or other worksheets. Selecting cells on the worksheet inserts absolute cell references. If you want Excel to adjust the references for each cell in the selected range, use relative cell references. For more information, see Create or change a cell reference and Switch between relative, absolute, and mixed references.

Tip: If any cells contain a formula that returns an error, conditional formatting is not applied to those cells. To address this, use IS functions or an IFERROR function in your formula to return a value that you specify (such as 0, or “N/A”) instead of an error value.

  1. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.
Conditional Formatting

The Conditional Formatting Rules Manager dialogue box appears.

Conditional Formatting Rules Manager dialog box

  1. Do one of the following:
  • To add a conditional format, click New Rule. The New Formatting Rule dialogue box appears.
  • To change a conditional format, do the following:

i. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

ii. Optionally, change the range of cells by clicking Collapse Dialogue in the Applies to box to temporarily hide the dialogue box, by selecting the new range of cells on the worksheet or other worksheets, and then by clicking Expand Dialogue.

iii. Select the rule, and then click Edit rule. The Edit Formatting Rule dialogue box appears.

  1. Under Apply Rule To, to optionally change the scope for fields in the Values area of a PivotTable report, do the following:
  • For scope by selection: Click Selected cells.
  • To scope by corresponding field: Click All cells showing <Values field> values.
  • Choose scope by Value field: Click All cells showing <Values field> for <Row>.

  1. Under Select a Rule Type, click Use a formula to determine which cells to format.

a. Under Edit the Rule Description, in the Format values where this formula is true list box, enter a formula.

You have to start the formula with an equal sign (=), and the formula must return a logical value of TRUE (1) or FALSE (0).

b. Click Format to display the Format Cells dialogue box.

c. Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK.

You can choose more than one format. The formats you select are shown in the Preview box.

Example 1: Use two conditional formats with criteria that uses AND and OR tests    The following example shows the use of two conditional formatting rules. If the first rule doesn’t apply, the second rule applies.

First rule: a home buyer has budgeted up to $75,000 as a down payment and $1,500 per month as a mortgage payment. If both the down payment and the monthly payments fit these requirements, cells B4 and B5 are formatted green.

Second rule: if either the down payment or the monthly payment doesn’t meet the buyer’s budget, B4 and B5 are formatted red. Change some values, such as the APR, the loan term, the down payment, and the purchase amount to see what happens with the conditionally formatted cells.

Formula for first rule (applies green colour)=AND(IF($B$4<=75000,1),IF(ABS($B$5)<=1500,1))
Formula for second rule (applies red colour)=OR(IF($B$4>=75000,1),IF(ABS($B$5)>=1500,1))

Cells B4 and B5 meet their conditions, so they're formatted green

Example 2: Shade every other row by using the MOD and ROW functions    A conditional format applied to every cell in this worksheet shades every other row in the range of cells with a blue cell color. You can select all cells in a worksheet by clicking the square above row 1 and to the left of column A. The MOD function returns a remainder after a number (the first argument) is divided by divisor (the second argument). The ROW function returns the current row number. When you divide the current row number by 2, you always get either a 0 remainder for an even number or a 1 remainder for an odd number. Because 0 is FALSE and 1 is TRUE, every odd numbered row is formatted. The rule uses this formula: =MOD(ROW(),2)=1.

Every other row is shaded blue

Note: You can enter cell references in a formula by selecting cells directly on a worksheet or other worksheets. Selecting cells on the worksheet inserts absolute cell references. If you want Excel to adjust the references for each cell in the selected range, use relative cell references. For more information, see Create or change a cell reference and Switch between relative, absolute, and mixed references.

The following video shows you the basics of using formulas with conditional formatting.

Copy and paste conditional formatting

If you want to apply an existing formatting style to new or other data on your worksheet, you can use Format Painter to copy the conditional formatting to that data.

  1. Click the cell that has the conditional formatting that you want to copy.
Copy and Paste buttons on the Home tab
  1. Click Home > Format Painter.

The pointer changes to a paintbrush.

Tip: You can double-click Format Painter if you want to keep using the paintbrush to paste the conditional formatting in other cells.

  1. To paste the conditional formatting, drag the paintbrush across the cells or ranges of cells you want to format.
  1. To stop using the paintbrush, press Esc.

Note: If you’ve used a formula in the rule that applies the conditional formatting, you might have to adjust any cell references in the formula after pasting the conditional format. For more information, see Switch between relative, absolute, and mixed references.

Find cells that have conditional formatting

If your worksheet contains conditional formatting, you can quickly locate the cells so that you can copy, change, or delete the conditional formats. Use the Go To Special command to find only cells with a specific conditional format, or to find all cells that have conditional formats.

Find all cells that have a conditional format

  1. Click any cell that does not have a conditional format.
  1. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Conditional Formatting.

Editing group on the Home tab

Find only cells that have the same conditional format

  1. Click any cell that has the conditional format that you want to find.
  1. On the Home tab, in the Editing group, click the arrow next to Find & Select, and then click Go To Special.
  1. Click Conditional formats.

Click Same under Data validation.

  1. Editing group on the Home tab

Manage conditional formatting rules

When you use conditional formatting, you set up rules that Excel uses to determine when to apply the conditional formatting. To manage these rules, you should understand the order in which these rules are evaluated, what happens when two or more rules conflict, how copying and pasting can affect rule evaluation, how to change the order in which rules are evaluated, and when to stop rule evaluation.

Learn about conditional formatting rule precedenceYou create, edit, delete, and view all conditional formatting rules in the workbook by using the Conditional Formatting Rules Manager dialogue box. (On the Home tab, click Conditional Formatting, and then click Manage Rules.)

Conditional Formatting menu with Manage Rules highlighted

The Conditional Formatting Rules Manager dialogue box appears.Conditional Formatting Rules Manager dialog boxWhen two or more conditional formatting rules apply, these rules are evaluated in order of precedence (top to bottom) by how they are listed in this dialogue box.

Here’s an example that has expiration dates for ID badges. We want to mark badges that expire within 60 days but are not yet expired with a yellow background colour, and expired badges with a red background colour.

Conditionally formatted data

In this example, cells with employee ID numbers who have certification dates due to expire within 60 days are formatted in yellow, and ID numbers of employees with an expired certification are formatted in red. The rules are shown in the following image.

Conditional formatting rules

The first rule (which, if True, sets cell background colour to red) tests a date value in column B against the current date (obtained by using the TODAY function in a formula). Assign the formula to the first data value in column B, which is B2. The formula for this rule is =B2<TODAY(). This formula tests the cells in column B (cells B2:B15).

If the formula for any cell in column B evaluates to True, its corresponding cell in column A (for example, A5 corresponds to B5, A11 corresponds to B11), is then formatted with a red background color. After all the cells specified under Applies to are evaluated with this first rule, the second rule is tested. This formula checks if values in the B column are less than 60 days from the current date (for example, suppose today’s date is 8/11/2010).

The cell in B4, 10/4/2010, is less than 60 days from today, so it evaluates as True, and is formatted with a yellow background colour. The formula for this rule is =B2<TODAY()+60. Any cell that was first formatted red by the highest rule in the list is left alone.

A rule higher in the list has greater precedence than a rule lower in the list. By default, new rules are always added to the top of the list and therefore have a higher precedence, so you’ll want to keep an eye on their order. You can change the order of precedence by using the Move Up and Move Down arrows in the dialogue box.Move Up and Move Down arrows

What happens when more than one conditional formatting rule evaluates to True

Sometimes you have more than one conditional formatting rule that evaluates to True. Here’s how rules are applied, first when rules don’t conflict, and then when they do conflict:

When rules don’t conflict    

For example, if one rule formats a cell with a bold font and another rule formats the same cell with a red colour, the cell is formatted with both a bold font and a red colour. Because there is no conflict between the two formats, both rules are applied.

When rules conflict    

For example, one rule sets a cell font colour to red and another rule sets a cell font colour to green. Because the two rules are in conflict, only one can apply. The rule that is applied is the one that is higher in precedence (higher in the list in the dialogue box).

  • How pasting, filling, and the Format Painter affect conditional formatting rules

While editing your worksheet, you may copy and paste cell values that have conditional formats, fill a range of cells with conditional formats, or use the Format Painter. These operations can affect conditional formatting rule precedence in the following way: a new conditional formatting rule based on the source cells is created for the destination cells.

If you copy and paste cell values that have conditional formats to a worksheet opened in another instance of Excel (another Excel.exe process running at the same time on the computer), no conditional formatting rule is created in the other instance and the format is not copied to that instance.

What happens when a conditional format and a manual format conflict

If a conditional formatting rule evaluates as True, it takes precedence over any existing manual format for the same selection. This means that if they conflict, the conditional formatting applies and the manual format does not. If you delete the conditional formatting rule, the manual formatting for the range of cells remains.

Manual formatting is not listed in the Conditional Formatting Rules Manager dialogue box nor is it used to determine precedence.

Controlling when rule evaluation stops by using the Stop If True check box

For backwards compatibility with versions of Excel earlier than Excel 2007, you can select the Stop If True check box in the Manage Rules dialog box to simulate how conditional formatting might appear in those earlier versions of Excel that do not support more than three conditional formatting rules or multiple rules applied to the same range.

For example, if you have more than three conditional formatting rules for a range of cells, and are working with a version of Excel earlier than Excel 2007, that version of Excel:

  • Evaluates only the first three rules.
  • Applies the first rule in precedence that is True.
  • Ignores rules lower in precedence if they are True.

The following table summarizes each possible condition for the first three rules:

If ruleIsAnd if ruleIsAnd if ruleIsThen
OneTrueTwoTrue or FalseThreeTrue or FalseRule one is applied and rules two and three are ignored.
OneFalseTwoTrueThreeTrue or FalseRule two is applied and rule three is ignored.
OneFalseTwoFalseThreeTrueRule three is applied.
OneFalseTwoFalseThreeFalseNo rules are applied.

You can select or clear the Stop If True check box to change the default behavior:

  • To evaluate only the first rule, select the Stop If True check box for the first rule.
  • To evaluate only the first and second rules, select the Stop If True check box for the second rule.
  • You can’t select or clear the Stop If True check box if the rule formats by using a data bar, colour scale, or icon set.

If you’d like to watch a video showing how to manage conditional formatting rules, see Video: Manage conditional formatting.

Edit the order in which conditional formatting rules are evaluated

The order in which conditional formatting rules are evaluated – their precedence – also reflects their relative importance: the higher a rule is on the list of conditional formatting rules, the more important it is. This means that in cases where two conditional formatting rules conflict with each other, the rule that is higher on the list is applied and the rule that is lower on the list is not applied.

  1. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.

Conditional Formatting menu with Manage Rules highlighted

The Conditional Formatting Rules Manager dialog box appears.

Conditional Formatting Rules Manager dialog boxThe conditional formatting rules for the current selection are displayed, including the rule type, the format, the range of cells the rule applies to, and the Stop If True setting.

If you don’t see the rule that you want, in the Show formatting rules for list box, make sure that the right range of cells, worksheet, table, or PivotTable report is selected.

  1. Select a rule. Only one rule can be selected at a time.
  1. To move the selected rule up in precedence, click Move Up. To move the selected rule down in precedence, click Move Down.

Move Up and Move Down arrows

  1. Optionally, to stop rule evaluation at a specific rule, select the Stop If True check box.

Clear conditional formatting

Clear conditional formatting on a worksheet    

  • On the Home tab, click Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

Follow these steps if you have conditional formatting in a worksheet, and you need to remove it.

For an entire worksheet

  • On the Home tab, click Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

In a range of cells

  1. Select the cells that contain the conditional formatting.
  1. Click the Quick Analysis Lensbutton image button that appears to the bottom right of the selected data.

Notes: Quick Analysis Lens will not be visible if:

All of the cells in the selected range are empty, or

There is an entry only in the upper-left cell of the selected range, with all of the other cells in the range being empty.

  1. Click Clear Format.
Clear option

Find and remove the same conditional formats throughout a worksheet

  1. Click on a cell that has the conditional format that you want to remove throughout the worksheet.
  1. On the Home tab, click the arrow next to Find & Select, and then click Go To Special.
  1. Click Conditional formats.
  1. Click Same under Data validation. to select all of the cells that contain the same conditional formatting rules.
  1. On the Home tab, click Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Web:

Format cells by using a colour scale

Colour scales are visual guides that help you understand data distribution and variation. There are two kinds of color scales—two-colour scales and three-colour scales.

A two-colour scale helps you compare a range of cells by using a gradation of two colours. The shade of the colour represents higher or lower values. For example, in a green and yellow colour scale, you can specify that higher value cells have a more green colour and lower value cells have a more yellow colour.

A three-colour scale helps you compare a range of cells by using a gradation of three colours. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red colour scale, you can specify that higher value cells have a green colour, middle value cells have a yellow colour, and lower value cells have a red colour.

  1. Select the cells that you want to conditionally format.
  1. Pick Home > Tables > Conditional Formatting > Colour Scales and then select a colour scale.

Format cells by using data bars

A data bar helps you see the value of a cell relative to other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.

  1. Select the cells that you want to conditionally format.
  1. Click Home > Tables > Conditional Formatting > Data Bars and then select a data bar.

Format cells by using an icon set

Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.

  1. Select the cells that you want to conditionally format.
  1. Click Home > Tables > Conditional Formatting > Icon Sets and then select an icon set.

Format only cells that contain text, number, or date or time values

To more easily find specific cells within a range of cells, you can format those specific cells based on a comparison operator. For example, in an inventory worksheet sorted by categories, you can highlight the products with fewer than 10 items on hand in yellow. Or, in a retail store summary worksheet, you can identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal to “SouthEast.”

  1. Select the cells that you want to conditionally format.
  1. On the Home tab, in the Tables group, click Highlight Cell Rules.
  1. Select the command you want, such as Between, Equal To Text that Contains, or A Date Occurring.
  1. Enter the values you want to use, and then select a format (fill, text, or border color), and then click OK.

If you’d like to watch videos of these techniques, see Video: Conditionally format text and Video: Conditionally format dates.

Format only top or bottom ranked values

You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify. For example, you can find the top 5 selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department.

  1. Select the cells that you want to conditionally format.
  1. On the Home tab, in the Tables group, click Top/Bottom Rules.
  1. Select the command you want, such as Top 10 items or Bottom 10%.
  1. Enter the values you want to use, and then select a format (fill, text, or border colour), and then click OK.

Format only values that are above or below average

You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating.

  1. Select the cells that you want to conditionally format.
  1. On the Home tab, in the Tables group, click Top/Bottom Rules.
  1. Select the command you want, such as Above Average or Below Average.
  1. Enter the values you want to use, and then select a format (fill, text, or border colour), and then click OK.

Format only unique or duplicate values

You can find unique or duplicate values in a range of cells.

  1. Select the cells that you want to conditionally format.
  1. On the Home tab, in the Tables group, click Highlight Cell Rules, and then select Duplicate Values.
  1. Enter the values you want to use, and then select a format (fill, text, or border colour), and then click OK.

Copy and paste conditional formatting

If you want to apply an existing formatting style to new or other data on your worksheet, you can use Format Painter to copy the conditional formatting to that data.

  1. Click the cell that has the conditional formatting you want to copy.
  1. Click Home > Format Painter.
Copy and Paste buttons on the Home tab

The pointer changes to a paintbrush.

Tip: You can double-click Format Painter if you want to keep using the paintbrush to paste the conditional formatting in other cells.

  1. To paste the conditional formatting, drag the paintbrush across the cells or ranges of cells you want to format.
  1. To stop using the paintbrush, press Esc.

Note: If you’ve used a formula in the rule that applies the conditional formatting, you might have to adjust relative and absolute references in the formula after pasting the conditional format. For more information, see Switch between relative, absolute, and mixed references.

Manage conditional formatting rules

When you use conditional formatting, you set up rules that Excel uses to determine when to apply the conditional formatting. To manage these rules, it is important to understand in what order these rules are evaluated, what happens when two or more rules conflict, how copying and pasting can affect rule evaluation, how to change the order in which rules are evaluated, and when to stop rule evaluation.

Learn about conditional formatting rule precedence

You create, edit, delete, and view all conditional formatting rules in the workbook by using the Conditional Formatting Rules Manager dialogue box. (On the Home tab, click Conditional Formatting, and then click Manage Rules.)

Conditional Formatting menu with Manage Rules highlighted

The Conditional Formatting pane appears to the right of your data.

When two or more conditional formatting rules apply to a range of cells, these rules are evaluated in order of precedence (top to bottom) by how they are listed in this dialogue box.

A rule higher in the list has greater precedence than a rule lower in the list. By default, new rules are always added to the top of the list and therefore have a higher precedence, so you’ll want to keep an eye on their order.

What happens when more than one conditional formatting rule evaluates to True

For a range of cells, you can have more than one conditional formatting rule that evaluates to True. Here’s how rules are applied, first when rules don’t conflict, and then when they do conflict:

When rules don’t conflict    

For example, if one rule formats a cell with a bold font and another rule formats the same cell with a red colour, the cell is formatted with both a bold font and a red colour. Because there is no conflict between the two formats, both rules are applied.

When rules conflict    

For example, one rule sets a cell font colour to red and another rule sets a cell font colour to green. Because the two rules are in conflict, only one can apply. The rule that is applied is the one that is higher in precedence (higher in the list in the dialogue box).

How pasting, filling, and the Format Painter affect conditional formatting rules

While editing your worksheet, you may copy and paste cell values that have conditional formats, fill a range of cells with conditional formats, or use the Format Painter. These operations can affect conditional formatting rule precedence in the following way: a new conditional formatting rule based on the source cells is created for the destination cells.

If you copy and paste cell values that have conditional formats to a worksheet opened in another instance of Excel (another Excel.exe process running at the same time on the computer), no conditional formatting rule is created in the other instance and the format is not copied to that instance.

What happens when a conditional format and a manual format conflict

For a range of cells, if a formatting rule is evaluated as True, it takes precedence over an existing manual format. You apply a manual format by using the buttons in the Font group on the Home tab (Excel 2016/2013) or Cells group on the Home tab (Excel 2010/2007).

If you delete the conditional formatting rule, the manual formatting for the range of cells remains.Manual formatting is not listed in the Conditional Formatting Rules Manager dialogue box nor is it used to determine precedence.

Clear conditional formatting

You can clear conditional formatting in selected cells or the entire worksheet.

  • To clear conditional formatting in selected cells:

a. Select the cells in the worksheet.

b. Click the Home tab > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

  • To clear conditional formatting in the entire worksheet, click the Home tab > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.

Note: You can’t use conditional formatting on external references to another workbook.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Conditional formatting compatibility issues

Advertisements

Leave a Reply

%d bloggers like this: