Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel 2010: Pivot Tables in Depth, author Curt Frye provides comprehensive, hands-on tutorials on Excel PivotTables, including more advanced techniques such as using macros and the new PowerPivot add-in. The course shows how to connect and consolidate data sources to power PivotTables, sort and filter records, display data in a PivotChart, print tables and charts, and also introduces the DAX language for performing advanced summaries in PowerPivot. Exercise files are included with the course.
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.
There are currently no FAQs about Excel 2010: Pivot Tables in Depth.
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.