My Kiva


Ed Ferrero

Pivot Table Tutorial - Part 6


Click the "Next" button. Step 3 of the Pivot Table Wizard appears, click the "Layout" button - now the layout shows the Fields you have brought in from Access.

Drag the Month Field to the Page area, the State, and Rep fields to the Row area, the Commission Percent Field to the Column area, and the Sales field to the Row area. The wizard should look like this.

Pivot Table Wizard - Step 3

Click "Ok" and "Finish" to see the completed Pivot Table. Month is a Page Field that shows the label (All), select Nov-97 from the dropdown list. Nov-97 is the only month showing on the list, because we only brought data from Access where the Sales.Month was Nov-97.

The finished Pivot Table can be found in EG5.XLS. It should be easy to work out sales net of commissions from this table.

Pivot Results

That was a lengthy example, but it covered the process in sufficient detail to hopefully get you started in creating your own pivot Tables from external data.

b) Some cautions

Pivot Tables based on external data can use a great deal of memory. By default all the records sourced from the external data are stored in the Pivot Table cache which is stored with the Excel workbook. Watch the size of your Excel file when saving a workbook containing one or more of these Pivot Tables.

It is possible to save a Pivot Table without saving the underlying data. Look at the "Options" button in Step 3 of the Pivot Table Wizard. However, any changes to the Pivot Table will then result in Excel having to read the original data from the external database again. This can be a time consuming operation for large data sets. It becomes a question of trading memory for speed - finding the right balance is largely a matter of trial and error.

Pivot Table Wizard - Options

4) Using Pivot Tables to consolidate data

a) A forecasting example

Assume that each sales rep prepares a monthly forecast of his sales by wine type. Suppose that, with a bit of training, we could get the sales reps to email the forecast in a common format such as that shown below. EG6.XLS contains separate worksheets for forecasts from four sales reps.

Consolidation Example

It is then possible to use a Pivot Table to sum all the forecasts. EG6.XLS. Sheet1 to Sheet4 contain forecasts in the above format for four sales reps. The sheet named Totals is blank. Select cell A2 in Totals. Start the Pivot Table Wizard and, in Step 1, choose Multiple Consolidation Ranges;

Pivot Table Wizard - Step 1

Click the Next button. Let Excel "Create a single Page field" for you then click the Next button. Place the cursor in the Range box, Click the Sheet1 worksheet tab, and select the range A4:B10. Then Click the Add button.

Pivot Table Wizard - Step 2b

Now Click the Sheet2 worksheet tab. The range A4:B10 is already selected for you, so just Click the Add button. Do the same for Sheet3 and Sheet4. The dialog should now look like this;

Pivot Table Wizard - Step 2b

Click the Next button. The Layout is already set up for you. Click the "Options" button, uncheck the "GrandTotal for Rows" option, and clock "Ok". Place the Pivot Table in the existing worksheet in starting cell A2, click "Finish". You have just summed the forecasts for red wines so place the heading "Red Wines" in cell A1. Repeat these steps, consolidating range D4:E10 in a Pivot Table starting in cell C2.

Then sum the two Pivot Tables to find the total forecast. The completed example is given in EG7.XLS.