Add Calculated Field to Pivot Table

Why Would you Add a Calculated Field to a Pivot Table?

When you add Calculated Fields to Pivot Tables you are able to add formulas to Pivot Tables. That's Right! You now longer have to create a formula in your source data to only use that formula for one Pivot Table.

Download: Starter file / Completed File

What is a Calculated Field?

A Pivot Table Calculated Field is a way of writing formulas within Pivot Tables. This is because it's not possible to write formulas as you normally would within Pivot Tables. Hence, if you write something along the lines of =A2+B2 you'll get an error. This is because the structure of Pivot Tables is quite difference from the normal way a spreadsheet is arranged. As a result you need to use Calculated Fields, which are designed for use within a Pivot Table, should you want to use Pivot Table data in formulas.

So if you want to calculated percentage of two columns in a Pivot Table or add a formula to a Pivot Table to do something else, Calculated Fields will be the answer you're looking for.

How to Add a Calculated Field to a Pivot Table?

Adding a Calculated Field - 2:03

First, add all the normal columns that you would like displayed in the Pivot Table. In other words the fields that are not calculated. You don't have to include the fields that you will use in your Pivot Table formula.

Next, click the PivotTable Analyze. You can find this on the ribbon at the top of the screen.

After that, in the Calculations section, click on the Fields, Items and Sets drop down menu.

Select Calculation Field

Using a Formula to Calculated Profit

The next thing we need to do is to enter a formula that will calculate the amount of profit. To do this we will subtract the expenses from the amount received. As a result we will see how much money we really made.

You should now see the Insert Calculated Field box.

In the Name Box type Profit. Then, in the Formula box enter following formula.

=Received+Expense

You can double click on the name of the column header, listed in the fields section, that you want to add to the formula.

Click Add to add the Calculated Field to the Pivot Table field list.

Now, look in the PivotTable Fields box and you will see that the Profit field is added to the Pivot Table Fields list. Also, the Profit field has been added to the Pivot Table.

Then, if necessary, right click on the Sum of Profit in the Pivot Table and select Number Formatting. Then, change the format of the Profit field in the Pivot Table to Accounting.

Calculate Percentage of Two Columns in a Pivot Table

Calculated Percentage of Profit - 3:27

You may already know how to calculate the percentage of two columns but how to calculated percentage of two columns in a Pivot Table? You again would use the Pivot Table Calculated Field feature to create the formula you need.

Click any cell in the Pivot Table that you want to add the Calculated Field to.

Click Fields, Items & Sets from the Ribbon, select Insert Calculated Field.

In the Insert Calculated Field box enter Profit % in the name box and enter =Profit/Received

Finally, Right Click in the Pivot Table anywhere in the Sum of Profit % Column.

Choose Number Formatting.

Double click Percentage.

Change the Headings at the top to something a little more appetising, change colours using the Design tab and your Pivot Table should look beautiful.

If you haven't already looked at the financial year tutorial then I really encourage you to do so.

This tutorial is one of a series of Accounts Tutorials that you are encouraged to follow. As a result you will learn everything from creating a basic accounts worksheet to a Pivot Table. Also, you will learn many everyday Excel hints and tips.

Other Excel Accounts Tutorials

Super Easy Accounts in Excel?

How to Make Your Spreadsheet Look Good?

Calculate VAT in Excel

Excel VAT Formula

Money Made Last Month

Pivot Tables for Beginners

Excel Financial Year Formula

Add Calculated Field to Pivot Table

Pivot Table Conditional Formatting

Excel Summary Sheet

SUMIF Excel Multiple Sheets

Clean up Messy Excel Data