Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
PivotTables help you summarize large data collections in an Excel worksheet but it can be hard to find data that matches specific criteria just by looking at the numbers. It's much easier to change a cell's fill or text color to indicate its relative value. Formats that change the appearance of cell's contents by applying rules are called conditional formats. Excel lets you apply conditional formatting rules to PivotTable cells and have the formats change position when you pivot the PivotTable. 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. As an example, I will create a greater than rule for the Revenue field. So for this example what I'd like to do is highlight any cell that contains a value greater than 100. To do that, click any cell in the field to which you want to apply the conditional format and then on the Home tab click the Conditional Formatting button, and then select the type of rule you want to create. In this case I want to create a Highlight Cells Rules format.
So I'll point to that item and then I can select which of the criteria I want to use. In this case, I want to create a Greater Than formula. And when I select that option, the Greater Than dialog box appears. Now I can type-in the value that I want the cells to be greater than. So in this case, I want all values greater than 100. So I will type 100 in the left-hand cell and then I can click the down-arrow in the second box, and I can either select an existing format or I can click Custom Format to create the format that I want.
I will create a simple format. I will just fill any cell that contains a value that meets my criteria in yellow. So I'm on the Fill contextual tab and I will click the yellow square. Click OK. Everything looks good in this dialog box so I will click OK again and Excel applies the format, but only to the selected cell. But you'll notice that there is an Options button that appears next to the cell. When you click that, you are allowed to select which type of cells you want to apply the rule to. There are three choices you can make.
The first is the Selected Cell, which means only the cell that you highlighted, or you can select All Cells showing "Sum of Revenue" values. When you click that, Excel includes cells that are in the main data area but also grand totals and subtotals. The third option is All Cells Showing "Sum of Revenue" values for "Month" and "Company". If you select that option, then Excel excludes any cells in grand total and subtotal rows and columns.
In general, you want to apply all conditional formats using the last option which excludes subtotal and grand total cells. Applying a simple rule based conditional format can make it much easier to interpret your PivotTable data. All you need to do is determine the criteria that reflects the data you want to highlight.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90727 Viewers
80 Video lessons · 137983 Viewers
59 Video lessons · 56768 Viewers
52 Video lessons · 70387 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.