Pinterest Pixel

Excel formulas in Calculated Items

When working with Calculated Items, you can use Excel Formulas right away (as long as you do... read more

Download Excel Workbook
Bryan
Posted on

Overview

Excel formulas in Calculated Items | MyExcelOnline Excel formulas in Calculated Items | MyExcelOnline

When working with Calculated Items, you can use Excel Formulas right away (as long as you do not reference any external cells) such as SUM, IF, OR, AND, AVERAGE, etc.

Here is our current Pivot Table setup:

Excel formulas in Calculated Items

You can easily add another row that calculates the average sales for each year using formulas in calculated items. Let’s see how it can be done!

 

Download the Excel Workbook below and follow the step-by-step tutorial below to add Excel formulas in Calculated Items.

download excel workbookExcel-formulas-Calculated-Items.xlsx

 

STEP 1: Click on any cell in the Pivot Table.

Excel formulas in Calculated Items

This will display PivotTable Analyze and Design tabs.

STEP 2: Go to PivotTable Analyze > Fields, Items & Sets > Calculated Item

Excel formulas in Calculated Items

STEP 3: In the Name box, type in a name that you prefer for the formula.

Excel formulas in Calculated Items

STEP 4: In the Formula box, type in the Average Function:

= AVERAGE(January,February,March,April,May,June,July,August,September,October,November,December)

Excel formulas in Calculated Items

STEP 5: Click Add then OK

Excel formulas in Calculated Items

 

Your Calculated Item is now ready!

Excel formulas in Calculated Items

 

Edit Formula in Calculated Item

If you wish to edit the formula from an average of 12 months to an average of 3 months, you can do that as well:

STEP 1: Click on any cell in the Pivot Table.

Excel formulas in Calculated Items

This will display PivotTable Analyze and Design tabs.

STEP 2: Go to PivotTable Analyze > Fields, Items & Sets > Calculated Item

Excel formulas in Calculated Items

STEP 3: In the Name box, select the formula.

Excel formulas in Calculated Items

STEP 4: Edit the formula.

= AVERAGE(January,February,March)

Excel formulas in Calculated Items

STEP 5: Click Modify.

Excel formulas in Calculated Items

STEP 6: Click OK.

Excel formulas in Calculated Items

 

Delete Formula in Calculated Item

You can delete the formula from the Pivot Table calculated item by following the steps below:

STEP 1: Click on any cell in the Pivot Table.

Excel formulas in Calculated Items

STEP 2: Go to PivotTable Analyze > Fields, Items & Sets > Calculated Item

Excel formulas in Calculated Items

STEP 3: In the Name box, select the formula.

Excel formulas in Calculated Items

STEP 4: Click Delete.

Excel formulas in Calculated Items

This is how you can easily create formulas in a calculated item, edit them or even delete them if it is not required anymore.

Click here to learn more about Pivot Table Calculated items!

 

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

If you like this Excel tip, please share it
Excel formulas in Calculated Items | MyExcelOnline Excel formulas in Calculated Items | MyExcelOnline
Excel formulas in Calculated Items | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

See also  Connect Slicers to Multiple Excel Pivot Tables

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...