Join Curt Frye for an in-depth discussion in this video Highlighting cells by applying a rule, part of Excel 2016: Pivot Tables in Depth.
- Pivot Tables help you summarize large data collections in Excel Worksheets. But it can hard to find data that matches specific criteria just by looking at the numbers. It's much easier to change a cell's filler or text color to indicate it's status relative to a rule. Formats that change the appearance of cell's contents by applying rules are called Conditional Formats. And Excel 2016 let's you apply conditional formatting rules to Pivot Table cells and have the formats change position when you pivot the pivot table.
I will demonstrate how to apply conditional formatting rules to a pivot table and I'll use the ConditionalRules Excel Workbook. That's a sample file you can find in the Chapter 5 folder of your Exercise Files collection. Creating a conditional format is a lot like creating a filter. In both cases you create rules to indicate which cells you want to be affected by your action. To apply a conditional format to a data field, click any cell in the data field part of the pivot table, and then on the Home tab of the ribbon, click the Conditional Formatting button to display the types of rules you can create.
In this movie I will create a rule-based Conditional Format so I'll point to Highlight Cells Rules, and I can select from Greater than, Less than, Between and Equal to. In this case I will create a Greater Than Conditional Format, so I'll click that. Excel displays the Greater Than dialog box, and I can format cells that are greater than a particular value. In this case Excel used the value from the active cell, but 80,000 is much too low.
Instead I want to display values that are above 275,000. So I'll type that in the box on the left-hand side, 275,000. If I wanted I could have clicked the Collapse Dialog button, and selected a cell with a comparison value that I wanted. But in this case I wanted to use the value 275,000, so I'll click the Expand Dialog button.
Now I can define the format I want to apply. To do that I will click the With button's down arrow. And I see a number of pre-created formats, none of those look interesting, so I'll click Custom Format and that displays the Format Cells dialog box. The Format Cells dialog box has four tabs. Those are Number -which allows you to change the number format; Font - which allows you to change the font, the style, the size and also the color of the value in a cell that meets the rule you're creating; you can also add Borders; and change the Fill color.
In this case I want the cell to stand out, so I will select the yellow color square and click OK. I've created my custom format, so I click OK to apply it to the pivot table. But what you probably noticed is that none of the cells with values above 275,000 are highlighted with yellow fill. The reason is that when you create a conditional format, and a single cell is selected, then you only apply the format to that cell.
However, to the right of that cell, is now a Formatting Options action button. When you click that, you see that you have three options on how to apply the formatting rule you just created. The first is the Selected Cells, which in this case we don't want to do but is certainly an option if you only want to apply conditional formatting to certain values. Next is All cells showing "Sum of Revenue" values, I'll show you what that looks like. And you can see that it includes both Grand Totals and Subtotals.
That's not really what we're going for. We want individual monthly values. So I'll click the action button again and click the final option which is All Cells showing "Sum of Revenue" values for "Month" and "Room Type". So, in other words, cells at the intersection of a Month row and a Room Type column. That would exclude Grand Total, because that is not a Room Type column. So I'll click the last item, and there I see my formatting applied.
As I expected from a previous inspection of the data, the Westminster and Oxford room types account for the vast majority of the values over 275,000 per month. Applying a simple rule-based conditional format can make it much easier to interpret your pivot table data. All you need to do is determine the rule that reflects the data you want to highlight.
- Identify how to format data for use in a PivotTable.
- Discover how to create a new PivotTable.
- Define how to connect to data sources.
- Evaluate and consolidate data from multiple data sources.
- Identify how to create calculated fields.
- Summarize field data.
- Review PivotTable sorting.
- Review PivotTable filters.
- Discover how to work with Excel slicers.
- Recall how to format PivotTables with styles and formats.
- Assess how to build PivotCharts.
- Assess how to run macros.
- Evaluate how to create a PivotTable using the data model.