Excel Pivot Table Add Column
Some of the advanced pivot table techniques are essential to go a long way in our careers. There is a situation where we need to think out of the box to overcome the challenge in front of us. Usually, we use columns of the data table and build report but often times you might have seen extract column which is not there in the data table but exists in pivot table fields. So this is because of the “Calculated Field” in Pivot Table and in this article we will show you how to add a new column in the pivot table using the above options.
Let me tell you the scenario which I have gone through, look at the below image of “Data Table in Excel” and “Pivot Table”.
In the “Data Table, “ we have only “Sales” & “Cost” columns but when we look at the pivot table we have “Profit” as the extra column, so as a new learner I was just wondering where did this new column come from and spent good amount of days to figure this out.
It is very difficult to know about this especially you are learning on your own, these new columns in the pivot table come because of “Calculated Fields in pivot table” & “Calculated Items”.
What is Calculated Field in Pivot Table?
A Calculated Field is a custom column or field created by using existing columns of pivot table by applying formulas, so this calculated field is a virtual column for pivot table which does not exist in the actual data table.
For an example look at the below pivot table.
In the above table, we don’t have a “Profit” column, usually other than pivot tables we simply insert a new column arrive “Profit” by deducting “Cost” from “Sales”.
For the pivot table, only in the next column we have inserted this formula and arrived profit column but the problem with this is, “Profit” column is not part of the Pivot Table so any changes to the fields of pivot table will show error values.
For example, we are filtering the first 3 months.
Look at the Profit column only for the first three columns showing the values for other cells it is still showing zero values, so to avoid all these we create a new virtual column through “Calculated Field”.
Example #1
Using the same formula we will create a new column.
Step 1: Place a cursor inside the pivot table to populate “Analyze & Design” tabs in the ribbon.
Step 2: Go to “Analyze” and click on “Fields, Items & Sets”.
Step 3: From the drop-down list choose “Calculated Field”.
Step 4: This will bring up below the insert calculated field window for us.
In the above window, we need to give two things to create a calculated field “Name & Formula”. Name will be given for the field and formula is used to create the field.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
Since we are creating the column as “Profit” give the same name.
Step 5: Now to arrive the Profit formula is “Sales – Cost”, so use the existing fields and frame the formula.
Step 6: Click on “Ok” or “Add”, the new calculated column has been automatically inserted to the pivot table.
There we have the new virtual column which is not there in the actual data table.
Example #2
With the continuation of the above example now we will insert a “Profit %” column. So arrive the “Profit %” formula would be “Profit / Sales”.
Follow the same steps above and frame the formulas as shown below.
Click on “Ok” and we will have a “Profit %” ready.
Note: Change the “Profit %” column number format to “%”.
Example #3
Now for the same pivot table above we will create some advanced calculated field using the IF statement.
To arrive BONUS column calculation would be as follow.
If the sales value is >4000 then a bonus is 1% of sales, otherwise, it would be 0.5%.
So follow the above steps as shown above and insert the formula as shown below.
Click on “Ok” and we will have a new column as “Bonus” in the pivot table.
Like this, we can insert columns to the pivot table using “Calculated Field”.
Things to Remember
- If you don’t see the field in the data table then it must be a calculated field only.
- Calculated Field can be arrived by using formulas and existing fields of the data table.
Recommended Articles
This has been a guide to the pivot table to add a column. Here we discuss how to add a new column in the pivot table using the calculated fields along with some practical examples and downloadable excel template. You may learn more about excel from the following articles –
- Pivot Table Group by Month
- Pivot Chart in Excel
- Using GetPivotData in Excel
- How to Sort in Pivot Table?
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
