Amal Nagm

Conditional formatting helps you answer specific questions about your data. You can apply conditional formatting to a cell range, an Excel table, or a PivotTable report. There are important differences to understand when you use conditional formatting on a PivotTable report.

The benefits of conditional formatting

Whenever you analyze data, you often ask yourself questions, such as:

• Where are the exceptions in a summary of profits over the past five years?
• What are the trends in a marketing opinion poll over the past two years?
• Who has sold more than $50,000 dollars this month?
• What is the overall age distribution of employees?
• Which products have greater than 10% revenue increases from year to year?
• Who are the highest performing and lowest performing students in the freshman class?

Conditional formatting helps to answer these questions by making it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets. A conditional format changes the appearance of a cell range based on a condition (or criteria). If the condition is true, the cell range is formatted based on that condition; if the conditional is false, the cell range is not formatted based on that condition.

Note When you create a conditional format, you can only reference other cells on the same worksheet; you cannot reference cells on other worksheets in the same workbook, or use external references to another workbook.

Conditional formatting for a PivotTable report

Conditional formatting in a PivotTable report is different than a cell range or an Excel table in several ways:

• If you change the layout of the PivotTable report, by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained, as long as the fields in the underlying data are not removed.
• The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined 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.

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

The default method of scoping fields in the Values area is by selection. You can change the scoping method to the corresponding field or value field by using the Apply formatting rule to options button, the New Formatting Rule dialog box, or the Edit Formatting Rule dialog box. The three methods of scoping give you greater flexibility depending on your needs:

Scoping by selection Use this method if you want to select:

• A contiguous set of fields in the Values area, such as all of the product totals for one region.
• A discontiguous set of fields in the Values area, such as product totals for different regions across levels in the data hierarchy.

Scoping by value field Use this method if you want to:

• Avoid making many discontiguous 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 field Use this method if you want to:

• Avoid making many discontiguous 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 by using all visible values, you can optionally apply the conditional format for each combination of:

• A column and its parent row field.
• A row and its parent column field.

Format all cells by using a two-color scale

Color scales are visual guides that help you understand data distribution and variation. A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and red color scale, you can specify that higher value cells have a more green color and lower value cells have a more red color.

Issue: I don't see my conditional formatting for any cell in the range.

If one or more cells in the range contain a formula that returns an error, the conditional formatting is not applied to the entire range. To ensure that the conditional formatting is applied to the entire range, use an IS or IFERROR function to return a value other than an error value.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.

   

3. Select a two-color scale.

   Tip Hover over the color scale icons to see which icon is a two-color scale. The top color represents higher values, and the bottom color represents lower values.

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

Advanced formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. 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 is displayed.

3. Do one of the following:
   • To add a conditional format, click New Rule.

     The New Formatting Rule dialog box is displayed.

   • To change a conditional format, do the following:
     1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
     2. 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 .
     3. Select the rule, and then click Edit rule.

        The Edit Formatting Rule dialog box is displayed.

4. 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.
5. Under Select a Rule Type, click Format all cells based on their values.
6. Under Edit the Rule Description, in the Format Style list box, select 2-Color Scale.
7. Select a Minimum and Maximum Type. Do one of the following:
   • Format lowest and highest values Select Lowest Value and Highest Value.

     In this case, you do not enter a Minimum and Maximum Value.

   • Format a number, date, or time value Select Number, and then enter a Minimum and Maximum Value.
   • Format a percentage Select Percent, and then enter a Minimum and Maximum Value.

     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 the distribution of values is proportional.

   • Format a percentile Select Percentile and then enter a Minimum and Maximum Value.

     Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

     Use a percentile when you want to visualize a group of high values (such as the top 20^thpercentile) in one color grade proportion and low values (such as the bottom 20^th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

   • Format a formula result Select Formula, and then enter a Minimum and Maximum Value.

     The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

   Notes

   • Minimum and Maximum values are the minimum and maximum values for the range of cells. Make sure that the Minimum value is less than the Maximum value.
   • You can choose a different Minimum and Maximum Type. For example, you can choose a Minimum Number and Maximum Percent.

8. To choose a Minimum and Maximum color scale, click Color for each, and then select a color.

   If you want to choose additional colors or create a custom color, click More Colors.

   The color scale that you select is displayed in the Preview box.

Format all cells by using a three-color scale

Color scales are visual guides that help you understand data distribution and variation. A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.

Issue: I don't see my conditional formatting for any cell in the range.

If one or more cells in the range contain a formula that returns an error, the conditional formatting is not applied to the entire range. To ensure that the conditional formatting is applied to the entire range, use an IS or IFERROR function to return a value other than an error value.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Color Scales.

   

3. Select a three-color scale. The top color represents higher values, the center color represents middle values, and the bottom color represents lower values.

   Tip Hover over the color scale icons to see which icon is a three-color scale.

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

Advanced formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. 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 is displayed.

3. Do one of the following:
   • To add a conditional format, click New Rule.

     The New Formatting Rule dialog box is displayed.

   • To change a conditional format, do the following:
     1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
     2. 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 .
     3. Select the rule, and then click Edit rule.

        The Edit Formatting Rule dialog box is displayed.

4. 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.
5. Under Select a Rule Type, click Format all cells based on their values.
6. Under Edit the Rule Description, in the Format Style list box, select 3-Color Scale.
7. Select a Minimum, Midpoint, and Maximum Type. Do one of the following:
   • Format lowest and highest values Select a Midpoint.

     In this case, you do not enter a Lowest and Highest Value.

   • Format a number, date, or time value Select Number, and then enter a Minimum, Midpoint, and Maximum Value.
   • Format a percentage Select Percent, and then enter a Minimum, Midpoint, and Maximum Value.

     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 the distribution of values is proportional.

   • Format a percentile Select Percentile and then enter a Minimum, Midpoint, and Maximum Value.

     Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

     Use a percentile when you want to visualize a group of high values (such as the top 20^th percentile) in one color grade proportion and low values (such as the bottom 20^th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

   • Format a formula result Select Formula, and then enter a Minimum, Midpoint, and Maximum Value.

     The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

   Notes

   • Minimum, Midpoint, and Maximum values are the minimum, midpoint, and maximum values for the range of cells. Make sure that the Minimum value is less than the Midpoint value, which in turn, is less than the Maximum value.
   • You can choose a different Minimum, Midpoint, and Maximum Type. For example, you can choose a Minimum Number, Midpoint Percentile, and Maximum Percent.
   • In many cases, the default Midpoint value of 50 percent works best, but you can adjust this to fit unique requirements.

8. To choose a Minimum, Midpoint, and Maximum color scale, click Color for each, and then select a color.

   If you want to choose additional colors or create a custom color, click More Colors.

   The color scale that you select is displayed in the Preview box.

Format all 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.

Issue: I don't see my conditional formatting for any cell in the range.

If one or more cells in the range contain a formula that returns an error, the conditional formatting is not applied to the entire range. To ensure that the conditional formatting is applied to the entire range, use an IS or IFERROR function to return a value other than an error value.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. 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.

   

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

Advanced formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. 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 is displayed.

3. Do one of the following:
   • To add a conditional format, click New Rule.

     The New Formatting Rule dialog box is displayed.

   • To change a conditional format, do the following:
     1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
     2. 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 .
     3. Select the rule, and then click Edit rule.

        The Edit Formatting Rule dialog box is displayed.

4. 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.
5. Under Select a Rule Type, click Format all cells based on their values.
6. Under Edit the Rule Description, in the Format Style list box, select Data Bar.
7. Select a Shortest Bar and Longest Bar Type. Do one of the following:
   • Format lowest and highest values Select Lowest Value and Highest Value.

     In this case, you do not enter a Shortest Bar and Longest Bar Value.

   • Format a number, date, or time value Select Number, and then enter a Shortest Bar and Longest Bar Value.
   • Format a percentage Select Percent, and then enter a Shortest Bar and Longest Bar Value.

     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 the distribution of values is proportional.

   • Format a percentile Select Percentile and then enter a Shortest Bar and Longest Bar Value.

     Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

     Use a percentile when you want to visualize a group of high values (such as the top 20^th percentile) in one data bar proportion and low values (such as the bottom 20^th 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 Shortest Bar and Longest Bar Value.

     The formula must return a number, date, or time value. Start the formula with an equal sign (=). Invalid formulas result in no formatting applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

   Notes

   • Make sure that the Shortest Bar value is less than the Longest Bar value.
   • You can choose a different Shortest Bar and Longest Bar Type. For example, you can choose a Shortest Bar Number and Longest Bar Percent.

8. To choose a Shortest Bar and Longest Bar color scale, click Bar Color.

   If you want to choose additional colors or create a custom color, click More Colors.

   The bar color that you select is displayed in the Preview box.

9. To show only the data bar and not the value in the cell, select Show Bar Only.

Format all 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.

Issue: I don't see my conditional formatting for any cell in the range.

If one or more cells in the range contain a formula that returns an error, the conditional formatting is not applied to the entire range. To ensure that the conditional formatting is applied to the entire range, use an IS or IFERROR function to return a value other than an error value.

Quick formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, click Icon Set, and then select an icon set.

   

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

Advanced formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. 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 is displayed.

3. Do one of the following:
   • To add a conditional format, click New Rule.

     The New Formatting Rule dialog box is displayed.

   • To change a conditional format, do the following:
     1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
     2. 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 .
     3. Select the rule, and then click Edit rule.

        The Edit Formatting Rule dialog box is displayed.

4. 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.
5. Under Select a Rule Type, click Format all cells based on their values.
6. Under Edit the Rule Description, in the Format Style list box, select Icon Set.
   1. Select 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.
   2. If you want, you can adjust the comparison operators and threshold values. The default range of values for each icon are equal in size, but you can adjust these to fit your unique requirements. Make sure that the thresholds are in a logical sequence of highest to lowest from top to bottom.
   3. 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 visualize all values proportionally because the distribution of values is proportional.

      • Format a percentile Select Percentile.

        Valid percentiles are from 0 (zero) to 100. You cannot use a percentile if the range of cells contains more than 8,191 data points.

        Use a percentile when you want to visualize a group of high values (such as the top 20^th percentile) in one data bar proportion and low values (such as the bottom 20^th 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 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 applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

   4. To make the first icon represent lower values and the last icon represent higher values, select Reverse Icon Order.
   5. 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.
   • There are three sizes of icons. The size of the icon that is displayed depends on the font size that is used in that cell.

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".

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

Quick formatting

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

   

3. Select the command that you want, such as Between, Equal To Text that Contains, or A Date Occurring.
4. Enter the values that you want to use, and then select a format.

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

Advanced formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. 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 is displayed.

3. Do one of the following:
   • To add a conditional format, click New Rule.

     The New Formatting Rule dialog box is displayed.

   • To change a conditional format, do the following:
     1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
     2. 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 .
     3. Select the rule, and then click Edit rule.

        The Edit Formatting Rule dialog box is displayed.

4. 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.
5. Under Select a Rule Type, click Format only cells that contain.
6. 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/2006.

     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 applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

   • Format by text Select Specific Text, select 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 applied. It's a good idea to test the formula in the worksheet to make sure that the formula doesn't return an error value.

   • Format by date Select Dates Occurring, and then select a date comparison.

     For example, select Yesterday or Next week.

   • Format cells with blanks or no blanks Select Blanks or No Blanks.

     Note A blank value is a cell that contains no data and is different than a cell that contains one or more spaces (which are text).

   • Format cells with error or no error values Select Errors or No Errors.

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

7. To specify a format, click Format.

   The Format Cells dialog box is displayed.

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

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

Format only top or bottom ranked values

You can find the highest and lowest values in a range of cells based on a cutoff value that 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 personnel analysis.

Quick formatting

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

   

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

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

Advanced formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. 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 is displayed.

3. Do one of the following:
   • To add a conditional format, click New Rule.

     The New Formatting Rule dialog box is displayed.

   • To change a conditional format, do the following:
     1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
     2. 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 .
     3. Select the rule, and then click Edit rule.

        The Edit Formatting Rule dialog box is displayed.

4. 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.
5. Under Select a Rule Type, click Format only top or bottom ranked values.
6. Under Edit the Rule Description, in the Format values that rank in the list box, select Top or Bottom.
7. Do one of the following:
   • To specify a top or bottom number, enter a number and then clear the % of the selected range check 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 check box. Valid values are 1 to 100.
8. 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.

9. To specify a format, click Format.

   The Format Cells dialog box is displayed.

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

    You can choose more than one format. The formats that you select are displayed 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.
2. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Top/Bottom Rules.

   

3. Select the command that you want, such as Above Average or Below Average.
4. Enter the values that you want to use, and then select a format.

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

Advanced formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. 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 is displayed.

3. Do one of the following:
   • To add a conditional format, click New Rule.

     The New Formatting Rule dialog box is displayed.

   • To change a conditional format, do the following:
     1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
     2. 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 .
     3. Select the rule, and then click Edit rule.

        The Edit Formatting Rule dialog box is displayed.

4. 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.
5. Under Select a Rule Type, click Format only values that are above or below average.
6. 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.
7. 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.

8. Click Format to display the Format Cells dialog box.
9. Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK.

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

Format only unique or duplicate values

Note You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.

Quick formatting

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

   

3. Select Duplicate Values.
4. Enter the values that you want to use, and then select a format.

Advanced formatting

1. Select one or more cells in a range, table, or PivotTable report.
2. 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 is displayed.

3. Do one of the following:
   • To add a conditional format, click New Rule.

     The New Formatting Rule dialog box is displayed.

   • To change a conditional format, do the following:
     1. Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box.
     2. 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 .
     3. Select the rule, and then click Edit rule.

        The Edit Formatting Rule dialog box is displayed.

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

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

Use a formula to determine which cells to format

If your conditional formatting needs are more complex, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values to a result returned by a function or evaluate data in cells outside the selected range.

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 is displayed.

2. Do one of the following:
   • To add a conditional format, click New Rule.

     The New Formatting Rule dialog box is displayed.

   • To change a conditional format, do the following:
     1. Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.
     2. 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 .
     3. Select the rule, and then click Edit rule.

        The Edit Formatting Rule dialog box is displayed.

3. 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.
4. Under Select a Rule Type, click Use a formula to determine which cells to format.
   1. Under Edit the Rule Description, in the Format values where this formula is true list box, enter a formula.

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

      Example 1: Use one conditional format with multiple criteria and cell references outside of the range of cells

      In this formula, one conditional format with multiple criteria applied to the range A1:A5 formats the cells green if the average value for all of the cells in the range is greater than the value in cell F1 and any cell in the range has a minimum value greater than or equal to the value in G1. Cells F1 and G1 are outside of the range of cells for which the conditional format is applied. The AND function combines multiple criteria, and the AVERAGE and MIN functions calculate the values.

      Formula	Format
      =AND(AVERAGE($A$1:$A$5)>$F$1, MIN($A$1:$A$5)>=$G$1)	Green cell color

      Example 2: Shade every other row by using the MOD and ROW functions

      This formula shades every other row in the range of cells a blue cell color. 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.

      Formula	Format
      =MOD(ROW(),2)=1	Blue cell color

   2. Click Format to display the Format Cells dialog box.
   3. Select the number, font, border, or fill format that you want to apply when the cell value meets the condition, and then click OK.

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

   Note You can enter cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) in a formula by selecting cells directly on a worksheet. Selecting cells on the worksheet inserts absolute cell references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.). If you want Microsoft Office Excel to adjust the references for each cell in the selected range, use relative cell references (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.).

Clear conditional formats

• Do one of the following:

  Worksheet

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

     

  2. Click Entire Sheet.

  A range of cells, table, or PivotTable

  1. Select the range of cells, table, or PivotTable for which you want to clear conditional formats.
  2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Clear Rules.
  3. Depending on what you have selected, click Selected Cells, This Table, or This PivotTable.