Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Reporting Challenge Appendix: The Planner pivot table

by
17th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

If you are not sure how to create a pivot table, follow these instructions to create the Planner worksheet.

These instructions apply to Excel 97 [Excel 2000 in square brackets]

1. Click on to the Database worksheet. Click on to cell C10, or any other cell containing data. From the main menu at the top of the screen, select:
Data-Pivot Table Report [Excel 2000 = Pivot Table and Chart Report]

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

2. Step 2 of 4 - The Range box should say: $A$1:$H$26.
Click on Next.

Excel may beep at you and offer a message saying that 'Tour pivot table will use less memory etc.'.
Just click on NO and continue.

Step 3 of 4: The Column-Row-Data box appears. To the right are all the column headings of your spreadsheet.
[Note: if you are using Excel 2000, you must first click on Layout]

4. The list on the right shows all the available fields. Drag JOBNO and drop it into the ROW area.
Now drag and drop the following fields as well:

CLIENT into the ROW area
TITLE into the ROW area
NAME into the ROW area
DEPT into the ROW area
NAME into the DATA area
DATE into the COLUMN area

5. Now click on Finish.
[If you are using Excel 2000, click OK, then Finish]

The Pivot Table is created in a new worksheet.

Tidying up
The pivot table looks untidy, principally because Excel automatically subtotals each Row field.

These subtotals need to be removed. To do this:

1. Double left click on the grey JobNo field button in cell A4 [cell A6 in Excel 2000, I think].

The Pivot Table Field box appears.

2. In the Subtotals section to the left, change Automatic to None. Click OK.

3. In the same way, now remove the subtotals for the Client, Title and Name fields. The pivot table will then look OK.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.