How to create a pivot table report in Excel

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.

How to create a pivot table report in Excel

Part of a more or less 160-page pivot table report.

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”;;.
How to create a pivot table report in Excel

formatting number format to show kilobytes in megabytes.

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

Download this sample file to practice yourself

Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

1 Comment

      Leave a reply