Home > Pivot > Calculation > Calculated Item
In addition to the existing items in a pivot table field, you can create one or more calculated items, using custom formulas. Also, see the difference between Calculated Items and Calculated Fields
Introduction
Create a Calculated Item
Delete a Calculated Item Manually
Delete a Calculated Item With a Macro
Modify a Calculated Item
Use Index Numbers in a Calculated Item
Change the Calculated Item Solve OrderVideo
Hide Items Created With Zero ValuesVideo
Get the Free Workbooks
Pivot Table Tools
Introduction - About Calculated Items
A Calculated Item is a custom formula in an Excel pivot table, that can use the sum of other items in the same field. For example, calculate the sum of 2 other items in a field.
Restrictions: Here are a few general restriction on using custom formulas:
- Cannot refer to the pivot table totals or subtotals.
- Unlike worksheet formulas, calculated item formulas cannot refer to worksheet cells, by address or by name.
- Only available in non-OLAP-based pivot tables (not data model pivot tables)
Features: Here are the key features of pivot table 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.
- 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 or Column area of the pivot table.
- Calculated items are NOT shown in the PivotTable Field List.
Warnings: If you create a calculated item in a field, be aware of these issues:
- The calculated item's value can be changed or deleted in the pivot table layout
- Fields that contain a calculated item cannot be moved to the Report Filters area
- If any field contains a calculated item, you cannot add multiple copies of any field to the Values area. For example, you could not add Quantity twice, to show the Min and Max values.
Create a Calculated Item
To see the steps for creating a calculated item, watch this short video. The written steps are below the video.
Note: In newer versions of Excel, the Calculated Item command is on the PivotTable Analyze tab, under Fields, Items & Sets.
How to Create a Calculated Item
In this example, the pivot table contains an Order Status field, and the orders currently have the following types of status:
- Shipped
- Pending
- Backorder
- Canceled
In the pivot table, you could create a calculated item, named Sold, to sum all the units that have been sold, for orders with a status of Shipped, Pending, or Backorder.
Follow these steps to create a calculated item:
- In the pivot table, select a cell that contains an Order Status item. For example, select cell A5, that contains the Backorder item.
- On the Ribbon's Options tab, click Calculations
- Click Fields, Items & Sets, and then click Calculated Item.
- Note: If the Excel window is wide enough, you'll see Fields, Items & Sets as a separate command.
- In Excel 2007, on the Ribbon's Options tab, in the Tools group, click Formulas, and then click Calculated Item.
- Type a name for the Calculated Item, for example, Sold, and then press the Tab key to move to the Formula box.
- In the Fields list, select Order Status, and in the Items list, double-click Shipped, and then type a plus sign (+).
- Double-click Pending, type a plus sign, and then double-click Backorder. The complete formula is
=Shipped + Pending + Backorder
Note: You can include the space characters or omit them.
- Click OK, to save the calculated item, and to close the dialog box.
The new calculated item, Sold, is added to the Row area in the pivot table.
However, the Grand Totals have increased, because the Sold item duplicates the values from other items.
Hide the Unnecessary Pivot Items
In the pivot table, you can hide the Shipped, Pending, and Backorder items, because they are included in the Sold calculated item.
With those pivot items hidden, the pivot table will show the correct Grand Totals.
Delete a Calculated Item Manually
If you no longer need a calculated item in a pivot table, you can manually remove it. Follow these steps to remove any unwanted calculated items:
- Select the cell that contains the label for the Formula1 calculated item.
- On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (or the Options tab in Excel 2010).
- In the Calculations group, click Fields, Items and Sets (Click Formulas in Excel 2010).
- Click Calculated Item.
- From the drop-down list of formulas, select the formula you want to delete.
- Click the Delete button
- Select and delete any other unwanted items, and then click the Close button.
Delete a Calculated Item With a Macro
If you frequently need to remove calculated items in a pivot table, you can use a macro to remove them. The following code remove the calculated item whose label is selected.
Add this code to a regular module, in a workbook that is always open, such as the Personal Workbook. Then, add a button on the Quick Access Toolbar, or on the Ribbon, to run the macro.
Sub CalcItemRemove()Dim pi As PivotItemOn Error Resume NextIf Not pi Is Nothing Then If pi.IsCalculated Then pi.Delete Else MsgBox "Please select a calculated item label" End IfElse MsgBox "Please select a pivot item label"End IfEnd Sub
Modify a Calculated Item
After you create a calculated item in a pivot table, you might need to change its formula.
In the previous section, you created a calculated item named Sold, in the Order Status field. The Sold item sums the orders with a status of Shipped, Pending, or Backorder.
You can change the calculated item's formula, so it doesn't include the Backorder items.
Follow these steps to modify the calculated item:
- In the pivot table, select one of the Order Status items. For example, select cell A6, which is the Canceled item.
- On the Ribbon's Options tab, click Calculations
- Click Fields, Items & Sets, and then click Calculated Item.
- Note: If the Excel window is wide enough, you'll see Fields, Items & Sets as a separate command.
- In Excel 2007, on the Ribbon's Options tab, in the Tools group, click Formulas, and then click Calculated Item.
- In the Insert Calculated Item dialog box, click the drop down arrow for the Name box.
- Select Sold, which is the name of the calculated item you want to change.
- In the Formula box, change the formula, to remove the +Backorder.
- The revised formula is =Shipped+Pending.
- Click Modify, to save the change, and then click OK to close the dialog box.
Use Index Numbers in a Calculated Item
Instead of item names, you can use index numbers in a calculated item's formula. This can be a helpful solution if the pivot table source data changes each month, to use the previous month's data. Instead of referring to specific dates in the calculated field, use the index numbers.
For example, to sum the data for the first date in the OrderDate field and the fifteenth date, create a calculated item in the OrderDate field, with the following formula:
= OrderDate[1]+OrderDate[15]
You can also refer to pivot items by their index number, relative to the calculated item. For example, you could create a calculated item named DateCalc, with the following formula:
= OrderDate[+3]-OrderDate[+2]
If the DateCalc calculated item is moved to the top of the list of OrderDates, it calculates the difference between the value for the OrderDate that is three rows below and the OrderDate that is two rows below.
Tip: To move the item, right-click the DateCalc item, click Move, and then click Move "DateCalc" to Beginning.
Warning: If you move the calculated item into one of the referenced positions, you create a circular reference.
Warning: If you use a negative number in the relative position, the number is automatically changed to a positive number, and the formula will not produce the expected results.
Change the Calculated Item Solve Order
If you have two or more calculated items in a pivot table, there might be cells that are affected by multiple calculated items. This can cause problems, if the formulas are not solved in the order that you expected.
For example, in the pivot table shown below, there is a calculated item -- CancelRate -- in the Status field. If you select a cell in the CancelRate row, the formula shows in the formula bar. It is calculating the cancellation rate for the total number of policies.
Later, a calculated item -- Northeast -- is created in the Region field. When you select the CancelRate cell in that column, it shows the Northeast formula, instead of the CancelRate formula.
The rate is shown as 11.7%, which is not correct. We don't want it to add the other rates, we want it to use the CancelRate formula.
Change the Solver Order
To fix the problem, you can change the Solve Order for the calculated items:
- Select a cell in the pivot table, and then on the Ribbon, under PivotTable Tools, click the Options tab
- In the Calculations group, click Fields, Items & Sets, and click Solve Order.
The message at the bottom of the Calculated Item Solve Order dialog box explains that the last formula listed is the one that determines the cell's value.
We'll move CancelRate to the bottom, so its formula will be used in the CancelRate row.
- Click on the CancelRate item, and click the Move Down button, twice, to move it to the bottom of the list.
- Click Close
Note: When you change the Solve Order, it affects all calculated items in the pivot table.
The Correct Results
With the Solve Order changed, the percentages in the CancelRate row are now showing the correct values -- 5.8% for the Northeast and 2.7% for the Southwest.
When you click on the Northeast CancelRate cell, the CancelRate formula is showing, so the solve order change has fixed the problem.
Watch the Solve Order Video
To see the steps for creating calculated items, and changing the solve order, please watch this short video.
Hide Items Created With Zero Values
Watch this video to see how to create a calculated item, hide the zero value rows, and allow multiple filter types. The written instructions are below the video
Hide Items Created With Zero Values
If you create a calculated item, extra items might appear in the pivot table, such as each city being listed under each region, with zero amounts in some rows.
To hide these zero items, use a Values filter:
- Right-click a cell that contains a City row label
- In the popup menu, click Filter, and then click Value Filters.
- In the Value Filter window, from the first drop-down list, select Qty, which is the Values field you want to check.
- In the second drop-down list, select does not equal
- In the third box, type 0 (zero), and then click OK
Create List of Pivot Table Formulas
With a built-in command, you can quickly create a list of the calculated items and calculated fields in the selected pivot table. Watch this short video to see the steps, and the written instructions are on the Pivot Table Calculated Fields page.
Get the Sample Files
1) Download the Create a Calculated Item sample file. The zipped Excel workbook is in xlsx format, and does not contain any macros.
2) Download the Calculated Item Index Number sample workbook.
3) Download the Solve Order sample file.
4) Download the Hide Items Created With Zero Values sample file
More Tutorials
FAQs - Pivot Tables
Pivot Table Introduction
Calculated Items vs Calculated Fields
Calculated Field - Count
FAQs
How do you use a calculated item in a PivotTable? ›
Click the PivotTable. This displays the PivotTable Tools, adding the Analyze and Design tabs. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field. In the Name box, select the calculated field for which you want to change the formula.
What is a calculated item in a PivotTable? ›A Calculated Item is a custom formula in an Excel pivot table, that can use the sum of other items in the same field. For example, calculate the sum of 2 other items in a field. Restrictions: Here are a few general restriction on using custom formulas: Cannot refer to the pivot table totals or subtotals.
What is the difference between calculated field and calculated item in PivotTable? ›The key difference between calculated fields and calculated items is that: Calculated Fields are formulas that can refer to other fields in the pivot table. Calculated Items are formulas that can refer to other items within a specific pivot field.
Why can't I do a calculated field in PivotTable? ›When you use the data model you cannot add calculated fields and calculated items as these tools are for regular PivotTables only. Instead you must use DAX functions to write calculated columns or measures. Please consider my Power Pivot and DAX course to learn more about how to write calculated columns and measures.
How do I add a count calculated field in a PivotTable? ›Calculated Field With Orders Count Field
In the Calculations group, click Fields, Items, & Sets, and then click Calculated Field. Click Add to save the calculated field, and click Close. The CountB field appears in the Values area of the pivot table layout, and in the field list in the PivotTable Field List.
In the PivotTable Fields list, check the Qty field, to add it to the Values area. Qty appears in the pivot table as Sum of Qty. Right-click a cell in the Sum of Qty column. Point to Summarize Values By, then click Count.
How do you create a calculated item you must first? ›To create a calculated item, first select an item in the row or column field you're working with. In this case, we want to add an item to the Region field, so we'll select an item in that field. Then, on the Options tab of the PivotTable Tools ribbon, click “Fields, Items & Sets”, and select Calculated Item.
How to calculate difference between two columns in PivotTable? ›- Right-click one of the Units value cells, and click Show Values As.
- Click Difference From.
- In the Show Values As dialog box, from the Base field list, choose Date. ...
- From the Base item list, choose (previous). ...
- Click the OK button, and the pivot table shows the differences in weekly sales.
If the result of your calculation will always be dependent on the other fields you select in a PivotTable. If you need to do more complex calculations, like calculate a count based on a filter of some sort, or calculate a year-over-year, or variance, use a calculated field.
Which is the most efficient method to modify a calculated field in a PivotTable? ›First, select any cell in the pivot table. Then, on the Options tab of the PivotTable Tools ribbon, click “Fields, Items & Sets”, then choose Calculated Field. Next, select the calculated field you want to work with from the name drop-down list. You can now update the formula as you like.
What is the purpose of calculated fields? ›
A calculated field is a field that uses existing database fields and applies additional logic — it allows you to create new data from your existing data. A calculated field either: performs some calculation on database fields to create a value that is not directly stored in the database or.
How do I add a formula in Power Pivot? ›- Each formula must begin with an equal sign.
- You can either type or select a function name, or type an expression.
- Begin to type the first few letters of the function or name you want, and AutoComplete displays a list of available functions, tables, and columns.
The Count function counts the number of records that contain a number in a single-column table. The CountA function counts the number of records that aren't blank in a single-column table. This function includes empty text ("") in the count.
How do I add a calculated field to a spreadsheet? ›- On your computer, open a spreadsheet in Google Sheets.
- Click the pop-up Edit button underneath the pivot table.
- In the side panel, next to "Values," click Add. click Calculated field. ...
- On the bottom right, click Add and the new column will appear.
We can count values in a PivotTable by using the value field settings. This enables us to have a valid representation of what we have in our data. For instance, in the example below, there is a count of 16 for clients when distinctly, they are only 4.
What is one of the drawbacks of using a PivotTable? ›1. It can be a time-consuming venture. For most programs, pivot tables can create the data needed to understand metrics, but the tool doesn't include calculation options in many instances. That means the collected data must be manually calculated or equations must be manually inputted and that takes time.
Why is Excel giving me a count instead of sum? ›That probably means that Excel sees the values as text instead of as numbers. Try the following: Select such values in a single column. Make sure that the number format is set to General or Number.
How do I create a calculated measure in Excel? ›To create a Calculated Measure in Excel
On the Analyze tab, in the Calculations group, choose OLAP Tools > MDX Calculated Measure. The New Calculated Measure dialog box opens. In the Name box, specify a name for the Calculated Measure. (This step is optional.)
Use AutoSum
Select an empty cell directly above or below the range that you want to sum, and on the Home or Formula tabs of the ribbon, click AutoSum > Sum. AutoSum will automatically sense the range to be summed and build the formula for you.
We can create a calculated column to reference a measure value like this: Column1= <measure name>. But you need to note the calculated column values are calculated based on table context so it's fixed. To do further calculation, you can use measure directly without creating additional calculated column.
How do you get the first occurrence of a value in Excel? ›
Count first instance of items with formula
Select a blank cell next to your data, and type this formula =(COUNTIF($A$1:$A1,$A1)=1)+0, press Enter key, and then drag the autofill handle down to the cell needed this formula.
Source data Standard charts are linked directly to worksheet cells, while PivotCharts are based on their associated PivotTable's data source. Unlike a standard chart, you cannot change the chart data range in a PivotChart's Select Data Source dialog box.
Can you sum two columns in a PivotTable? ›If your original set of data has multiple columns with numeric values, you may find yourself adding additional fields to the Values area. If this is the case, the PivotTable will display the sum of one set of data followed by the sum of the second set of data in an adjacent column.
How to find calculated field difference between two columns in Excel? ›Calculate the difference between two numbers by inputting a formula in a new, blank cell. If A1 and B1 are both numeric values, you can use the "=A1-B1" formula. Your cells don't have to be in the same order as your formula. For example, you can also use the "=B1-A1" formula to calculate a different value.
How do I compare two columns in Excel and return a value from the third column? ›In the Formula Type drop down list, please select Lookup option; Then, select Look for a value in list option in the Choose a formula list box; And then, in the Arguments input text boxes, select the data range, criteria cell and column you want to return matched value from separately.
What is difference between calculated column and calculated measure? ›a calculated column belongs to a single table, while a measure belongs to the whole data model. A calculated column is evaluated in a row context (row by row, like in an excel table), while a measure is evaluated in the filter context.
What is the difference between table calculation and calculated field? ›Table Calculations are simpler and their scope is more limited compared to Calculated Fields. Calculated fields are much more diverse enabling deeper analysis.
What are examples of calculated fields? ›A calculated field is a new field that performs calculations based on existing fields in your PivotTable. For example, you could use a calculated field to find the average ticket sales using an existing total sales field and a field containing the number of tickets sold.
Which control type do you use to create a calculated field? ›Text boxes are the most popular choice for a calculated control because they can display so many different types of data. However, any control that has a Control Source property can be used as a calculated control.
Is there a limit to the number of calculated fields in a PivotTable? ›You can create pivot tables with up to 500,000 records. You can add any combination of row and column field values that add up to 40. For example, if you have 10 row field values, then you can add up to 30 column field values. You can create pivot table calculations only on nonaggregated values.
Which data type Cannot be marked as calculated field type? ›
Calculated fields are not available for floating number or Lookup type fields.
How do you create a profit ratio using the calculated fields? ›To sum all of the profit figures as well as sum all of the sales figures and then divide by the totals, the calculation on Tableau calculated field looks like: Sum([Profit])/Sum([Sales]). Tableau now knows to sum the figures first and then calculate the ratio, rather than sum all the individual ratios.
How do you reference a formula in a PivotTable? ›You can quickly enter a simple GETPIVOTDATA formula by typing = (the equal sign) in the cell you want to return the value to and then clicking the cell in the PivotTable that contains the data you want to return.
What is the difference between pivot and Power Pivot? ›The normal pivot version just lists fields within this single table or source that we're pointing to. Power Pivot allows us to access any of the fields in any of the tables in our data model, and then analyze them based on any relationships that we've defined. So that's the most obvious of the differences.
How do I enable calculated fields in Power Pivot table? ›Click the POWERPIVOT tab on the Ribbon in your workbook. Click the Calculated Fields in the Calculations area. Click the New Calculated Field in the dropdown list.
Where can a calculated column be used? ›- If you want your new data to appear on ROWS, COLUMNS, or in FILTERS in a PivotTable, or on an AXIS, LEGEND, or, TILE BY in a Power View visualization, you must use a calculated column. ...
- If you want your new data to be a fixed value for the row.
The difference is the context of evaluation. A measure is evaluated in the context of the cell evaluated in a report or in a DAX query, whereas a calculated column is computed at the row level within the table it belongs to.
Should calculated fields be stored in a table? ›Calculated fields belong in queries, not tables.
Calculated columns are part of life on a spreadsheet, but do not belong in a database table. Never store a value that is dependent on other fields: it's a basic rule of normalization.