Tutorial: Consolidating multiple worksheets in a Pivot Table

This tutorial is in response to a question posed by reader Mr Tom Trainer, who asked on 1st August: "Can pivot tables be used to manipulate 3-D spreadsheets, (i.e. spreadsheets consisting of several sheets in identical format, with different numbers) to effectively rotate the angle at which the spreadsheet is viewed. I want to see, for example, cell C5 from each sheet side by side on a new sheet"

The answer to all these questions is Yes. In the tutorial you will combine multiple worksheets into one consolidated worksheet, then produce different views of the data via pivot tables. The source worksheets do not even have to be identical, just similar. In the tutorial you will amend the format of one source worksheet and see how the new format is reflected in the pivot table.

This tutorial comes with three files:
1/ Startup instructions - instructions on how to download pivot table files. Print these details and return to AccountingWEB
2/ Word tutorial - download this guide and print it off, or follow it below
3/ Excel file - containing the multiple spreadsheet data. Download and save it on your hard disk.

The STARTUP file contains detailed instructions on how to run through the tutorial; but if you have any problems, email editor[at]accountingweb.co.uk with details of the files you're having problems with.

INTRODUCTION

This tutorial shows you how to consolidate and analyse multiple spreadsheets by means of an Excel pivot table.

Budgets for three companies have been submitted to you. You will consolidate them into a group summary, then analyse the group data via pivot tables to see Revenue by company by month, Profit by quarter, and so on.

Note that the source spreadsheets need to be similar in format, but not identical. During the tutorial we will change the structure of one of them and see how this is reflected in the pivot table consolidation.

This is an adapted version of a tutorial published by Mr John Lacher, an American CPA. The original can be found on www.lacher.com - Table of contents ' 'Pivot table multiple consolidation example'. Thanks to Mr Lacher for his guidance.

It should take you about 15 minutes to work through. If possible, it is a good idea for two people to do it together, one reading the instructions, the other operating the keyboard.

You will make extensive use of both left and right mouse buttons. When I say 'right click' I mean click the right mouse button. When I say 'click' on its own, I mean 'click the left mouse button'.

The tutorial was developed and tested on Excel 97. Notes in square brackets [] indicate where other versions of Excel differ. If you are not sure which version you have, find out now by starting up Excel. At the right of the main menu click on Help, then About Microsoft Excel. The version is at the top of the screen.

David Carter 6 August 2000 email david.carter@moose.co.uk

1. STARTUP

Start up Excel. Open the file: lacher99.xls

The file opens. It contains three worksheets, CompanyA, CompanyB and CompanyC.

Each company contains figures from Jan 1995 to Dec 1995, plus a yearly Total.

Click onto cell Jan-95 in cell B10. Note that the cell actually contains the date 01/01/95 which has been formatted to the Date format 'Mar-97' (mmm-yy).

Note also that Mr Lacher has set numbers in front of 'Revenue', 'Cost of Sales' 'Expenses' etc so that these rows will be sorted by Excel into the correct order.

2. CONSOLIDATE THE THREE WORKSHEETS

You are in CompanyA. To consolidate the three companies into one:

From main menu select Data ' Pivot Table Report . Pivot table wizard - Step 1 appears.

The default is 'Excel List or database'. Change to the third option: Multiple Consolidation ranges

Click Next. Step 2a appears. Select: 'I will create the Page fields'. Next

Step 2b appears. The cursor is in the 'Range' box at the top.

Click onto the CompanyA worksheet, then:

Click onto cell A10, 'Category'. Then hold down your Shift key and:

Press Right arrow key across and down to the Profit row (17) until the WHOLE range is enclosed in moving lines ' EXCEPT FOR THE 'TOTAL' COLUMN N (we'll discuss the reason for this later).

The Range box should read CompanyA!$A$10:$M$17

Release the Shift key

Click on Add to insert the Range into the 'All Ranges' box.,

