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.
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. (
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….
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.
