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 an 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 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 the 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 the “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.” The name will be given for the field, and a 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 into 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, the 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 Fields can be arrived at 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 a 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
