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 most popular trends in corporate management is to use icons to indicate how a company's performance compares to its goals. If you set up your workbook as a dashboard that summarizes performance you can indicators such as the familiar red and yellow and green stoplight images to indicate acceptable, unacceptable, and excellent results. In Excel, such a group of indicators is called an icon set. To apply an Icon Set conditional format to a PivotTable, click any cell in the PivotTable data field.
Then on the Home tab, click the Conditional Formatting buttons, point to Icon Sets, and then at the bottom click More Rules. When you do, the New Formatting Rule dialog box appears. The first thing you can do is select which cells to apply the rules to and in this case, you only want the cells in the body of the PivotTable. Not the grand total or subtotal cells. So you should select the third option, which is All cells showing "Sum of Revenue" values for "Month" and "Company".
Next, you can select the icon style that you want. So if you click the down arrow, you can select and I usually prefer to use the black rimmed traffic lights. And now you can establish your rules. Now, it's very important that you select the type of rule you want to create first. In this case this PivotTable contains numerical data as opposed to percentages so we'll create a Number type of rule. So I'll click the Type down arrow in the first row and click Number.
When you select a new type, Excel presets the rules so you can define its values. so in the Value field I want to show a green light for anything a hundred or higher. So I'll edit that values it reads 100, and then I can select the type for the rule that will define what shows a yellow light. So I'll click the second type of cell and click Number, and for that value I'll click 50. So that rule says that it will display a yellow light for any value less than 10 and greater than or equal to 50. And the final rule says it will show a red light or anything else.
So with those changes in place I can click OK and Excel applies the conditional format to the body of the PivotTable. Icon sets can provide useful information about large datasets. But they really come into their own when you use them to summarize a relatively small amount of data. If you or your executive team monitor company performance using dashboards, you should strongly consider using icon sets to enhance your ability to understand the dashboard summary at a glance and also to determine where to reward strong performance and investigate underperformance.
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.