This article updates IT Zone consultant editor David Carter's hugely popular 1999 pivot table tutorial, which applies Excel's powerful multidimensional analysis tool to a common business activity - the annual budget.

Introduction
For the management accountant, Excel pivot tables represent the most important development in IT since the original invention of the spreadsheet.

If you are regularly required to analyse data, or to prepare and present management reports, they are an indispensable tool. Particularly valuable is the facility to export raw transactions out of a company's accounting system (Sage, Pegasus, Sun etc), import them into Excel, then use pivot tables to analyse the transactions and present the results in a customised report.

Microsoft first introduced pivot tables in version 5 of Excel, released in 1994. The basic design has changed little since then, but the number of records that can be analysed has consistently increased. Excel 5 could handle a maximum of 16,000. Excel 97 went up to 65,000 records. In practice, the only limit now to the number of records a pivot table can handle is the physical memory of your PC, so pivot tables are suitable for use in all but the largest applications.

Note 1:This tutorial is designed to be used with Excel 2003 and Excel 2000. There is an earlier version for use with Excel 5, Excel 95 and Excel 97 [1].

More Pivot Table tutorials are available via IT Zone [2].

Tutorial methodology
In this tutorial you are an accountant setting up next year's budget for your company. For simplicity's sake the number of nominal ledger accounts has been reduced to a minimum and the budget is only for three periods. (Note: in the USA Nominal Ledger = General Ledger).

The company is made up of 4 departments, each of which contains 2 or 3 staff. Each member of staff is treated as a separate cost centre. The departments and their staff are:

• Administration (Arthur and Anita)




• Design (Diana, David and Doug)




• Marketing (Mick and Maureen)




• Production (Pat, Paula and Peter)

Download the Excel file [3] to your own computer.

You can also download these instructions as a Word file [4], or follow them on screen. If you have any problems please email the editor [5] at AccountingWEB and we will email you copies of the data files by return.

The tutorial should take you about 20-30 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'.

Tip One ' If you make mistakes, it's easier to start again
If you find that the instructions seem OK, but at some point what's on your screen doesn't seem to tally with what the tutorial says, try clicking several times on Excel's Undo icon (the left curving arrow in the icon bar at the top). This will cancel your previous steps. But if the error does not immediately come to light, it is better not to try to correct any errors but to go back and start over again. To do this, close the file, and do NOT save. Then re-start.

Tip Two ' Repeat the tutorial
First time around you cannot expect to memorise each section, and probably by the end you will have forgotten what you did at the beginning. So if you want to teach yourself how these pivot tables work in detail, be prepared to go work your way through the tutorial two or three times. Second or third time around you will find that a lot of things fall into place that didn't really register earlier because then you were just concentrating on following the instructions.

Contents of the budget database
1. Start up Excel. Open the file: budgdem.xls

The Excel document before you should have the headings: NLGROUP, NLCODE, NLDESC, CC, DEPT, YR, PD, MTH, BUDGET, COMMENTS

To make sure that you have downloaded the complete file, go to the bottom of the spreadsheet. There should be 101 rows.

2. Click on the grey I column heading above BUDGET. The column is highlighted.

At the bottom of the screen it should say Sum = 187633. Make a note of this number.

3. Find the worksheet name at bottom left (it is probably Sheet1). Right click onto the Sheet1 name.

A menu appears. Select Rename. Rename the worksheet MASTER.

4. Click back onto the data in the worksheet.

