Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

By using a pivot table, you can convert a huge table of data into an attractive printed report. Figure 18-41 shows a small portion of a pivot table that I created from a table that has 25,664 rows of data. This data happens to be my digital music collection, and each row contains information about a single music file: the genre, artist, album, filename, file size, and duration.

Figure 18-41: A 119-page pivot table report.

The pivot table report created from this data is 119 pages long, and it took about five minutes to set up (and a little longer to fine-tune it).

Here's a quick summary of how I created this report:

1. I selected a cell in the table and chose Insert Tables PivotTable.

2. In the Create PivotTable dialog box, I clicked OK to accept the default settings.

3. In the new worksheet, I used the PivotTable Field List and dragged the following fields to the Row Labels area: Genre, Artist, and Album.

4. I dragged these fields to the Values area: Song, Size, and Duration.

5. I used the Data Field Settings dialog box to summarize Song as Count, Size as Sum, and Duration as Sum.

6. I wanted the information in the Size column to display in megabytes, so I formatted the column using this custom number format:

   ###,###, "Mb";;

7. I wanted the information in the Duration column to display as hours, minutes, and seconds, so I formatted the column using this custom number format:

   [h]:mm:ss;;

8. I edited the column headings. For example, I replaced Count of Song with No. Songs.

9. I changed the layout to outline format by choosing PivotTable Tools Design Layout Report Layout.

10. I turned off the field headers by choosing PivotTable Tools Options Show/Hide Show Field Headers.

11. I turned off the buttons by choosing PivotTable Tools Options Show/Hide +/- Buttons.

12. I displayed a blank row after each artist by choosing PivotTable Tools Design Layout Blank Rows.

13. I applied a built-in style by choosing PivotTable Tools Design PivotTable Styles.

14. I increased the font size for the Genre.

15. I went into Page Layout View, and I adjusted the column widths so that the report would fit horizontally on the page.