Now click onto the CompanyB worksheet, cell A10 'category'. Repeat the process with CompanyB and CompanyC until you have all three ranges in the box.

3. ASSIGN NAMES TO EACH DATA RANGE

You now have to assign each of these three data ranges a 'Field' name. In the bottom half of the screen, change the number of page fields you want from 0 to 1.

The 'Field One' box becomes available.

In the 'All ranges' box, click and highlight 'CompanyA'. Then:

Click onto the 'Field one' box and type CompanyA

Click and highlight 'CompanyB' in the 'All Ranges' box, then

Click onto the 'Field one' box and type CompanyB

 

Click and highlight 'CompanyC' in the 'All Ranges' box, then

Click onto the 'Field One' box and type CompanyC

Click on: Next Pivot Table Wizard, Step 3 appears.

[Excel 97 only. Click Next to accept the ROW-COLUMN-DATA defaults offered.]

Make sure that the option New Worksheet is selected, not Existing Worksheet.

Simply click on Finish. A new worksheet Sheet1 is created.

Right click on Sheet1 at the bottom of the screen and Rename the worksheet Consolidated

 

4. FORMAT THE NUMBERS TO ZERO DECIMAL PLACES

Right click on any number within the pivot table, then from the menu select: Field.
[or Field Settings, or Pivot Table Field]

The 'Pivot Table Field' dialogue box appears. From the list of options on the right, select:

Number. The 'Format Cells' box appears.

From the 'Category' list, select: Number Then:

Decimal Places = 0 - Use 1000 separator, YES - -1234 in red OK OK

The decimal places disappear and there are commas separating the thousands.

5. SHADE AND REFORMAT THE COLUMN HEADINGS

[Excel 97]
Click on the grey Column field button in cell B3.

The monthly column headings become highlighted.

or
[Excel 2000]
Move the mouse pointer over the top of the grey Column field button in cell B3 until it assumes the shape of a thick black down arrow. Then left click the mouse.

The monthly column headings become highlighted.

Select: Format - Cells - Date - Mar-97 - OK. The dates appear as Jan-95, Feb-95 etc.

