Pivot Tip 5 - How to lay data totals side-by-side. by David Carter
If you put two items into the DATA area, the pivot table defaults to show one item under the other. If you want to see them side by side, drag and drop with the blue brick thingie.
There doesn't seem to be an official name that describes this blue thing. Anyhow, have a practice to see how it works.
In the previous sessions you have been asked to save the practice database pivot_practice.xls on your own machine. If you HAVE done this, open it now and click onto the pivot worksheet.
[If you HAVEN'T made your own copy, you should do so now. Follow the instructions at the end of this article.]
The first line of the pivot worksheet should read:
Compaq 1000mb 2300 2300
If it doesn't, amend the Layout of the pivot table as follows:
Row = PRODUCT, Column = Years, DATE, Page = CUSTNAME, Data = Sum of NET
1. First, add the Quantity into the Data area as follows:
Right click to bring up the pivot table menu. Select Pivot Table Wizard-Layout
In the DATA area, drag and drop QTY above NET.
The pivot table is recalculated. The Quantities sold are now included, but they are displayed above the Net sales value. You would prefer to see them side by side.
If you have several items in the Data area and you want to show them side by side, this will create several columns in your pivot table. But you already have columns for Apr, May and June.
Adding columns for Qty and Net will make the pivot table confusing, so first of all remove Year and DATE as follows:
Drag the grey field buttons Year and DATE from C3 and D3 and drop them onto cell A1 so that they become Page fields.
Only three columns now remain in the pivot table ' PRODUCT, the two 'Sum of' fields, and TOTAL.
2. Using the Blue Brick Thingie
Depress the left-hand mouse button while the cursor hoavers over the grey Data field button in cell D5. Drag it down a few millimetres until you see a horizontal 'brick' and below it, an icon with a slab of blue to the left.
Keeping your eye on the slab of blue, drag the brick up and to the right. When the slab of blue changes to horizontal, release the mouse key.
Sum of QTY and Sum of NET now appear side by side.
This thing with the blue represents a pivot table. If the blue is vertical down the left, it will drop the item into the ROW area; if it's horizontal across the top, into the COLUMN area; if it's big blue at the bottom, into the DATA area.
[NB: If you are using Excel 97, there's no blue. Instead the slab is simply vertical or horizontal]
3. Getting back the monthly totals for Apr, May, June
We'll put the April, May and June totals back into the pivot table by putting them into the ROW area.
Right click on the pivot table for the menu. Choose Pivot Table Wizard'Layout.
Move the DATE field out of the PAGE area and into the ROW area underneath PRODUCT. OK
PRODUCT is subtotalling. To remove it, double click on the PRODUCT field button in A6. Change Subtotals from 'Automatic' to 'None' (as described in Pivot Tip 1).
The totals are now expanded into monthly figures for Apr, May and June.
To show the grand totals only, use Hide Detail/Show Detail (as described in Pivot Tip 4). So:
Right click on PRODUCT in A6. From the menu: Wizard ' Group and Show Detail ' Hide Detail.
This collapses the totals up to Year to Date totals.
To see the monthly sales figures of the individual items, simply double left click on the second item Compaq H16 250mb.
The monthly figures appear. To remove them, double click on the cell once again.
We don't wish to save any of this. Close the workbook, but don't Save.
Appendix: How to create the pivot worksheet
You need to have your own copy of the pivot_practice.xls workbook; otherwise you will have to begin each session by re-creating the pivot table from scratch.
The first articles in the series Tip 1 and Tip 2 and Tip 3 explained how to create the pivot worksheet. If you are new to pivot tables, it would be best for you to go through these first articles in full. If you are already familiar with pivot tables, create the pivot worksheet now as follows:
Download the Pivot_Practice.xls database
Create the following pivot table:
PRODUCT in the ROW area
CUSTNAME in the PAGE area
NET in the DATA area
DATE in the COLUMN area
OK, then Finish.
Now Group the dates by month as follows:
Right click on the DATE field button in C3. Select: Group and Show Details ' Group.
Months is highlighted as the default. Scroll down to the bottom of the list under Quarters and highlight Years as well. OK.
The pivot table now displays the transactions grouped by month ' Apr, May, Jun.
The first line of the pivot table should read:
Compaq 1000mb 2300 2300
Change the name of the worksheet from sheet1 to pivot (double left click on the worksheet tab at the bottom)
There should now be two worksheets ' pivot and master.
Now save the Pivot Practice workbook on your own machine. Future articles in this series will use the pivot worksheet as their starting point.
Related material in ExcelZone
For more than four years, David Carter and AccountingWEB members have built up a massive stockpile of material on Excel and pivot tables. To delve more deeply into the subject, see:
Subscribe to the ExcelZone newswire
To keep up with David Carter's Five Minute tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.