WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Pivot Table Add Column

Pivot Table Add Column

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.”

DataProfit 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.

You can download this Pivot Table Add Column Excel Template here – Pivot Table Add Column Excel Template

For example, look at the below pivot table.

Pivot table add Column Example 1

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.”

Pivot table add Column Example 1-1

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.

Pivot table add Column Example 1-2

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.”

Pivot Tbale analyze Fields

Step 3: From the drop-down list, choose “Calculated Field.”

Pivot table add Column Example 1-3

Step 4: This will bring up below the insert calculated field window for us.

Pivot table add Column Example 1-4

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.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Since we are creating the column as “Profit,” give the same name.

Pivot table add Column Example 1-5

Step 5: Now, to arrive, the Profit formula is “Sales – Cost,” so use the existing fields and frame the formula.

Example 1-6 (profit)

Step 6: Click on “Ok” or “Add” the new calculated column has been automatically inserted into the pivot table.

Pivot table add Column Example 1-7

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.

Example 1-8 (profit %)

Click on “Ok,” and we will have a “Profit %” ready.

Pivot table add Column Example 1-9

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.

Example 1-10 (Bonus)

Click on “Ok,” and we will have a new column as “Bonus” in the pivot table.

Pivot table add Column Example 1-11

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?
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Pivot Table Add Column Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More