To add shading, click on the yellow 'Fill Color' icon in the bottom row of icons, second from right (the one that looks like it's pouring a can of paint).

Now centre the months by clicking the Centre icon.

Then B to make them bold.

Finally, click anywhere to remove the highlight.

6. CHECK THE ACCURACY OF THE CONSOLIDATION

At the bottom of the screen, click onto the worksheets CompanyA, CompanyB and CompanyC and make a note of the profit of each in the first month, Jan-95.

January profit in each was 28, 21 and 39. Total 88.

Click back onto the Consolidated worksheet. The total profit in B11 is 88, which is correct.

The individual worksheets underlying this one can be seen via the Page1 field. Click onto the down arrow in cell B1 next to the Page1 field.

A list appears ' Company A, Company B, Company C.

Click onto Company A. Profit in Jan 95 is 28, which is correct.

Check the figures for Companies B and C.

Finally, click back onto the down arrow in B1 and select (All). The consolidated totals appear once more. (All) represents the sum of all the individual worksheets.

7. REMOVE THE 'GRAND TOTAL' ROW.

However, one thing that is clearly wrong is the 'Grand Total' line in Row 12, which is simply adding together all the values in Rows 5 to 11. Remove it as follows:

Right click on pivot table for menu. Select: [Table] Options. The Pivot Table Options box appears.

At top left, remove the tick against Grand Totals for Columns. OK. The Grand Total disappears.

8. PRESENTING THE CONSOLIDATION IN DIFFERENT WAYS

Make a copy of the Consolidated pivot table as follows:

Right click on the Consolidated tab at the bottom of the screen. From the menu select:

Move or Copy The Move or Copy box appears.

Tick the Create a Copy check box at bottom left. Then: OK

A new worksheet is created with the name Consolidated (2).

Rename the worksheet by Company.

Right click on the new pivot table for menu. Select: Wizard. [Excel 2000 ' Layout]

Drag Column from the COLUMN area and drop it above Row in the ROW area.

Drag Page1 into the COLUMN area. Finish

The pivot table is recalculated to show Companies A, B and C side by side.

The Totals in row 12 are false and need to be removed. To do this:

Double left click on the grey Column field button in A4. The 'PivotTable Field' box appears.

At the left, change 'Subtotals' from Automatic to None. OK. The subtotals disappear.

9. ANALYSE EXPENSES BY CATEGORY AND BY COMPANY

You would like to see the monthly Revenue figures broken down by company

Make another copy of the pivot table as follows:

Right click on the by Company tab at the bottom of the screen. From the menu select:

Move or Copy The Move or Copy box appears.

Tick the Create a Copy check box at bottom left. Then: OK

A new worksheet is created with the name by Company (2).

Rename the worksheet by Category.

Right click on the new pivot table for menu. Select: Wizard. [Excel 2000 ' Layout]

Drag Row from the ROW area and drop it into the PAGE area. Finish

The pivot table is recalculated with 'Row' as the Page field and (All) in B1.

Click onto the down arrow in B 1. A list of categories appears.

From the list, select 1.0 Revenue. The Pivot table displays monthly Revenue by company.

We need to restore the Column totals in order to show yearly totals at the bottom.

Right click on pivot table for menu. Select: [Table] Options. The Pivot Table Options box appears.

At top left, tick the Grand Totals for Columns box. OK. The Grand Total reappears.

Click onto the down arrow in B1 again. This time select 5.0 Profit

Finally, click onto B1 and select 1.0 Revenue again.

 

10. GROUP THE FIGURES BY QUARTER

Click back onto the Consolidated worksheet

At present the figures are monthly. To group them into Quarters:

Double right click on the grey COLUMN field button in B3. From the menu select:

Group and Outline - Group. Click on Months to remove the highlighting. Then click on Quarters to highlight it. OK. The monthly figures are grouped by quarter.

Note that this is the reason why you were instructed NOT to include the 'Total' column of each worksheet when you set up the Data ranges. If you had, when you tried to Group Excel would have returned the error message 'Cannot Group this Selection'. For the Group command to work, the row grand totals must have been calculated by the pivot table

 

11. DRILL DOWN TO CHECK A BALANCE

You are doubtful about the figure of 333 for Quarter 4 Overheads in E10. To check the underlying transactions that make up the total, double click on E10.

A new sheet is created listing the 9 amounts that make up this total.

Click back onto the Consolidated worksheet.

12. 'UNGROUP' THE TOTALS BACK TO MONTHS

We'll go back to displaying the figures by month. Double right click on Column in B3.

From the menu, select: Group and Outline - Ungroup.

The pivot table is now displayed by month.

13. AMENDING THE FORMAT OF THE SOURCE DATA

Check that you can alter the contents of the worksheet - Tools - Protection - Protect Sheet - Uncheck Contents box. The source worksheets do not have to be in identical format. For example, suppose that you wish to add a further row to the worksheet for Company C.

Click onto Company C.

Insert a blank row beneath 3.2 Expense Type 2.

In the blank row A15 type a new category 3.21 Expense Type 2a

Click onto B15. Calculate the new expense type as = B14*0.75

Copy the formula across to all columns including the Total column.

Now click back onto the Consolidated worksheet.

Right click on pivot for menu: Select: Refresh Data.

The pivot table is recalculated and changes.

Expense Type 2a is now included but at the bottom of the list. To sort the rows correctly:

Left click on the grey Row button in A4 to highlight column A.

Click onto the AZ icon. The rows are sorted into numerical order.

Refresh Data automatically adjusted the data ranges you set up. To check this:

Right click for menu - Wizard - Back. Step 2b of the wizard appears

The data range for CompanyC now includes an extra row.

Cancel.

Finally, File - Save As Filename = Consolidate

End of tutorial

Comments
There is 1 comment. Login or register to view it.

Excellent tutuorial

AnonymousUser |