Thread: Consolidate your Data

Excel is a powerful program with many useful features for financial and statistical analysis. If you understand your data well, and know what you want to achieve, then using Excel becomes a routine. Professionals, especially in the Financial sector, will swear by Excel's analytical features. One such feature is PivotTables. If you understand the basic concepts of a PivotTable, the rest is easy (the PivotTable wizard leads you through the process). What you really need to think about is your data, and what form you want to see it in.

Look at data in a flat table (a simple Excel list). Then transform this list into a PivotTable. Suddenly, data becomes much more meaningful, because a PivotTable summarizes it well. Subtotals and totals are automatically added at the end of columns or rows. What's more, you can 'Pivot' the information in the table: transpose rows and columns or hide certain columns and rows. So a PivotTable also lets you look at data from different perspectives.

In this workshop we'll show you how to use a PivotTable to consolidate data in separate Excel ranges. These ranges can either be within the same worksheet or in other sheets within the workbook file (.XLS) or in separate workbook files. But why not use Excel's Data Consolidate feature, you might ask? The PivotTables method is a simple alternative to Data Consolidate, especially when the data to be consolidated resides in separate worksheets, or if the table structures of the different ranges are not the same. Data Consolidate requires the structures to be exactly the same.

STEP 1 - 4: CREATE THE DATA RANGES IN DIFFERENT SHEETS
TIP: After creating a data table, you should sort it in ascending order on the primary column (usually the first column). In this example we have a table for Product Sales in the four territories: North, South, East, and West. So we sort the table on the 'Product' field (as shown in Step 4).



Start Microsoft Excel, open a new worksheet. Click on the Sheet2 tab (at the bottom) and prepare a similar table (or one with your data). Copy this table to Sheet3 and Sheet4 (Step 2). To insert a new sheet after Sheet4, right-click on Sheet4 and choose the Insert option. Now copy the, same data range into the new sheet.
o Click on Sheet3. Change the data values accordingly. Also change the values in other data ranges. You may add titles at the top of each sheet to reflect the territory. Rename the Sheet tabs to reflect the four regions. To do this, right-click on the sheet tab and choose 'Rename'.
o Click on the Sheet2 tab. Select the whole data table (including the headings). o With the table selected, click 'Data

I Sort'. Choose to sort by 'Product' in Ascending order (Step 4). Save the file after creating and sorting all the data ranges. The next thing to do is to define the ranges and create the PivotTable, This is done in Steps 5 to 8.

o Click on the Sheet1 tab. Sheet1 has been left blank for the PivotTable. Then click 'Data I PivotTable and PivotChart report'. The PivotTable wizard appears.

In the dialog box select 'Multiple consolidation ranges'. Also select the option 'PivotTable'. Click the 'Next' button (Step 5).

o In Step 2a of the PivotTable Wizard select 'I will create the page fields'.
This is meant for the Page Axis. We will explain this later. Click 'Next' (Step 6).

o In Step 2b of the PivotTable Wizard you will define the data ranges. Click in the 'Range' box. Then click directly on the 'Sheet2' tab. Select the table and include the column headings in the selection. Then click the 'Add' button (Step 7 & 8). o Select data ranges in other sheets in a similar manner. Don't forget to click the 'Add' button after selecting a data range. Do not click the 'Finish' button yet.

STEP 9 - 12: DEFINE THE PAGE AXIS
After defining the data ranges, the next thing to do is define the Page Axis or Page Field. In the Step 2a of the PivotTable Wizard you selected the option 'I will create the page fields'. Well, you will define this in Steps 9 - 12 of this workshop.
But what is meant by 'Page Axis' or 'Page Field'? In this example, the final PivotTable generated is really a consolidation of the four data tables in four separate sheets. But as we said before, a PivotTable allows you to view data from different perspectives. The Page, Row, and Column fields in a PivotTable let you 'Pivot' the data and view it from different perspectives. These fields appear with a drop-down arrow alongside. You can click the drop arrow and choose what fields need to appear.

As an example we have used the Page Field or Page Axis to view data on the basis of territory. So we can have North, South, East and West Regions as the options for the Page Field. This is defined in Step 10 of this workshop.
Don't forget to click on the Sheet1 tab before clicking the 'Finish' button in the final step.

In Step 2b of the PivotTable Wizard near the question 'How many page fields do you want?' Click on '1' (Step 9). Under 'All ranges' click on a sheet name, for instance 'North' (as in this example). In the 'Field one' box type 'North'. Do this for the other data ranges too. At the end you would have separately defined four options (North, South, East, West) for Field one. These options will later appear in the drop list box for the Page Axis in the PivotTable (Step 10). a Click 'Next' to move on to Step 3 of the PivotTable wizard. Click on the Sheet1 tab. Sheet1 was deliberately left blank for the PivotTable. Also select the option 'Existing Worksheet' in the Wizard. Finally click 'Finish' (Step 11). a The consolidated PivotTable appears in Sheet1 (Step 12).

In the consolidated sheet (Step 12), notice the field 'Page l' at the top. The word 'All' is mentioned alongside. Click the drop list arrow next to it and notice the options that you defined in Step 10. Click on the drop arrow and then on one of the options, say 'North' for instance. Click 'OK'. Notice that the data in the table now changes to reflect the data for the Northern territory.

In this manner you can choose to see selective data ranges in the PivotTable. Click the 'All' option to see the consolidated table again. You could have as many as four fields for the Page Axis. TIP: If you change data in a range, you must refresh the PivotTable to reflect the updated figures in consolidation. To do this, click in the PivotTable and then click the 'Refresh' button (red exclamation mark icon)