Section 9.4. Pivot Charts | Access 2007[c] The Missing Manual

9.4. Pivot Charts

Access lets you create charts based on the data in a pivot table report. In fact, every Pivot Table view has an associated Pivot Chart view. To switch from the table to the chart that displays your results graphically, select PivotTable Tools Design View PivotChart View, or use the view buttons at the bottom-right corner of the window.

With the product pivot-table example from earlier in this chapter, the pivot chart lets you easily spot high-flying groups. You just need to look for the biggest bars, as shown in Figure 9-21.

Figure 9-21. This pivot chart shows a pivot table that's been split into category row groups and country column groups. Each row group appears as a cluster of adjacent bars. You can hover over a bar to see a tooltip that tells you more about it. In this example, the currently selected bar (which is clearly the biggest) shows that bike sales in the U.S. lead all other groups.

Tip: Choose PivotChart Tools Design

Show/Hide

Legend to see a legend box that identifies your groups.

Tip: Before you create a pivot chart, it's often useful to limit the amount of information in your pivot table. Too much information can lead to a chart that's dense and hard to read. The easiest ways to hide data are to avoid using too many levels of grouping, and to restrict groups you aren't interested in, by using filtering, as described in the previous section.

9.4.1. Choosing a Chart Type

Another limitation with pivot charts is that they don't give you many options for data visualization. You can change the type of chart that's used by right-clicking the chart, and then choosing Change Chart Type. A gallery appears with different options. However, most of the charts shown in the gallery, from pie charts to line charts, won't create a decent display of your data if you have a lot of groups. In fact, really only three other reasonable options are worth trying:

A stacked bar or column chart creates a bar for each group, and then subdivides that bar to show you the subgroups (Figure 9-22).

Figure 9-22. In a stacked column chart, each row group is a single bar. The bar is then subdivided into its column groups. In this example, that means you have one bar for each country, and separate regions in the bar represent sales in different categories for that country. The stacked column chart makes it easier to tell how different categories compare. Clearly, bikes lead the sales in all countries .

A 100% stacked bar or column chart is similar, except it stretches every bar to occupy the full height of the chart. This way, you can really compare the sub-groups (Figure 9-23).

Figure 9-23. In a 100-percent stacked column chart, you can't tell which country has the most sales, but you can compare the breakdown. For example, you can find out which country makes the highest proportion of its sales from bikes. (In this example, it appears to be Australia, but the other countries are surprisingly similar.)

A 3-D bar or column chart is basically the same as a normal bar or column chart. It just lets you pack the bars from side to side and front to back in a more logical arrangement (Figure 9-24).

Tip: To rotate a 3-D chart, click somewhere in the blank space around your chart. Then, choose Pivot-Chart Tools Design

Tools

Property Sheet, which shows the Properties window. Now head to the 3D View tab, which is packed full of sliders you can move to get a different perspective on your data.

9.4.2. Printing a Pivot Chart

If you want to print a pivot chart, just use Office button Print (or Office button Print Preview to take a closer look at what your results will look like first).

If you don't have a color printer, you may have trouble distinguishing the different groups. You can pick specific colors for each group, but it's a bit of work. Here's how:

Figure 9-24. In the 3-D column chart, the countries are arranged from left to right, with each product category bar placed from front to back. Sadly, you can't choose which product category is placed at the front and which one ends up at the backit's alphabetic.

Click a specific group somewhere on your chart (like the Bikes group in the Australia column.)
Pause, and then click the group again to select it everywhere . For example, if you click the Bikes group in Australia twice, you wind up selecting the Bikes group in all countries, which is what you want to change .
Choose PivotChart Tools Design Tools Property Sheet from the ribbon to show the Properties window .
Repeat this process for each group you want to change, until you have a nice set of printer-friendly colors .