Creating a New Calculation in an Excel Pivot Table

Ever since I published the article on how to display both values and percentages in a pivot table & How to Get Running Totals in Pivot Tables, I am getting more and more emails asking about the other features and tricks within Pivot Tables in Microsoft Excel.

One such useful feature is the ability to add a new calculated field, which is not even present in the base data set, but can be derived from the existing fields.

We can call such a field as Adding a New Calculated Field in the Pivot Table. Once added, it is available to you in a pivot table as a regular field, and can be summed, counted, averaged, etc. with ease.

To Add a Calculated Field, go to the Pivot Table Options Menu, and Find the “Fields, Items & Sets” drop down menu in the ribbon.

Calculated Field Menu in Excel 2010, Excel 2013
Calculated Field Menu in Excel 2010, Excel 2013

Click and select the Calculated Field from the drop down menu.

Type a field name in the Name: field.

We will key in the formula in the next blank cell. Do note that Excel already provides you with a Zero there. You need to remove the Zero first. And then begin to type your formula.

Calculated Field in Excel Pivot Table
Calculated Field in Excel – Popup Screen

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn theĀ  Pivot Table techniques in our
Pivot Table Masterclass Training
!


All the existing Fields are listed in the Field List below the formula.

Let’s say we have to calculate Bonus, which is 10% of the Sales Commission.

So the field Name we would give would be called Bonus.

The Formula would be =Sales Comm * 0.10

Click OK to close this popup menu.

You will now notice that a new field called Bonus is active in the Field List.

Double Click or Drag it to the Values Section of the Pivot Table Layout.

Voila! You have now created an new calculation called Bonus, based on existing values within the Pivot table, and it is now available for analysis in the Pivot Table.

You can use this simple technique to create as many pivot table fields, and enhance your analysis.

If you would like more such tips, you may subscribe to the ExcelChamp Excel Tips Newsletter or Excel Tips RSS Feed.

Cheers,
Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

PS: If you liked the tip, please do Like us, Stumble Upon on the Left Socials Bar, or Digg it!

Different Grouping For Multiple Pivot Tables Based on Same DataSet

One of the key features of Pivot Tables is to summarize the information quickly. Another great advantage is its ability to look at percentages – percent of total, percent of grand total, percent of row total, running total in etc.

Yet another fantastic feature is the ability to group data in Excel – either by existing columns, or by creating your own custom logic. Data can be grouped by Text Columns or even Date Columns. And the result is instantly summarized. Amazing stuff!

All is good so far… The problem crops up when you have created some grouping, and then decide to build another pivot table to get another view of the same data, while keeping the original pivot table in place.

The second pivot table automatically groups the data based on the first pivot’s grouping. And if you change the grouping on the second pivot, the first pivot table changes too.. Bo hoo hoo šŸ™

This can be frustrating and sometimes difficult to troubleshoot or fix.

Why does a Pivot Table share its Grouping with another Pivot Table?

That’s because both the pivot tables are sharing the same pivot cache. To understand better, when Excel creates a pivot table, it makes a copy of the entire source data, and creates a temporary pivot cache in the memory. This duplicated cache is now stored with the Excel file, doubling its size. If your source data was huge, the excel file soon soars in size too.

To save hard drive space and memory, when the second pivot table was created, it used the same cache as the first pivot table. This makes sense, but then since the cache is shared, change in the cache for one pivot table affects the other pivot table too.

The solution is to have a separate pivot cache for the second pivot table.

How to Create a Separate Cache for the Second Pivot Table?

Although this method will use up more memory, it is a good solution, works well, and hardly takes any time to implement.

Select the source data, go to the Formulas tab, and click on Define Name button.

Key in a unique name in the popup. Let’s say you call this DataSet1. This creates a Unique Named Range.

Now click on the Define Name button once more, and create another name for the same data set. Let’s call this DataSet2.

Create Pivot Tables with Unique Data Sets

Now create the first pivot table based on the first Data set (DataSet1). Group on whatever fields you want. Once you are happy with the result, do the same thing for the second pivot table.

Just remember to use the second data set for the second pivot table (DataSet2). Group the data on a different field.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn theĀ  Pivot Table techniques in our
Pivot Table Masterclass Training
!


You will notice that the grouping of the second table stays, and it does not alter the grouping order of the first pivot table. That’s because we are using different defined names.

Thus, Excel creates two different pivot caches, and even though both refer to the same data set, it is transparent to Excel, and is of no consequence.

Now you can enjoy the benefits of two different views, one with one set of grouping, and another with another set of grouping.

Although this method inflates the size of the file, it is a quick and dirty method, that works well. Try it out… and let me know how it goes…

Cheers,Vinai Prakash,
Founder of ExcelChamp.Net

Additional Resources:
If you found this tip useful, you may want to subscribe to the ExcelChamp Weekly Excel Tips Newsletter.

And check out these cool Excel Tips too…

Cheers – Vinai

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

How To Get Running Totals In a Pivot Table

Pivot Tables are great in summarizing data from thousands of rows in just seconds. You can get Sum, Count, Average of numbers from any grouping in a Pivot table.

But the one thing that you need at times is a running total in a column. For example, in January, you sold 100 cans, and in February you sold 120 cans. So the total cans you sold till Feb is 100+120=220. This is a running total up to the month of February.

If you want Excel to do this, you can write a formula in the data cells. But to get this thing done in a Pivot Table, you need to use a different way. And it is already pre-built inĀ  Excel Pivot Tables, starting from Microsoft Excel 1995.

Raw data for Pivot Table
Raw data for Pivot Table

All you need to do is to click on the Data Field in the Pivot Table Field List, and a popup comes up.

Choose the last option – Value Field Settings.

Move to the Second Tab – Show Values As.

Then select “Running Total in” in the drop-down. Select a base field. It must be a text field, like Product, Category, Region etc.

Show Values As - Running Total In
Show Values As – Running Total In

Voila, you get a running total in the data field inside the pivot table. And all you did was to select the correct option. No formulas to write… no calculations to make or check. Isn’t it wonderful?

Running Totals Calculated
Running Totals Calculated

Using % Running Total In

Similarly, if you choose % Running Total In as the calculation, the running total numbers would be displayed as a percentage of the total. Giving you a much nicer perspective.

Running total Percentage in Excel Pivot
Percentage of Running total

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn theĀ  Pivot Table techniques in our
Pivot Table Masterclass Training
!


If you’d like to learn more Excel Pivot Table Tricks, here are some additional articles:

Questions About Pivot Tables in Excel?

If you have any questions regarding Pivot Tables in Excel, simply post a comment below and I’ll help you with it. All the best!

And Subscribe to our Excel Tips Newsletter, so we can send you more such Pivot Table Techniques by email. Only useful stuff, without the hype. And we won’t spam you. So don’t worry. Just subscribe.

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net

Vinai conducts 1-2 day seminars and Training Sessions on Microsoft Excel all over the world, helping companies maximize their productivity in using Microsoft Excel. If you would like to find out more, simply contact us.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!