PPT Excel: How to add a field in pivot table

This post explains how to add a calculated field in your pivot table with other columns. Most often, after generating your pivot table, you may ask to add more columns for further analysis. Eg. if you have sales data and have to report the growth positive or negative in an additional field in your pivot table.

Suppose you have the below sales data, create a summary table to display the sum of sales by representative field, and need to add a column to highlight the positive or negative growth.

In the data set, you have given four fields Representative, Prod, Sales_H1, and Sales_H2 with 250 records of data.

Add Pivot table in excel

Please follow the below steps to add a pivot table to your worksheet

1. Select your entire data set A1:D251
2. Go to Insert menu
3. Select PivotTable – to open PivotTable from table or range dialog box

4. Make sure the Table/Range is covered your entire data range
5. Select New Worksheet, you can choose Existing worksheet if you need the pivot in the same worksheet
6. Press OK

Now you added a pivot table in a new worksheet, you can see the blank pivot table

Please follow the below steps to add the fields into your pivot and additional calculated fields

1. Drag the Representative field to the Rows pane
2. Drag both Sales_H1 and Sales_H2 fields to the Values pane

3. Click any Pivot cell – you can see the PivotTable Tools menu in the excel ribbon
4. Go to PivotTable Analyze
5. Select Fields, Items, & Sets
6. Choose Calculated Field – to open Insert calculated field dialog box

7. Type calculated field name Growth in the Name input box
8. Type the below formula in the Formula input box

=Sales_H2 -Sales_H1

9. Press Add
10. Press OK

Your final output will look like the below image. Later you can rename headers or format the pivot table to match your report template.

Conclusions

The pivot table is one of the most powerful tools in excel. It helps you to create a summary table and the slicer allows you to filter the data for your reports and analysis.

If you are very familiar with pivot tables you can use the pivot to create a dashboard in excel.