Excel 2010 Bible

Chapter 35: Analyzing Data with Pivot Tables

743

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

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: Track, Size, and Duration.


5. I used the Data Field Settings dialog box to summarize Track 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:


8. I edited the column headings. For example, I replaced Count of Track with Tracks.


9. I changed the layout to outline format by choosing PivotTable Tools ➪ Design ➪
   Layout ➪ Report Layout ➪ Show In Outline Form.


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


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


12. I displayed a blank row after each artist’s section by choosing PivotTable Tools ➪
    Design ➪ Layout ➪ Blank Rows ➪ Insert Blank Line after Each Item.


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