Producing a Report with a Pivot Table

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).

On the CD 

This workbook, named music list .xlsx, is available on the companion CD-ROM.

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.

Note 

Step 14 was actually kind of tricky. I wanted to increase the size of the genre names but leave the subtotals in the same font size. Therefore, I couldn't modify the style for the PivotTable Style I chose. I selected the entire column A and pressed Ctrl+G to bring up the Go To dialog box. I clicked Special to display the Go To Special dialog box. Then I selected the Constants option and clicked OK, which selected only the nonempty cells in column A. I then adjusted the font size for the selected cells.