What is Pivot Table in MS Excel?

Ever wondered what we would do when we have chunks of data with thousands of rows and columns to be processed? Finding a minute amount of information from the data would be a hefty and time consuming task, right? Well, Understanding the data itself consumes a lot of time without Pivot tables. Built in formulae, functions, fast and accurate processing of the data makes Pivot Table the most powerful feature of MS Excel.

Pivot table is a data summarization tool which allows you to extract and analyse large data in simplified and detailed way. As said by Microsoft, Pivot tables are applied to summarize, analyze, explore, and present the data into tables and charts. The only thing necessary to create a Pivot Table is “The DATA should be organised in a tabular format without any blank rows”.

Creating a Pivot Table:

We have the data of a Clothing industry. Now, let us find out the Total Quantity for each item for large size according to colors.

Now, click on Pivot Table icon in the Insert tab and then select the range of table and click ok after checking the options.

Now, another sheet is opened with empty Pivot Table and Field List pane as shown. As we need to find out the Total Quantity for each item for large size according to colors. We will drag items and size into rows section and Quantity into Values section.

In order to find out the sum of quantity, we can right click on the Values section and select Value Field Settings. Then select sum from the list given and click OK. Similarly, any other Operations like Product, Maximum, Minimum, Count, Average and Standard Deviation can be calculated.

Now that, output is displayed, it consists of data related to all sizes. But, we’re asked to find out only large sized clothes. So, we’ll apply filter on the Size option as shown and select Large from the list. Filters can also be applied on the text in the cells called as Label Filters while Cells with numbers can be filtered as Value Filters. When these filters are clicked a list of options are displayed with different selections. Depending on our requirement, further filters can be applied on the Pivot Table.

This output table can also be sorted according to the values as Ascending or Descending order or according to the text as in alphabetical order

On right clicking, we get the sorting and filter options. If basic filter is needed, it can be done in the first drop down itself. But, if advanced sorting is needed, we opt for more sort options. It can be observed that sorting can be done on Items or on Colors also with a mere right click on them.

So, now our resultant table is as shown with all its values in Descending Order.

As said by Microsoft, there are more than billion people using MS Excel. Of all the billion people using Excel, only a very few percentage master them. Pivot Tables are easy to learn and are very helpful in making quick decisions. A little Practice and understanding of the procedure makes you the ringmaster of Pivot Tables.

Sources:

https://blogs.technet.microsoft.com/microsoft_blog/2014/02/10/power-bi-for-office-365-empowers-everyone-to-analyze-visualize-and-share-data-in-the-cloud/

https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576?ui=en-US&rs=en-US&ad=US

About Varun:

Varun is a B.Tech (Mechanical). Currently He is working as Analyst Intern with NikhilGuru Consulting Analytics Service LLP (Nikhil Analytics), Bangalore.