After you create a pivot table, you can enhance the results by writing your own formulas, to create calculated fields and calculated items. e When you’re getting started with formulas, it might not be clear what the difference is. When should you use a calculated field and when should you create a calculated item?
We’ll take a look at a pivot table that uses both types of formulas, and see where and how they work. You can also find more information on each type of formula on my Contextures website:
About Formulas
- Formulas are available only in non-OLAP-based pivot tables
- You can’t create formulas that refer to the pivot table totals or subtotals.
- Formulas can’t refer to worksheet cells by address or by name.
- You can create a list of all the formulas in a pivot table.
About Calculated Items
- A calculated item becomes an item in a pivot field. Its calculation can use the sum of other items in the same field.
- For calculated items, the individual records in the source data are calculated, and then the results are summed.
- Calculated items are listed with other items in the Row Labels or Columns area of the pivot table.
- Calculated items are not listed in the PivotTable Field List.
Warning: If you create a calculated item in a field, you will:
- NOT be able to move the field to the Report Filters area
- NOT be able to add multiple copies of a field to the Values area.
About Calculated Fields
- Use calculated fields to perform calculations on other fields in the pivot table.
- For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.
- Sum is the only function available for a calculated field.
- A calculated field becomes a new field in the pivot table, and its calculation can use the sum of other fields.
- Calculated fields appear with the other value fields in the pivot table. Like other value fields, a calculated field’s name may be preceded by Sum of.
- Calculated fields appear in the PivotTable Field List.
Calculated Item Example
You can create a calculated item when you want to perform calculations on specific items in a pivot field.
In the example shown below, the Order Status field has four items – Backorder, Canceled, Pending and Shipped.
In the Order Status field, you could create a calculated item named Sold, that sums the orders with a status of Shipped, Pending, or Backorder, but doesn’t include Canceled orders.
In the screen shot below, the Sold calculated item is showing, and the Backorder, Pending and Shipped items have been hidden.
When to Use a Calculated Field
Use calculated fields to perform calculations on other fields in the pivot table. In this example, each sales representative receives a 3% bonus if they sold more than 500 units.
The calculated field formula checks the value in the Units field, and calculates the bonus amount based on 3% of the Total field.
Here is the result, with the bonus showing in the applicable rows. The calculated item is used to create a total of all the Sold items (Backordered, Pending and Shipped).
NOTE: A calculated field cannot check the text value of a label, so use filtering or calculated items to show only the applicable items.
_________________
Thank you, Debra.
It is very good and explanatory article.
I discovered for myself using IF in a calculated field’s formula and expect to start using it widely in my pivot tables
Just to note from my practice: I rarely use Calculated Items because they cannot be added into complex pivot tables (in my Excel 2007). Second reason is an impossibility to add Calculated Items to the Grouped fields. Though the idea of Calculated Items is brilliant, these two constraints make them an infrequent instrument 🙁
Thanks Ivan! I agree, those restrictions make them difficult to use. I prefer to add calculations to the source data, if possible, and avoid the pivot table formulas.
Thanks Debra. A nice summary intro to these features. As a BI analyst, I use pivots with both fields and items on externally sourced data. They enable me to create percentage variations and base values for further calculations. I manipulate large data sets where recalculating source data with helper columns is not feasible. Pivots with calc fields and items allows me to perform only on the selected data and so cut down on overheads. I am a real supporter of them.
Thanks LeonK, I can see why pivot formulas are useful to you in those situations.
Awesome post, Debra. Thank you for your help.
These tools are very nice when using a pivot table, however, I face some problems depending on what I exactly need.
For instance, If I have a calculated item which calculates the difference between two columns of the pivot table (two differente years), and I insert a calculated field that is a division between two columns from the data source (example, “Revenue/quantity”), the original calculated field doesn’t work properly. It should show the difference between the columns originated by the calculated field (“Revenue/quantity”) – which is a column labels and have two years selected, but it shows other values that I don’t recognize at a first look.
Has someone ever needed something like this?
Thanks all.
I created a calculated item to show a percentage increase/decrease between two items. The percentages are being summed in the subtotal lines instead of being calculated. Other reporting software I can change subtotals to be calculations, not sums. Is there an option for that in Excel pivot tables?
I don’t have this issue when I create calculated fields. For this particular situation calculated field is not applicable.
I have run into the same problems when I tried to create exactly what you were trying to do.: to figure out ASP for a particular product. Have you been able to resolve it?
You actually can do calculations with totals by using:
SUM(‘Field A’)/SUM(‘Field B’). I’ve used this successfully to work out a ratio of actual cost to planned cost which works when the pivot table collapses to subtotals.
Thanks Jamie, and your formula divides the totals in each row, whether it’s an item row or a subtotal row.
For example — if you sold Bars and Cookies in the East, the pivot table would show this for actual and planned sales, with your formula:
What I meant is that you can’t refer to a Subtotal or Grand Total in the pivot table in a calculation.
For example, this formula won’t work:
East Bar Actual / East Total Actual