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 | MyExcelOnline

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 WORKBOOK

 

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

Excel formulas in Calculated Items | MyExcelOnline

This will display PivotTable Analyze and Design tabs.

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

Excel formulas in Calculated Items | MyExcelOnline

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

Excel formulas in Calculated Items | MyExcelOnline

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 | MyExcelOnline

STEP 5: Click Add then OK

Excel formulas in Calculated Items | MyExcelOnline

 

Your Calculated Item is now ready!

Excel formulas in Calculated Items | MyExcelOnline

 

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 | MyExcelOnline

This will display PivotTable Analyze and Design tabs.

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

Excel formulas in Calculated Items | MyExcelOnline

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

Excel formulas in Calculated Items | MyExcelOnline

STEP 4: Edit the formula.

= AVERAGE(January,February,March)

Excel formulas in Calculated Items | MyExcelOnline

STEP 5: Click Modify.

Excel formulas in Calculated Items | MyExcelOnline

STEP 6: Click OK.

Excel formulas in Calculated Items | MyExcelOnline

 

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 | MyExcelOnline

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

Excel formulas in Calculated Items | MyExcelOnline

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

Excel formulas in Calculated Items | MyExcelOnline

STEP 4: Click Delete.

Excel formulas in Calculated Items | MyExcelOnline

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!

 

HELPFUL RESOURCE:

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

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn