Show Conditional formatting typically works 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 considerations:
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 option button, the New Formatting Rule dialog box, or the Edit Formatting Rule dialog box.
Note: Quick Analysis is not available in Excel 2010 and previous versions. Use the Quick Analysis button to apply selected conditional formatting to the selected data. The Quick Analysis button appears automatically when you select data.
If you'd like to watch a video that shows how to use Quick Analysis to apply conditional formatting, see Video: Use conditional formatting. You can download a sample workbook that contains different examples of applying conditional formatting, both with standard rules such as top and bottom, duplicates, Data Bars, Icon Sets and Color Scales, as well as manually creating rules of your own. Download: Conditional formatting examples in Excel 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 yellow color scale, as shown below, you can specify that higher value cells have a more green color and lower value cells have a more yellow color. Tip: You can sort cells that have this format by their color - just use the context menu.
Tip: If any cells in the selection 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 other than an error value. Quick formatting
You can change the method of scoping for fields in the Values area of a PivotTable report by using the Formatting Options button that appears next to a PivotTable field that has conditional formatting applied. Advanced formatting
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. Tip: You can sort cells that have this format by their color - just use the context menu. Quick formatting
You can change the method of scoping for fields in the Values area of a PivotTable report by using the Formatting Options button that appears next to a PivotTable field that has conditional formatting applied.. Advanced formatting
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.
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
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
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. Tip: You can sort cells that have this format by their icon - just use the context menu. The example shown here works with several examples of conditional formatting icon sets.
You can choose to show icons only for cells that meet a condition; for example, displaying a warning icon for those cells that fall below a critical value and no icons for those that exceed it. To do this, you hide icons by selecting No Cell Icon from the icon drop-down list next to the icon when you are setting conditions. You can also create your own 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 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
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
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.
Note: You cannot conditionally format fields in the Values area of a PivotTable report by text or by date, only by number. Quick formatting
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. If you'd like to watch videos of these techniques, see Video: Conditionally format text and Video: Conditionally format dates. Advanced formatting
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
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
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
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
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 color). Grade values that are found just once in the Grade column (unique values) are highlighted in a green color.
Quick formatting
Advanced formatting
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. Notes: If there's already a rule defined that you just want to work a bit differently, duplicate the rule and edit it.
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 colors 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.
The following video shows you the basics of using formulas with 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.
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. 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
Find only cells that have the same conditional format
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.
If you'd like to watch a video showing how to manage conditional formatting rules, see Video: Manage conditional formatting. 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.
Clear conditional formatting on a worksheet
Follow these steps if you have conditional formatting in a worksheet, and you need to remove it. For an entire worksheet
In a range of cells
Find and remove the same conditional formats throughout a worksheet
Tip: The following sections use examples so you can follow along in Excel for the web. To start, download the Conditional Formatting Examples workbook and save it to OneDrive. Then, open OneDrive in a web browser and select the downloaded file.
The Conditional Formatting task pane provides everything you need for creating, editing, and deleting rules. Use Manage Rules to open the task pane and work with all the Conditional Formatting rules in a selection or a sheet.
From here, you can:
You can use a formula to determine how Excel evaluates and formats a cell. Open the Conditional Formatting pane and select an existing rule or create a new rule. In the Rule Type dropdown, select Formula.
Enter the formula in the box. You can use any formula that returns a logical value of TRUE (1) or FALSE (0), but you can use AND and OR to combine a set of logical checks. For example, =AND(B3="Grain",D3<500) is true for a cell in row 3 if both B3="Grain" and D3<500 are true. You can clear conditional formatting in selected cells or the entire worksheet.
Color scales are visual guides which help you understand data distribution and variation. Excel offers both two-color scales and three-color scales. 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 yellow color scale, you can specify that higher value cells be more green and lower value cells have a more yellow.
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.
Tip: You can sort cells that have one of these formats by their color - just use the context menu.
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.
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.
This option lets you highlight specific cell values within a range of cells based on their specific contents. This can be especially useful when working with data sorted using a different range. For example, in an inventory worksheet sorted by categories, you could highlight the names of products where you have fewer than 10 items in stock so it's easy to see which products need restocking without resorting the data.
You can highlight the highest and lowest values in a range of cells which are based on a specified cutoff value. Some examples of this would include highlighting the top five selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department.
You can highlight 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 locate manufactured materials that fall below two standard deviations in a quality rating.
If you want to apply an existing formatting style to other cells on your worksheet, use Format Painter to copy the conditional formatting to that data.
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. What can we use conditional formatting for?Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify. This changes the appearance of a cell range based on a condition (or criteria). You can use conditional formatting to highlight cells that contain values which meet a certain condition.
Which formatting is used to format cell values based on a set of conditions?Use Excel conditional formatting to highlight worksheet cells automatically, based on rules (conditions) that you set. Make cells a different colour, or change border, font style, or number format. Rules can be based on a selected cell's contents, or values in a different cell.
How do I apply conditional color formatting in Excel?Conditional Formatting in Excel. Select the range A1:A10.. On the Home tab, in the Styles group, click Conditional Formatting.. Click Highlight Cells Rules, Greater Than.. Enter the value 80 and select a formatting style.. Click OK. Result. ... . Change the value of cell A1 to 81.. Can you apply date formats to cells by using the date category in the format cells dialog box?You can also press CTRL+1 to open the Format Cells dialog box. In the Category list, click Date or Time. In the Type list, click the date or time format that you want to use.
|