Pivot Table Add Column

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 ExcelData Table In ExcelA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section.read more” and “Pivot TablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it. read more.”

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 tableCalculated Fields In Pivot TablePivot table calculated fields are formulas with reference to other fields, and calculated values refer to other values within a specific pivot field.read more” & “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.

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?