Chapter 6. Using Excel Charts and Pivot Tables with Access Data

There is something about putting your information into a colorful chart or summarizing a tremendous amount of data in a pivot table that really adds to an analysis. Unfortunately, some Excel users struggle with putting together a simple chart, and many have never even heard of pivot tables. This chapter has two main goals: first, to explain why these built-in Excel features are important and how they can be used; and second, to show how to automate reporting of Access data through Excel charts and pivot tables with VBA.

Excel has many built-in standard chart types, as well as some custom chart types. There are several custom chart types that allow you to plot data on two axes, which is very useful for analytical functions. For example, you can graph sales on one axis and margin on another to see if periods of high sales correspond with lower margins. While you could see the same thing by looking at a numbers-only report, using a chart really illustrates the analysis. These are the standard types of charts that are built into Excel 2003:

Column	Line	XY (Scatter)
Donut	Surface	Stock
Cone	Bar	Pie
Area	Radar	Bubble
Cylinder	Pyramid

Excel also offers a number of built-in custom charts:

Area Blocks	B&W Column	B&W Pie
Colored Lines	Columns with Depth	Floating Bars
Line-Column on 2 Axes	Logarithmic	Pie Explosion
Stack of Colors	B&W Area	B&W Line (Timescale)
Blue Pie	Column-Area	Cones
Line-Column	Lines on 2 Axes	Outdoor Bars
Smooth Lines	Tubes

While there are many charts available, pick the chart type that shows your data most directly. Readers should be able to quickly see what the chart is trying to show. If you have something specific to highlight, try your data with a number of different types of charts and ask people what they think the graph is showing. After ensuring that the graph is easily understood by readers, consider whether the graph should be printed in color. I have been on the receiving end of a number of charts where you could not tell the difference between series because the graph was printed in black and white. Another good tip is to use different markers on line charts for each series to easily distinguish them, even if you are also using color.

You can easily use graphs in conjunction with a pivot table. If you want to graph a pivot table in Excel 2003, right-click on the PivotTable and select PivotChart from the menu. While PivotCharts may seem many levels deep in the GUI interface, they can still be automated by VBA.

When creating charts, I generally recommend using named ranges instead of fixed ranges because they make it much easier to handle situations where the number of items being charted changes. Regardless of whether you use fixed ranges or named ranges, you can to open an existing Excel workbook from Access and update data used in an existing chart. You can then have code run to set your upper and lower bounds of each axis, etc. Many companies have monthly or quarterly analytics involving graphs that include things like cost per item, average hold time, customer satisfaction scores, etc. If you have a working Excel workbook that graphs information, simply open the workbook with VBA, push in the new data, and print the new graphs all without leaving Microsoft Access. This is one of the easiest types of automation and will often save you hours of work each reporting period.

This chapter later covers automation of charts. However, because of the ability to easily create charts from pivot tables, automation of pivot tables will be covered first. Both creating charts from scratch with VBA and changing data and/or data ranges of existing charts will be covered. One other technique relating to charts covered in this chapter is using VBA to change a variable in a simple model and graphing the results. You will find this very useful for financial applications.