In my previous article, “Teach users to manipulate data with Excel pivot tables,” I described how pivot tables can be created and used to manipulate data. Although pivot tables allow us to analyze our data, they’re not the most appealing way of displaying the results of this analysis. The best way to present our findings is through the use of a chart, specifically a pivot chart. With pivot charts, we can perform the same operations we did with pivot tables, but display the results in a manner that provides more impact.
Because the instructions outlined in this article draw heavily on those presented in my article, “Teach users to manipulate data with Excel pivot tables,” I highly recommend you read that piece before proceeding.
Creating a pivot chart
First, select the Chart Wizard button in the PivotTable toolbar (see Figure A).
|Select the Chart Wizard button.|
Doing so will immediately bring up the pivot chart on a new worksheet, as shown in Figure B.
|A pivot chart is worth a thousand words.|
We can now manipulate the pivot chart in the same manner as we did with the pivot table, only now the results will be shown graphically. For example, the pivot chart currently shows the minimum salary for both groups, as reflected in the pivot table we created in the previous article. To see what the average salary is, we first double-click the Min of Salary button in the upper left corner. This will bring up the PivotTable Field dialog box. We then select Average from the Summarize by: scroll box just as we did when we worked with the pivot table (see Figure C). After clicking OK, the chart will now display the average salary for both groups.
|Selecting Average will make the pivot chart display the average salary for both groups of employees.|
In addition to changing how the data is calculated, we can also add more fields to the chart by dragging and dropping them to the Drop More Series Fields Here box located above the legend on the pivot chart (see Figure D).
|Add more fields by using Drop More Series Fields Here.|
For example, to break down the average salaries for both groups by division, we would drag and drop the Division field from the PivotTable toolbar to get the results shown in Figure E.
|The average salaries by division for both groups of employees are now displayed.|
Once a pivot chart is created, it can be changed and formatted like any other chart. For example, you can change the chart type by clicking the Chart Type button in the Chart toolbar. You can also use the Chart toolbar to make any other formatting changes to your pivot chart.
|You can customize your pivot chart to meet your specifications.|
Practice makes perfect
Now that your students have been shown how pivot tables and pivot charts can be used to analyze and present data, they should try their new skills with other relatively simple databases, e.g., a 20-record customer order database. Just as when we created the pivot table and chart used here and in the first article, the new database should consist of only those fields that can be used to ask a meaningful question of the data, such as what is the dollar sum of all orders for each product? In this case, you should keep the product list to no more than five so that the students can easily check their results against the data. In the end, students will find that success comes not from clicking the right buttons, but from asking the right questions!
What do you think of the information presented in this article? Were Mary Ann’s instructions clear, concise, and helpful? What type of training material would you like to see us cover in the future? We want to know what you think. Post a comment or write to Mary Ann Richardson.