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! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

1 Comment

      Leave a reply