{"id":24726,"date":"2021-10-29T14:11:38","date_gmt":"2021-10-29T12:11:38","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=24726"},"modified":"2024-02-06T16:06:11","modified_gmt":"2024-02-06T15:06:11","slug":"excel-formulas-in-calculated-items","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-formulas-in-calculated-items\/","title":{"rendered":"Excel formulas in Calculated Items"},"content":{"rendered":"

\"Excel<\/a><\/p>\n

When working with Calculated Items, you can use Excel Formulas<\/a> right away (as long as you do not reference any external cells) such as\u00a0SUM, IF, OR, AND, AVERAGE, etc.<\/strong><\/p>\n

<\/div>\n

Here is our current Pivot Table<\/a> setup:<\/p>\n

\"Excel<\/p>\n

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

 <\/p>\n

Download the Excel Workbook below and follow the step-by-step tutorial below to add Excel formulas in Calculated Items.<\/p>\n

\n

download excel workbook<\/strong><\/i><\/span>Excel-formulas-Calculated-Items.xlsx<\/span><\/a><\/p>\n<\/div>\n

 <\/p>\n

STEP 1:<\/strong><\/span> Click on any cell in the Pivot Table.<\/p>\n

\"Excel<\/a><\/p>\n

This will display PivotTable Analyze<\/strong> and Design<\/strong> tabs.<\/p>\n

STEP 2: <\/strong><\/span>Go to PivotTable Analyze > Fields, Items & Sets > Calculated Item<\/strong><\/em><\/p>\n

\"Excel<\/p>\n

STEP 3:<\/strong><\/span> In the Name<\/strong> box, type in a name that you prefer for the formula<\/a>.<\/p>\n

\"Excel<\/a><\/p>\n

STEP 4:<\/strong> <\/span>In the Formula<\/strong> box, type in the Average Function:<\/p>\n

= AVERAGE(January,February,March,April,May,June,July,August,September,October,November,December)<\/strong><\/p>\n

\"Excel<\/a><\/p>\n

STEP 5: <\/strong><\/span>Click\u00a0Add\u00a0<\/strong>then\u00a0OK<\/strong><\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

Your Calculated Item is now ready!<\/p>\n

\"Excel<\/p>\n

 <\/p>\n

Edit Formula in Calculated Item<\/strong><\/span><\/p>\n

If you wish to edit the formula <\/strong>from an average of 12 months to an average of 3 months, you can do that as well:<\/p>\n

STEP 1: <\/strong><\/span>Click on any cell in the Pivot Table.<\/p>\n

\"Excel<\/a><\/p>\n

This will display PivotTable Analyze<\/strong> and Design<\/strong> tabs.<\/p>\n

STEP 2:<\/span> <\/strong><\/span>Go to PivotTable Analyze > Fields, Items & Sets > Calculated Item<\/strong><\/em><\/p>\n

\"Excel<\/p>\n

STEP 3: <\/strong><\/span>In the Name box, select the formula<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

STEP 4: <\/strong><\/span>Edit the formula.<\/p>\n

= AVERAGE(January,February,March)<\/strong><\/p>\n

\"Excel<\/a><\/p>\n

STEP 5: <\/strong><\/span>Click Modify<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

STEP 6: <\/strong><\/span>Click OK<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

Delete Formula in Calculated Item<\/strong><\/span><\/p>\n

You can delete the formula from the Pivot Table calculated item<\/strong> by following the steps below:<\/p>\n

STEP 1:<\/strong><\/span> Click on any cell in the Pivot Table.<\/p>\n

\"Excel<\/a><\/p>\n

STEP 2:<\/span> <\/strong><\/span>Go to PivotTable Analyze > Fields, Items & Sets > Calculated Item<\/strong><\/em><\/p>\n

\"Excel<\/p>\n

STEP 3: <\/strong><\/span>In the Name box, select the formula.<\/p>\n

\"Excel<\/a><\/p>\n

STEP 4: <\/strong><\/span>Click Delete.<\/p>\n

\"Excel<\/a><\/p>\n

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

Click here<\/strong><\/a> to learn more about Pivot Table Calculated items!<\/p>\n

 <\/p>\n

Make sure to download our FREE PDF on the\u00a0333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

When working with Calculated Items, you can use Excel Formulas right away (as long as you do not reference any external cells) such as\u00a0SUM, IF, OR, AND, AVERAGE, etc. Here is our current Pivot Table setup: You can easily add another row that calculates the average sales for each year using formulas in calculated items. […]<\/p>\n","protected":false},"author":2,"featured_media":25094,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[4],"tags":[787,783,786],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/24726"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=24726"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/24726\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/25094"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=24726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=24726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=24726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}