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

Pivot Tip 4 - Drill down on your data via Hide/Show Detail. by David Carter

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

David Carter's series of Five Minute tips aims to develop your skill at using Excel pivot tables. If you want to test the tip for yourself, David takes you through a worked example on the practice database.

Pivot Tip #4 ' Use Hide Detail and Show Detail to drill down on your data

A pivot table can summarise thousands of records into just a couple of lines. To drill up or down between totals and transactions use the Hide Detail and Show Detail options from the pivot table menu. Or better still, just double left click onto the cell itself.

Practice Session
In the previous sessions you have been asked to save the practice database pivot_practice.xls on your own machine. 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 pivot table layout as follows:
Row = PRODUCT, Column = Years, DATE, Page = CUSTNAME, Date = Sum of NET

1. First, add Product Group to the pivot table
Right click to bring up the pivot table menu. Select Pivot Table Wizard-Layout

Drag and drop PRGRP above PRODUCT in the Row area. OK - then Finish on the Wizard dialouge box.

The pivot table is recalculated. The products are now sorted and subtotalled by Product Group.

2. Using the Hide Detail/Show Detail commands
Hide Detail/Show Detail will expand and collapse the pivot table to different levels. So:

Right click on the grey PRGRP field button in cell A5. The pivot table menu appears.

Select: Group and Outline'Hide Detail
The pivot table is collapsed to show only Monitors, Printers and Servers.

Now double left click on Printers in cell A7. The screen expands to show the detail for Printers.

Double left click is a keyboard shortcut for Hide/Show detail.

Now double click again on Printers in cell A7. This time the screen collapses. Double left click is a 'toggle' key: if the cell is collapsed, it expands it, if the cell is expanded, it collapses it.

3. Drill down on a number to show the detail
In June you sold Monitors to the value of £10,180. You can drill down on any number to see the underlying transactions. Therefore, now double click on 10180 in cell E6.

Excel creates a new worksheet which lists the individual items.

4. A Quick Way of tidying up the new worksheet
This new worksheet is tidy, but in real life they are unformatted and often messy. To tidy the new worksheet quickly, simply copy over all the formatting from the master worksheet as follows:

Click on to the Master worksheet (called "Short", which contains the original 27 rows). Highlight the whole sheet by clicking onto the top left brick next to A and 1. The whole worksheet should be highlighted.

Click onto the Format Painter icon at the top (looks like a paintbrush).

Now go back into the new worksheet. Hopefully, the mouse pointer now has a paintbrush beside it.

Left click on the top left brick between A and 1. The worksheet is reformatted the same as the master database.

It may not look pretty, but what you have done is create a new spreadsheet which contains the items from the master database that made up the June £10,180 subtotal.

Finish and DON'T Save
We don't need the new worksheet so don't Save the work you've just done.

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 press Finish on the Wizard dialogue box.

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:

  • David Carter's Five Minute Pivot Table Tips - index
  • Want to learn about Excel pivot tables? Start here
  • The Excel Compendium - Pivot Tables
  • 100 Best Time-Saving Ways to Use Microsoft Office by Simon Hurst

    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.

  • Tags:

    Replies (0)

    Please login or register to join the discussion.

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