Est. 2002
Tell Me More
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
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.
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
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.
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.
Sign up to receive our monthly newsletter including special promotions, hints & tips, and the latest Computer Tutoring news!
At Computer Tutoring, we prioritise your privacy and only collect the necessary information to provide our services. We will only share the personal data you provide in a form if you have opted in for those services. Our website also uses Google Analytics. You have the right to access, amend, or request deletion of your personal data by contacting us at info@computertutoring.co.uk.
For more details, please read our full Privacy Policy.
