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

Pivot Tip 22 - Format drill-down worksheets with the Format Painter

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 'pivot practice' database.

One of the great features of pivot tables is drill-down ' you can double click on any total and Excel creates a new worksheet showing all the transactions that make up that total.

But unfortunately the new worksheet is often poorly formatted. In particular, a column of numbers will often show some numbers with no decimals, some with 1 decimal, some with 2, 3 or 4, making it virtually unreadable.

Format drill down worksheets quickly by copying over the formats from the original source database.

Go to the source database; click at top left to highlight the entire worksheet; click onto the Format painter icon; click back onto the new worksheet; click onto the top left of the sheet. All the columns of the worksheet should now be formatted as in the source database.

Practice Session ' using the Format Painter to format a new worksheet
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. [If you HAVEN'T made your own copy, you should do so now. Follow the instructions at the end of this article.]

Hopefully, you have a pivot worksheet already. Amend it as follows. [If you don't have a pivot worksheet, create a new pivot table.]

ROW = PRGRP, and below that, PRODUCT,
COLUMN = Years, DATE (grouped into months, Apr, May, Jun)
DATA = Sum of NET

[For this exercise the pivot table doesn't have to have to be broken down by months. You can just show Total sales if you like. If you would like to show the sales for April, May and June, but have forgotten how, refresh your memory by looking at Pivot Tip 2 - How to Group Totals by Month]

The first line of the pivot worksheet should read:

Monitors Daewoo 23' 0 1200 0 1200 (or just 1200)

[There should be subtotal lines for Monitors, Printers and Servers. If there aren't, double left click on PRGRP in A5. The Pivot Table field box appears. Change Subtotals to Automatic.]

Drill Down on Total Sales of Servers
Total sales of Servers were £22,075. You would like to investigate this total by looking at the underlying transactions.

Double click on the total 22075 in cell C21 or F21.

A new worksheet is created that lists all the individual Servers sales (column F).

This practice drill-down sheet is pretty tidy, but in real life they are usually very messy. Autoformat them straight away as follows (note that columns H, I and J have no decimals)

At the bottom, click onto the source database ' the SHORTworksheet.

Highlight the entire worksheet by clicking on the top left tile (above the 1, to left of the A)

Now click the Format Painter icon ' (looks like a paintbrush). Moving lines appear.

At the bottom of the screen, click back onto the new worksheet.

Left click onto the top left tile above 1, left of A (a paintbrush appears next to it).

The paintbrush disappears and the number columns H, I and J now show 2 places of decimals, (the same format as the SHORT worksheet.)

All columns of the worksheet have been reformatted in one go.

Finally, the number you originally double clicked on was 22075 ' total Server sales. Just to check that these are all the transactions, click onto the top of column I ' Net , to highlight it. At the bottom of the screen Excel shows the column total. It is 22075. Fine.

END OF SESSION. Do not 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 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:

  • 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 (1)

    Please login or register to join the discussion.

    avatar
    By sasasaassa
    24th Jul 2009 22:22

    Painters have lost their values because of the latest painting software that has revolutionized this field. Everyone now can easily paint whatever he/she wants. That's the reason why painters have lost their value but I still love San Diego Painter a lot. They are real painters.

    Thanks (0)