Pivot Table

This blog is intended to provide you the knowledge about most important and widely used feature known as Pivot Table which is a table of statistics that summarizes the data of a more extensive table. This summary might include sums, averages, or other statistics, which the Pivot Table groups together in a meaningful way that can be used for analysis and presentation.

To access PivotTable, following steps are available:

•  Go to Insert ribbon and Choose Pivot Table option.

• Press keyboard shortcut key Alt + N + V.

 

Arguments for creating PivotTable 

• Choose the data that you want to analyze:

It indicates to select the source data table/range which is considered to create the Pivot Table. 

• Choose where you want the PivotTable report to be placed:   

It indicates to select the cell location where the PivotTable is pasted.

             

For Example:     We have the following data for some clients as shown in a following screenshot which we need to analyze.

With this data, we need to present the total WIP (Work in progress) amounts for the given clients along with their verticals from the table. We can easily meet this requirement with the creation of Pivot Table by applying the following setups.

• Go to the Insert menu and click on PivotTable option. The following dialog box will be appeared. 

      

• In the above showing dialog box, we will meet the mandatory criteria as follows:

• Choose the data that you want to analyze:

Select the data/table range which is used for analysis.

• Choose where you want the PivotTable report to be placed:   

Here, we have the 2 options i.e New Worksheet or Existing Worksheet. The former is used to if we want to paste the Pivot Table into new worksheet or later is used if we want to paste it to another location but in the same worksheet in which the source date is located. In our example, select it as Existing Worksheet and cell as $I$1.

 

• Once the criteria gets fulfilled, click OK, the introductory table will be appeared onto the selected location and the panel for PivotTable fields will be appeared in right side in worksheet as shown in the following screenshot.

      

• Now, as we need to show the client wise verticals and WIP amounts, first select the field named as ‘Sold-to party name’ and drag the same into the area for Rows in the panel. Client information will get displayed at the Pivot Table location.

      

• As, we need to show the given verticals also with the client, again go to the panel, select the field named as ‘Vertical’ and drag the same to below the field - Sold-to party Name in the area for Rows. Doing so, the client wise verticals will get displayed. Here, important to note that as we have dragged the 2^nd field below the 1^st one, so details will be shown in the same columns (as shown in the following screenshot) which is not presentable. So, to separate this 2 fields into 2 columns, put the cursor at the Pivot Table, go to Design menu, click on the Report Layout option and choose the Show in Tabular form option. Both the fields will be organized into the separate columns.

    

• To show the sum of the WIP amounts for clients of their verticals, select the field ‘Amount (in USD)’ and drag the same in the area of Values. After applying some needed formatting, the final summary will get ready to present.

  

If you have any kind of challenge or you want to learn about any other function/option which is not covered or you want me to add more into the previously covered topic, please feel free to connect with me. I will be happy to answer your queries or take your suggestions.