Using conditional formats lets you add visual cues that describe your PivotTable data. In this video, learn how to highlight cells by applying a rule.
- [Instructor] PivotTables helps 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 is much easier to change a cell's fill or text color to indicate its relative value. Formats that change the appearance of a cell's contents by applying rules are called conditional formats. In this movie, I will show you how to apply conditional formats to a PivotTable. My sample's feel 05_01_ConditionalRule and you can find it in the chapter five folder of your exercise files collection. The PivotTable I have here displays data for states in the eastern half to the United States and I've broken down olive oil sales by extra virgin and regular categories. Let's say that I want to highlight only those cells that contain values of greater than $800 and if I want to highlight only cells that contain values greater than 800, then I need to go the Analyze contextual tab of the ribbon, click Select and then click Entire PivotTable. So, that selects the entire pivot table rather than just individual cells. Now I can create my conditional format by going to the Home tab of the ribbon, click the Conditional Formatting button, point to Highlight Cells Rules and from here I can select the type of rule that I want to create. So, I will click Greater Than and that displays the New Formatting Rule dialog box. Well, let's say that instead of values greater than 800, I want 800 or more, so that's a small change but it might make a difference, so I will click the operation or comparison operation controls arrow Now I can click in the box next to it and type in a value or if I wanted, and the click a cell within the body of the worksheet but in this case I'll just stay with typing in 800. Now I can assign the format and it will open to the last tab that you had open. So, I'll start looking at Fill. If you're not on the Fill tab, then go ahead and click it in the tab bar at top. We're looking for higher values, and click OK. Everything looks good, so I'll go ahead and click OK The first is that my background color, the fill color is fairly light, so the white text doesn't stand out a lot Because we're adding values together, it's much more likely that I would get a grand total of greater than 800 for individual states and categories. and again, that's on the Home tab of the ribbon that I want to change and in my case, that's the only one. And click Edit Rule. I want to change my format, so I will click the Format With button and click Custom Format. And I'm back in my Format Cells dialog box. I'll leave my font colors white but I will go to fill and I'll click background color and I'll change it to a much darker green. Right, everything looks good there. So, I'll go ahead and click OK. And OK again. Next, I want to change the range of cells that the rule applies to. To do that, I can work from within the Applies To box here, so I'll click the collapse dialog button, I will select the cells that I want the rule to apply to, click the expand dialog button here and click OK. now reflects what I want to get out of this pivot table. then you see that I still have the format applied to the same cells as before. However, if I were to remove product category by dragging it up to the field list, and then drag product category back, then my conditional format is gone as well. So, one limitation of working with conditional formats in Excel for Mac is that when you're within the pivot table, any changes that you make to scope will remove the conditional format entirely.
Note: This course was recorded in Office 365 for Mac. However, much of the content will be useful to those working with Office for Mac 2019 and 2016.
- Formatting data for use in a PivotTable
- Creating a new PivotTable
- Connecting to external data sources
- Summarizing totals and other data fields
- Creating calculated fields
- Summarizing field data
- Sorting and filtering PivotTables
- Working with Excel slicers
- Formatting PivotTables styles and layouts
- Applying conditional formats
- Summarizing data visually using a PivotChart