Excel Pivot Table Reports: The What, the How and Some Tips

When you maintain an Excel sheet, I am sure you do not feed in data just for the sake of storage. Most of the times you intend to analyze and

organize that data

, right? Now, MS Excel has got a beautiful feature called

Pivot Table

that lets you summarize large tables in the form of a report. Then, without much manual effort and time you can analyze your data. Let us see how.

Here’s a section of sample data sheet that I maintained during a sports event at my office. It holds registration fee for different games, collected from different floors of the office building, and by different people.

I would like to get the grand total, meaning the total for each floor, for each event and the total money collected by each SPOC. The solution is to create a pivot table. A sample for the above data is shown below.

Creating a Pivot Table

The minimum effort it takes to generate such a table should not make you undermine the immense potential of this tool.

The tutorial is based on MS Excel 2013. The steps would be more or less same on other versions as well.

Step 1: Select the data/cells on which you would like to create a pivot table.

Step 2: Navigate to Insert tab and click on the tool that reads PivotTable, towards the extreme left of the ribbon.

Step 3: The Create Pivot Table window will show up. It would show the selected data range. If you wish you can change it here or choose an external data source. Besides, you have an option to create the table on the same sheet or a new one (prefer a new one to avoid errors).

Step 4: This will activate the Pivot Table panel on the right. Select the fields on which you want to base your analysis.

Step 5: Now, you will start getting a feel of what Pivot Tables are. Try and choose different combinations with different row and column labels to get a detailed understanding.

Here, check out another report against the selected fields and row/column labels respectively. Try and compare the images (left and right).

Let me tell you frankly that you would understand this feature in detail only by usage and practice. Reading tutorial just once isn’t enough.

Few Handy Tips

• Choose your row and column labels carefully because they decide how the analyzed data will be arranged.
• You can move fields between row and column labels through drag and drop.
• For a field, the context menu shows you an option called Value Field Setting that allows you to choose the nature of the report or field data.
• You may apply filter to the result in order to get a customized report.

Conclusion

This tutorial is meant to set forward the basic technique to create a pivot table and few rules and standards that can get you started. There’s a lot more to it though. You will learn the ropes only by creating a lot of them and playing around with the options.