Sunday, May 11, 2014

How to create Pivot Table? A tutorial with complete pictorial example.

Pivot is a very useful tool in terms of data analysis and collectively looking into data in a customized way.  If you know how to use the filter and sorting option you can easily understand pivot. By filter option you look data for single data point for particular type of data but by using PivotTable you can create customized table for all the data point containing all type of available data in the worksheet. 

Disadvantage of filtering or sorting is that you can analyse data in singly particular way, but in pivot you can tabulate data for all the rows available collectively for the selected parameters.
Now let's take an example by using the below dummy data for creating Pivot Table.
Just type the data as shown below in the worksheet. (I’m using here Microsoft Excel 2013)

In PivotTable there are two options Pivot Table and Pivot Chart. At first let’s start with Pivot Table. Go to Insert tab, at very first option you can find PivotTable.

Select the table/range A1:F21 for input and as the data is small I would prefer select Existing Worksheet and enter Location for output, leave Add this data to the Data Model unchecked. Now this will build a box at H1 area and PivotTable Fields will be visible at the right side. (as shown below)

Check Data Analyst or drag Data Analyst to ROWS box down. Check Productivity, Quality, Production Hours and Date or drag down to VALUE box. Make sure Date is in VALUE box.

Count of Date shows number of working days for each Data Analyst. You can see here Sum of Quality but actually we wanted to see the average to Quality not Sum and in percentage data form, for that we need to select correct data setting.
At VALUE box click on the dropdown for Sum of Quality and click on the Value Field Settings….

Select average from the list and click on the Number Format (button) and select Percentage number format.

Now the table will look like below, this is PivotTable.

Whenever you update any data in your worksheet data, you don't have to create a fresh PivotTabale just right click anywhere on the PivotTable and select Refresh and get the updated data in your PivotTable.