Pivots Table in Excel

What is a Pivot?

A pivot table is a tool to create summary reports from data-sets irrespective of their sizes but is helpful even if the data is more than just 20 rows!

Apart from creating summary reports (counting, summing observations for a variable like a salary or diesel consumption at a pump, etc..), it can be used to analyze data – by selecting various combinations of different variables, you can see how your data changes thus making strategies accordingly.

For example: In fraud, we may have millions of customers leading to millions of data, but the fraudsters may just be a few hundred. Using Pivot tables, we can identify the common attributes of fraudsters, thus targeting them without impacting the genuine customers.

Pivot Table Structure

The below are lists down all the columns we have in the dataset for which the Pivot table was inserted. Currently, we have 3 columns in our dataset – Name, Age and Salary

The four pivot table data fields are given below:

Filters: In Filters, we will drag and drop those variables from the above list on which we want to apply a filter.

Columns: In Columns, we will drag and drop that variable for which we want its different values to appear in different columns

Rows: In Rows, we will drag and drop that variable for which we want its different values to appear in different rows.

Values: In Values, we will drag and drop those variables for which we want to summarize the data.

How to create a Pivot table?

There are two ways to create a Pivot Table –

Using Toolbar Method –

1. Go to Insert Tab and click on ‘PivotTable’.

• Excel will prompt you to give the data range for which you want to insert a pivot.

• Once you have given the data-range, it is then up to you whether you want to create the Pivot on the same worksheet or a new sheet – clicking on ‘New Worksheet’ will create the pivot table on the new worksheet and clicking on ‘Existing Worksheet’ will create the pivot table on the current sheet at the location (cell address) you specify by just clicking on the cell.

Not using Toolbar –

• Select the dataset for which you want to insert the pivot table
  • And using your keyboard press the following keys Alt key, D, P and P again (Alt + D + P + P)
  • A pop-up will appear for Pivot Table in which your dataset range would already be present
  • Now you just need to specify the place where you want to insert the pivot table i.e. a ‘New Worksheet’ or on the ‘Existing Worksheet’.

Once either of the above steps has been completed, and you click on ‘Finish’ (and if you had selected on ‘New Worksheet’ which most find more convenient to use), you will get the following format on a new sheet.

Formatting in a Pivot Table

The salary figures in the above pivot table need formatting – we will use the following steps –

• Go to the values field in the pivot table and click on the black arrow head pointing downwards
• A pop-up menu as shown below will appear – click on ‘Value Field Settings’

• Once you have clicked on ‘Value Field Settings’, another pop-up menu will appear which will look like below –

• Before discussing ‘Custom Name’ field, let’s look at ‘Summarize Values By’ – since over here we are summing the salary variable (for numerical values, by default excel will sum the numbers and not count them) – ‘Sum’ has been highlighted, if you want to ‘COUNT’ and not ‘SUM’, you can select ‘Count’ and pivot table will then give you the count
• Now assuming, we just want salary sum here, to change the format, we will click on ‘Number Format’ and a familiar window (shown below) will appear. You can now change the format to ‘Currency’ and add a $ (dollar) sign and click on ‘OK’

• Once you have clicked ‘OK’, if you want you can change the ‘Custom Name’ field to something you like – do note that you cannot use an already existing column name. I have changed the name to ‘Salary Sum’ and then click on ‘OK’

The data will appear as  shown in the image below. You will notice that the cell A4 is displayed as ‘Salary Sum’ and not as ‘Sum of Salary’. Also, there is a $ (dollar) symbol for salary figures.

Slicer

The slicer feature in Microsoft Excel provides visual filters or interactive buttons that display the items that have been chosen within a Pivot Table. In this section, we will check the way by which you can apply filters in a pivot with the help of Excel’s Slicer feature –

• Click anywhere on the Pivot.
• Now go to ‘Analyze’ conceptual bar in the toolbar.
• Now click on ‘Insert Slicer’.

• A prompt will appear asking for which columns you want to insert the slicer for – select all the column and click on ‘OK’

• Here, we have inserted Slicer for 3 columns, ‘Portfolios worked on’, ‘Gender’ and ‘Region’ – as you keep on selecting items in any of these slicers, your Pivot table will keep on getting updated based on these filter values.
• To select multiple values together, click on the ‘Multi-Select’ option, and then you can select multiple values together.

• To remove filters, click on the icon on the top right and all the filters will be removed

Calculated Fields in the Pivot Table

To add calculated fields to the pivot, you will need to –

1. Click on the Pivot Table
2. Click on the ‘Analyze’ bar appearing in the toolbar.

• In the ‘Calculations’ section, click on ‘Fields, Items & Sets’ and then click on ‘Calculated Field’

• On clicking on ‘Calculated Field’, a pop-up will appear as shown below –

• Now let’s suppose I want the average amount of salary being given to an age group based on successful cases completed by the employees in that age group –
• We will click on ‘Calculated Field’
• In the ‘Name’ section, we will mention the name as ‘Avg Commission per Case’
• In the Fields section, because we want to find the average salary per successful case, we will first click on ‘Salary’ field and click on ‘Insert Field’

• Then in the Formula section, we will manually add a slash used for division ‘/’

• And then click on ‘Successful Cases’ and click on ‘Insert Field’ and then click on ‘Add’ and then ‘OK’

A new variable will be created in the Pivot table showing the average commission per successful case – you can then change the format or the name of this field as shown in earlier sections

If you want to add a new calculated field, you can go to the sheet which consists of data and add a new column there using formulas.

If you add a new column after the last column, then to ensure that the new column appears in the pivot table, you will –

• Click anywhere on the pivot table
• Go to ‘Analyze’ tab
• Click on ‘Change Data Source’ – clicking on it, excel will take you to the sheet which consists of the data
• Update the range and click on OK
• And then click on ‘Refresh’ next to ‘Change Data Source’ option and the new column will start appearing in the ‘PivotTable Fields’

If you add a new column between existing columns, you will just need to click on ‘Refresh’ and the new column will start appearing in the ‘PivotTable Fields’