Close

January 28, 2013

Pivot Table in MS Excel 2013

Pivot Table helps us to visualize our data for analysis. Pivot table manipulates, sorts, counts data in a separate table. Pivot Table/Chart allows users to visualize and model their data in three dimensional views.

It helps users to analysis and reporting by creating Charts and Graphs. But the disadvantages is we cannot handle too much of data and searching/sorting of data takes much time. To handle large amount of data, Microsoft released a tool called Power Pivot. The Father of Pivot Tables is PITO SALAS. 

To Insert Pivot Table in MS Excel 2013, you require valid set of data as shown below.

[auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”Pivot Table in MS Excel 2013 – QAInsights.PNG” alt=”Pivot Table in MS Excel 2013 – QAInsights” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content/uploads/2013/01/Pivot-Table-in-MS-Excel-2013.png[/auto_thumb]

Say, from above data, we want to visualize from various perspectives i.e. Count of Students, Count of Language paper alone, and Count of Total Marks of all Students et al. It will be time consuming if we do it manually.

Go to Insert tab and select Pivot Table as shown below.

[auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”Pivot Table in MS Excel 2013 – Insert Pivot Table in MS Excel 2013 – QAInsights” alt=”Pivot Table in MS Excel 2013 – Insert Pivot Table in MS Excel 2013 – QAInsights” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content/uploads/2013/01/Pivot-Table-in-MS-Excel-2013-Insert-Pivot-Table-in-MS-Excel-2013-QAInsights.png[/auto_thumb]
By selecting Pivot table you will get Create Pivot Table window as shown below. To create a Pivot table for an external source you can select the appropriate option in this case we already have data in worksheet so select ‘Select a table or range’ option then click on the button next to text box.

[auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”Pivot Table in MS Excel 2013 – Create Pivot Table in MS Excel 2013 – QAInsights” alt=”Pivot Table in MS Excel 2013 – Create Pivot Table in MS Excel 2013 – QAInsights” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content/uploads/2013/01/Pivot-Table-in-MS-Excel-2013-Create-Pivot-Table-in-MS-Excel-2013-QAInsights.png[/auto_thumb]

Once you click on the button you can select the range to create Pivot Table i.e. from C2 to I7 as shown below.

[auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”Pivot Table in MS Excel 2013 – Create Pivot Table Dialog Box in MS Excel 2013 – QAInsights” alt=”Pivot Table in MS Excel 2013 – Create Pivot Table Dialog Box in MS Excel 2013 – QAInsights” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content/uploads/2013/01/Pivot-Table-in-MS-Excel-2013-Create-Pivot-Table-Dialog-Box-in-MS-Excel-2013-QAInsights.png[/auto_thumb]

Now select Existing location option under Choose where you want the PivotTable report to be place and specify the exact location. Or if you want to create in a new worksheet, select New Worksheet and then Click on OK.

Now you can see one more sheet gets created as shown below which contains Pivot Table Field List at right side and Pivot Table at left side.

[auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”Pivot Table in MS Excel 2013 – Pivot Table Snapshot in MS Excel 2013 – QAInsights” alt=”Pivot Table in MS Excel 2013 – Pivot Table Snapshot in MS Excel 2013 – QAInsights” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content/uploads/2013/01/Pivot-Table-in-MS-Excel-2013-Pivot-Table-Snapshot-in-MS-Excel-2013-QAInsights.png[/auto_thumb]
Now you have to decide how you want to visualize your data. In this case I am going to visualize as follows. In Pivot Table Field List I am selecting all the parameters, once you select corresponding parameters will gets added up at left side as shown below.

[auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”Pivot Table in MS Excel 2013 – Pivot Table Snapshot 2 in MS Excel 2013 – QAInsights” alt=”Pivot Table in MS Excel 2013 – Pivot Table Snapshot 2 in MS Excel 2013 – QAInsights” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content/uploads/2013/01/Pivot-Table-in-MS-Excel-2013-Pivot-Table-Snapshot-2-in-MS-Excel-2013-QAInsights.png[/auto_thumb]

You can customize your view by drag and drop of the fields at bottom right corner of Pivot Table Field List as shown below. According to the selection of parameters you can see the respective Pivot table at left side of the excel sheet.

[auto_thumb width=”150″ height=”150″ link=”” lightbox=”true” align=”center” title=”Pivot Table in MS Excel 2013 – Pivot Table Fields in MS Excel 2013 – QAInsights” alt=”Pivot Table in MS Excel 2013 – Pivot Table Fields in MS Excel 2013 – QAInsights” iframe=”false” frame=”true” crop=”true”]http://qainsights.com/wp-content/uploads/2013/01/Pivot-Table-in-MS-Excel-2013-Pivot-Table-Fields-in-MS-Excel-2013-QAInsights.png[/auto_thumb]
If you change the source data, corresponding data will gets reflects in Pivot table. So, no need to generate the pivot table repeatedly. You can also apply Filter too if you want to filter based on your conditions.

Thanks for visiting QAInsights!

We’re always posting interesting articles on QAInsights. I request you to subscribe so you don’t miss out anything.

Subscribe to our QAInsights YouTube Channel. Subscribe our free weekly newsletter or Google feeds.

About NaveenKumar Namachivayam

Hi there! is a passionate and experienced Performance Test Analyst based in Chicago, USA with skill sets in Manual Testing, Test Automation, Performance Testing, and Test Estimation. He loves to learn and experiment new trends and models in Software Testing. Please stop by at his personal blogs: Excel Blog, DealsBrook, NaveenKumarN.in, and Affiliate Insights blog and Software Testing Memes.

Leave a Reply

Your email address will not be published. Required fields are marked *