Join Curt Frye for an in-depth discussion in this video Formatting PivotTable cells using color scales, part of Excel 2010: Pivot Tables in Depth.
- View Offline
One of the more recent developments in data presentation is the concept of the heatmap which uses a cell's value to determine which fill color to assign to the cell. Unlike rule-based formats that either apply a color or not, the Color Scale conditional format applies a color from a two or three color gradient. Gradients can be hard to visualize if you've never worked with one before, so I'll start with an example and then show you how to create it. I applied a yellow to red format to this PivotTable's data field. The lower the value the more yellow the cell's interior color; the higher the value the more red.
The middle values, which in this example are all around 50%, are filled with a mix of yellow and red making orange. So you can see the 100% are very dark, the 49% is toward the middle, and lower values such as 8% or 7% have a lot of yellow in them. Now I am going to clear the format for the PivotTable and to do that I'll just click Conditional Formatting > Clear Rules. I'll just Clear Rules from This PivotTable to get rid of it. To create a color scale using a built- in format, you can click any cell in the PivotTable and then on the Home tab click Conditional Formatting, point to Color Scales, and then select the color scale that you want.
You can choose from two color scales and three color scales. And yes, the yellow red scale I showed you has orange at the middle, but three color scales have a middle color that also affects the format. Let's say that you wanted to apply the Green-Yellow-Red format and that is where the highest values have green, the middle values have yellow, and the red values are the lowest. If I click that within the palette that appears, Excel applies that format but only to the active cell. If I want to apply it to other cells within the PivotTable I can click the Formatting Options button and then click the last option which applies the format to All cell showing "Sum of Usage" for "Workstation" and "Day", which means that it would exclude any subtotal or grand total cells that were included in the PivotTable.
When you apply that format you see that the highest values are green, the middle values are yellow, and the low values are red. The values between the middle and the high are yellowish green and the values between the middle and low are yellowish red or orange. Now once again I am going to clear this particular format. I'll go to Conditional Formatting > Clear Rules > Clear Rules from This PivotTable. And now I am going to create my own custom color scale, and to do that I'll go back to the Conditional Formatting > Color Scales menu and then click More Rules.
We can now use the controls in the New Formatting Rule dialog box to create our custom color scale. The first thing I am going to do is select the All cell showing "Sum of Usage" values for "Workstation" and "Day", which again applies the format to all cells within the PivotTable, but not subtotal or grand total cells, and then I'll use the controls at the bottom end in the Edit the Rule Description to create my custom two color format. For the lowest values I'm going to choose yellow so we have Minimum and Type indicating that this is the Lowest Value. Click Color and I'll just select yellow, and then for the Highest Values I will select blue.
With those selections in place I can click OK and Excel applies the format. You go through exactly the same procedure when you create a three color scale, but there you obviously have to pick the middle color as well. Color scales provide terrific visual feedback for resource utilization maps. If you run a grocery store and want to track which shelf positions get the most traffic or if you run an Internet cafe and track workstation usage, a Color Scale conditional sormat will indicate which resources are the most and least popular.
- Formatting data for use in a PivotTable
- Connecting to an external data source
- Refreshing a data source
- Adding, removing, and positioning subtotals and grand totals
- Creating a calculated field
- Grouping PivotTable fields
- Clearing and reapplying PivotTable filters
- Applying field styles
- Formatting cells
- Creating a PivotChart
- Printing PivotTables and PivotCharts
- Creating relationships between tables in a PowerPivot model
- Using the DAX language for advanced summaries in PowerPivot