- Planning an Approach
- Planning the Workbook Structure
- The VBA Code
- The Analysis Sheets
This chapter is from the book
This chapter is from the book
This chapter is from the book
The Analysis Sheets
I use several worksheets and chart sheets to analyze what’s going on with the sales of my books. For an analysis that looks at the data over time, a pivot table (and pivot chart) is a nearly perfect solution. It collapses the hourly figures that I get from my web queries into more manageable time slices—I find that summarizing by month strikes a good balance between the overwhelming amount of data in an hourly analysis and the lack of detail in an annual or even quarterly analysis. Of course, when your baseline extends over several hundred days, it’s time to start thinking about quarters or years as your unit of analysis.
Figure 1.9 shows one of the pivot tables that I rely on to synthesize the data on sales rankings.
)
Figure 1.9 When you connect this pivot table to a pivot chart, you can start making real sense of the data.
As powerful as pivot tables are—and I believe that pivot tables are the most powerful and flexible tool for data synthesis and analysis available in Excel—they can’t tell when you have changed the underlying data, and (without help) they can’t tell that their underlying data range has added another row.
In contrast, something as simple as the SUM( ) function can update itself when the underlying values change. If you have entered this formula
=SUM(A1:A5)
in some cell, the value it returns changes immediately if you change any of the values in A1:A5. That’s not true of a pivot table that’s based on those cells or any other cell. You have to do something special to refresh the table when the underlying data changes.
But even SUM( ) won’t change its own argument. If you now put a new value in A6, SUM( ) doesn’t change itself from SUM(A1:A5) to SUM(A1:A6).
The way I prefer to handle that is by means of dynamic range names. You might already know that you can assign a name to a worksheet range and use that name instead of a range address. If you have given A1:A5 the name Addends, you can use this instead of SUM(A1:A5):
=SUM(Addends)
I show you how to name a range shortly. First, you should know that if you define a range properly, you can get it to change its own dimensions when you add a new row or column to it. (Tables, which were new in Excel 2007 and have therefore been around for a while now, do that automatically. If you’re not familiar with them you should probably investigate their capabilities. I still prefer dynamic range names because of a possibly irrational belief that I can control them better. And I don’t care for the structured formulas that, optionally, come along with tables.)
Defining a Dynamic Range Name
Begin by selecting the worksheet that contains the range that you want to name. This step is not strictly necessary, but I usually find it helpful. Then follow these steps:
Click the Ribbon’s Formulas tab.
Click the Define Name button in the Defined Names group. The New Name dialog box shown in Figure 1.10 appears.
Figure 1.10 You can use a formula instead of an address in the Refers To box.
Enter the name you want to establish in the Name box. I use SalesData as the name of the dynamic range in the Collector workbook.
In this case, leave the Scope with the default value of Workbook.
I base the pivot table on the data in my Summary sheet, shown in Figure 1.8. With the Summary sheet’s layout as shown there, type the following formula in the Refers To box:
=OFFSET(Summary!$A$3,0,0,COUNTA(Summary!$A:$A),17)
Click OK.
)
Here’s a quick explanation of the Refers To formula. The OFFSET( ) function returns a range of cells that are offset from an anchor cell. Here, the anchor cell is defined as $A$3 on the Summary sheet, and the dollar signs make it a fixed reference. Notice in Figure 1.7 that cell A3 is where the label When is entered, and more labels follow it in row 3, columns B through Q. I want to include those labels in the defined range name because they’re needed for the pivot table.
The two zeros that follow $A$3 in the OFFSET( ) function tell Excel how many rows and how many columns away from A3 the resulting range should begin. In this case, because both values are zero, the resulting range is offset by zero rows and zero columns: that is, the range begins at cell A3.
The fourth argument to the OFFSET( ) function, COUNTA(Summary!$A:$A), tells Excel how many rows to include in the offset range. The COUNTA( ) function tells Excel to count the number of values in (here) column A on the Summary worksheet. So when the VBA code runs and adds a new value in column A, the COUNTA function counts an additional value and redefines the number of rows in the range. That’s how adding a new row at the bottom of the range causes the definition of the range to automatically increase by one row.
The final argument to the OFFSET( ) function tells Excel how many columns to include in the result range. Here I want to include seventeen columns: one for the date, eight for the book rankings in columns B through I, and eight for the presumed book sales in columns J through Q.
It’s called a dynamic range name because the dimensions of the range can automatically change depending on the number of records that are added to or removed from the range.
Using the Dynamic Range Name
With the dynamic range name defined, you can use it when you create a pivot table. Here’s how to do that:
Begin by activating a blank worksheet and then click the Ribbon’s Insert tab.
Click Pivot Table in the Tables group. The dialog box shown in Figure 1.11 appears.
Figure 1.11 Use the dynamic range name you defined in the Table/Range box.
Type the name you supplied in Step 3 of the prior numbered list into the Table/Range box. This causes Excel to use that range as the data source for the pivot table.
Click OK. The PivotTable Fields pane appears.
Drag the When field into the Rows box.
Drag the Stats field into the ∑ Values box.
Drag the Stats K field into the ∑ Values box. Continue adding to the ∑ Values box each sales estimate field that you want to appear in the pivot table.
Dismiss the PivotTable Field List by clicking its Close box. The pivot table now appears as shown in Figure 1.12.
Figure 1.12 You still need to group on date.
Right-click in the date column of the pivot table and choose Group from the shortcut menu.
Grouping by Months is the default selection. Click OK.
The pivot table might seem to disappear from the visible worksheet. That can happen when the grouping operation causes the pivot table to occupy many fewer rows. Just scroll up to it.
)
)
){kind=link}
){kind=link}
The pivot table should now appear as shown in Figure 1.13. (It differs from the pivot table shown in Figure 1.9 because that figure shows the total of the paperbound and the electronic editions.)
)
Figure 1.13 You can also use the Value Field Settings to select a display format for the summary values in the pivot table.
A pivot table such as the one in Figure 1.13 isn’t very informative by itself. Fortunately, it’s easy to create a pivot chart from a pivot table. Just select any cell in the pivot table, click the Ribbon’s Insert tab, and choose (say) a Line chart from the Charts group.
You get a chart embedded in the active worksheet, and you can usually tell more about your data, at least on a macro level, from the chart than directly from the pivot table (see Figure 1.14).
)
Figure 1.14 You usually need to do some tinkering with the pivot chart before it starts to show you what you need to know.
One of the useful aspects of pivot charts is that they don’t need any special handling to keep up with the underlying pivot table. When the pivot table’s data changes, or gets more rows, the pivot chart updates automatically.
You do need a way to force a refresh of the pivot table. There are many ways to handle that. Some are mildly complicated, such as writing a special Worksheet Activate event handler using VBA. That event handler would force a refresh any time you activate the worksheet that contains the pivot table. Probably the simplest way is to right-click any cell in the pivot table and to choose Refresh from the shortcut menu.
Among the tweaks I like to apply to a newly created pivot chart are the following:
Right-click an embedded chart, click Move in the shortcut menu, and choose to move the chart to a new sheet.!
Right-click a data series in the chart and choose to format it. Reduce the width of the lines and change any obnoxious color to a more acceptable one—black, for example.
Add a trendline. Right-click a data series and choose Add Trendline from the shortcut menu. In line charts, linear trendlines and moving average trendlines are often very informative. Tinker with the length of a moving average trendline until you get something that smooths out the rough in the series but preserves its basic movement. Chapter 3, “Forecasting with Moving Averages,” and Chapter 4, “Forecasting a Time Series: Smoothing,” provide guidance on selecting the length of a moving average.