Generating the pivot table
1. Click onto a single cell, eg C10 (It has to be one cell. If more than one cell is highlighted, the pivot table won't work).

We'll build up the pivot table step by step.

2. From the main menu at the top of the screen, select:
Data - Pivot Table and Pivot Chart Report

The Pivot Table Wizard, Step 1 of 3 screen appears. Click on Next.

3. Step 2 of 3 The Range box should say: $A$1:$J$101.

[Earlier versions of Excel sometimes didn't pick up the range automatically. If the Range box is blank or says Database, type in $A$1:$J$101].

4. Click on Next.

5. Step 3 of 3: Click onto Layout at the left.
The COLUMN'ROW-DATA box appears.

To the right are all the column headings of your spreadsheet. Drag and drop them as follows:

NLCODE into the ROW area.

BUDGET into the DATA area.

[When dropped into the DATA area, BUDGET should now say 'Sum of BUDGET'. If it says 'Count of BUDGET', double click on Count of BUDGET. A box appears. Change 'Count' to 'Sum']

Now click on OK. Finish.

A list of Nominal Codes appears with a total beside it. The total at the bottom of the sheet is the total you noted down.

Excel has taken the 101 budget records in the MASTER worksheet and generated a pivot table that summarises them by nominal code

Look at the bottom left of your screen. To create the pivot table, Excel has generated a new worksheet to the left of MASTER.

6. Click back onto the MASTER worksheet to confirm that your original Budget Database is still there.

7. Now right click back onto the new worksheet with the pivot table. From the menu Rename it PIVOT.

Changing the pivot table
Nominal codes on their own don't tell us much. We'll add the Nominal Description as well:

1. Click anywhere within the pivot table, then right click the mouse.

The pivot table menu appears. Format Cells is at the top.
[In Excel 2003 a Pivot Table Field List and a Pivot Table icon bar are displayed. For the purpose of this exercise, close them.]

2. From the menu, select Pivot Table Wizard. OK Layout.

The COLUMN-ROW-DATA box re-appears.

3. Drag and drop NLDESC below NLCODE in the ROW area. Then: OK Finish.

The nominal description is displayed on the pivot table, but the screen is not very clear as the NLCODE is totalling. These totals need to be removed.

4. Right click on the grey NLCODE field button in cell A4. The menu appears. Select: Field Settings. The Pivot Table field box appears.

5. In the Subtotals area on the left, change from Automatic to None. Click OK That looks better.

Tidying up the pivot table
We want to centre the Nominal Code and put commas between the thousands.

1. Move the pointer over the top of NLCODE in A4 until it turns into a thick black down arrow. Then left click. The column is highlighted.

2. Centre the account codes by clicking the Centre icon.

3. Click anywhere to remove the highlighting.

4. Now right click on the pivot table to produce the menu. Select: Field Settings. The Pivot Table Field box appears. From the list of options on the right, select: Number.

5. The 'Format Cells' box appears. From the 'Category' list, select: Number. Then:
Decimal places = 0 - Use 1000 separator? YES - click on the -1234 in red at the bottom of the list - OK - OK

Commas now separate the thousands

Adding more columns
Next we'll break the Total down by period:

1. Right click anywhere within the pivot table (you must always click within the pivot table. Clicking outside it brings up the wrong menu).

2. Select: Pivot Table Wizard. Layout.
The COLUMN-ROW-DATA box re-appears.

3. Drop PD into the COLUMN area. OK Finish. The pivot table breaks down by Period.

4. Once again, right click for the menu option Pivot Table Wizard-Layout. This time drop NLGROUP above NLCODE, so that the ROW area contains NLGROUP, NLCODE and NLDESC.

5. OK Finish. The pivot table is divided into '71-Salaries, ' 72-Marketing' etc, each with a total.

Shade subtotals and column headings
We'll add shading to the subtotal lines and the column headings.

1. In cell A10, move the mouse pointer onto the '71' in '71-SALARIES Total' until it assumes the shape of a thick horizontal black arrow.

2. Now left click the mouse. All the sub-total lines are highlighted

3. 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).

4. Now click anywhere to remove the highlighting. The Total lines are all shaded yellow.

In addition, we'll shade and centre the monthly column headings, as follows:

5. In cell D3, move the mouse pointer around the top of the grey PD field button until it turns into a thick black down arrow, then click. The column headings '1' to '3' are highlighted.

6. Apply the yellow Fill Color icon again. Or if you prefer another colour, click on the down arrow to the right of the icon, and select a colour from the palette. Personally, I like light blue or light green in the bottom row.

7. Centre the months by clicking the Centre icon.
Click anywhere to remove the highlight.

Drilling down on a balance
One area where screen-based reports like pivot tables score over paper-based reports, is in the area of 'drill-down'. If you wish to query a balance, it is a simple matter to double click on it and drill down to see the underlying entries.

For example, the figure of £12,000 in E14 for Internet Costs, period 2, looks rather large. To query it:

1. Double click on the 12000 in cell E14. The originating transaction appears with the comment: 'Set up new web site'

2. Look at the bottom left of your worksheet. Your drill down has generated a new worksheet.

3. Click back onto PIVOT. Let's try another example: In D10 the figure of 10,960 for PR & Promotions looks very high. Double click on it.

4. The balance is made up of the standard budget of £1,500, plus £9,460 for the Olympia exhibition.

5. Click back onto the PIVOT worksheet.

This ability to drill down on any balance is very useful. For example, if you use pivot tables to report monthly costs to departmental managers, to query a figure they simply have to double click on it to see a list of the underlying transactions.

Breaking down by department
So far we've presented the budget in a standard Profit and Loss format for the whole company. Now we'll break down the company total by departments:

1. Right click on pivot table to bring up the menu. Pivot Table Wizard-Layout.

2. Remove PD from COLUMN by dragging and dropping it anywhere outside the box.

3. Drop DEPT into the COLUMN area. OK Finish.
Now you see the totals for ADMIN, DESIGN, MARKETING and PRODUCTION.

But we may wish to analyse by both Department and Period. Excel allows you to add another dimension of analysis via Page fields.

4. Right click on pivot table to bring up the menu.
Pivot Tables Wizard'Layout.

5. Take DEPT and drop it onto the PAGE area.
Put PD back into the COLUMN area. OK Finish.

The pivot table is once again broken down by period. But in cell A1 it says DEPT. In B1 it says (All)

6. DEPT is a Page field. To see how it works, click on the down arrow in the right of cell B1.

7 ADMIN, DESIGN, MARKTG, and PRODN appear. Highlight MARKTG and OK. The pivot table now shows a mini Profit and Loss account just for the Marketing department.

8. Click on the B1 down arrow to see the budgets for ADMIN, DESIGN and PRODN. [Note that the (All) option signifies the company total.]

9. In B1, click back onto MARKTG once more.

We will now break down the Marketing budget farther into individual cost centres (CC).

10. Right click on pivot for menu. Pivot Table Wizard-Layout.

11. Drop CC underneath NLDESC. Then double click on NLDESC. The Pivot Table field box appears.

12. In the 'Subtotals' section, change from Automatic to None. Click OK at top right. OK Finish.

The pivot further subdivides to show the costs of Mick and Maureen, the individual members of the Marketing department. [Note that a CC of (blank) means that the budget was allocated to the Marketing department as a whole, not to any individual within Marketing.]

Hide detail and show detail
The larger your spreadsheet becomes, the more levels the pivot table is liable to acquire. The Hide Detail and Show Detail commands allow you to expand and collapse it between different levels.

1. To see this, first click on cell B1 and select (All). This displays the budget for the whole company down to the (lowest) Cost Centre level.

2. To collapse the pivot table up to the highest level, simply right click on A4, NLGROUP. The menu appears.

3. Select: Group and Show Detail-Hide Detail
The pivot table is summarised at top level ' 71-Salaries, 72-Marketing, 73-Establishment etc..

4. To see more detail for the Salaries group:
Double left click on 71-SALARIES in cell A5.
Excel expands the Salaries detail down to CC level.

5. To revert to the higher level, double left click in the space below 71 ' SALARIES (eg A6 to A20). The pivot table once again is summarised at NLGROUP level.

6. Finally, show the entire pivot table in full detail once more, as follows: Right click on NLGROUP in cell A4. The menu appears.

7. Select: Group and Show Detail-Show Detail. The full company detail is displayed once again.

Hiding a column
Sometimes, also, you might want to see cumulative totals for selected periods. For example, suppose that you now want to see cumulative totals for Periods 1 and 2 only:

1. On the grey PD field button in E3 there is a down arrow. Click on it. Periods 1, 2 and 3 are listed, all ticked. Remove the tick on Period 3. OK

The period 3 column disappears and the cumulative totals for each NLCODE are recalculated.

Revising the budget
The greatest advantage of using pivot tables for budgetting is the control they give you when making any revisions. It is very easy to change a budget figure, then instantly see the results of the change. Nor are there any formulae whose logic must be rechecked each time you amend the budget.

Suppose, for example that Marketing want to amend their budget. Firstly, unhide period 3 as follows:

1. Double right click on PD in cell E3. Select: Field. Click on 3 to remove the highlight. OK

2. Click the down arrow in the PD field in E3.
Tick 3 once again. OK

3. Period 3 returns. Now click on the down arrow in B1 and select MARKTG.

You should see the budget for Marketing, broken down to the cost centre (Maureen and Mick) level.

Revision 1 ' correct an error in Direct Mail
Suppose that the figure of £50 in F12, Direct Mail, period 2 was an error and should have been £500.

1. Make a note of the NLCODE ' 72030.

2. Now click onto the MASTER worksheet containing the original 101 budget records.

3. Left click on anywhere in the data, then in the main menu select: Data-Filter-Autofilter

4. Down arrows appear against every column heading. In B1, click the Down arrow beside NLCODE.

5. A list of NLCODES appears. Highlight 72030 and click. Excel finds 3 records (see message at bottom left). [If the screen is blank you may need to scroll up to Row 1 to see them.]

6. Change the Period 2 (May) BUDGET figure from 50 to 500.

7. Now click back onto PIVOT. Check that the figure for Direct Mail, Period 2 is still 50.

8. To update the change, right click on the pivot table to bring up the menu, then: Refresh Data

The 50 changes to 500 and all the related column and row totals are updated.

Revision 2 ' move Doug out of Design into Marketing
Suppose that in period 3 Doug is going to move out of Design department and into Marketing. Also, his salary will increase from £1,917 to £2,500.

1. Click onto the MASTER worksheet. The filter is still on. The down arrow in cell B1 is blue, indicating that the filter is being applied to this field.

2. Click on the blue down arrow, then select (All) from the top of the list. All the records reappear

3. Click on the down arrow in D1. From the list of CCs, select DOUG

3. Three records for Doug come up. In the third row (June) change DESIGN to MARKTG, and change 1917 to 2500.

4. Now click onto the PIVOT worksheet. Right click onto the pivot table, then: Refresh Data.

Doug is added to the Marketing budget for period 3. The totals are all recalculated.

OK. It's time to take break. Save your work via: File'Save. Then relax.

END