You can convert a huge table of data into an attractively printed report using a pivot table. The following figure shows a small portion of a pivot table. This pivot table is created from a data table that has 63530 number of rows of data. This data is about my digital music collection, and each row contains information about a music file: the genre, the artist name, the track name, the album name, the duration, and the file size.
The pivot table report created from this data is more or less 160 pages long, and it took about five-seven minutes to set up this pivot table.
Here’s a quick process step of how I created this report:
- I have selected a cell in the data table and chosen Insert ➪ Tables ➪ PivotTable. The Create PivotTable dialog box has appeared.
- I clicked OK to accept the default settings.
- In the new worksheet, I used the PivotTable Fields task pane and dragged the Genre, Artist, and Album fields to the Rows area.
- I dragged the Title, Size, and Duration fields to the Values area.
- I used the Value Field Settings dialog box to summarize Size as Sum, and Duration as Sum.
- I wanted to display Size column in megabytes (not kilobytes). So I formatted the column using this custom number format: ###,###, “MB”;;.
- I wanted to display Duration column as hours, minutes, and seconds, so I formatted this column using this custom number format: [h]:mm:ss;;
- I have edited the column headings. For example, I replaced Sum of Size as Track Size and Sum of Duration as Duration of track.
- I have chosen Show in Outline Form using this command PivotTable Tools ➪ Design ➪ Layout ➪ Report Layout ➪ Show in Outline Form.
- I turned off the +/- buttons by choosing PivotTable Tools ➪ Analyze ➪ Show ➪ +/– Buttons.
- I applied a built-in style by choosing PivotTable Tools ➪ Design ➪ PivotTable Styles.
- I went into Page Layout view choosing VIEW ➪ Workbook Views ➪ Page Layout and adjusted the column widths so that the report would fit horizontally on the page.
- I added two Slicers (Genre and Artist) to use this pivot table easily to display specific music types.
Read More: Creating an Excel Pivot Table Manually
Note that you can expand and contract fields. For example, to hide the albums under each artist, select any artist and choose PivotTable Tools ➪ Analyze ➪ Active Field ➪ Collapse Field. To hide all the artist names (and just display the genres), select any genre cell and choose PivotTable Tools ➪ Analyze ➪ Active Field ➪ Collapse Field. Use the Expand Field command to unhide hidden fields.