{"id":217,"date":"2011-09-21T15:38:50","date_gmt":"2011-09-21T15:38:50","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=217"},"modified":"2023-12-30T23:14:25","modified_gmt":"2023-12-30T23:14:25","slug":"adding-a-calculated-field-to-a-pivot-talbe-in-excel-2010","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/adding-a-calculated-field-to-a-pivot-talbe-in-excel-2010\/","title":{"rendered":"Adding a Calculated Item to a Pivot Table in Excel 2010"},"content":{"rendered":"

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

Above is an example of a standard pivot table in Microsoft Excel 2010.\u00a0 It is set up with financial quaters as column headers and products as Row labels.\u00a0 I’m interested in seeing the results for the combined sales for the first half and the second half of the year. As you can see I have colour coded these two halves and now I am going to add two “calculated items” showing a total for Q1+Q2 and Q3+Q4.<\/p>\n

TAKE ACTION:\u00a0 <\/strong><\/p>\n

    \n
  1. \"\"<\/a>Ensure your cursor is placed onto the Q4 column header as in the image.<\/li>\n
  2. Select the “PivotTable tools” tab and click on “options”<\/li>\n
  3. In the “calculations” box” select “fields, items, & sets” and then \"\"<\/a>“calculated items”<\/li>\n<\/ol>\n

    When this box appears follow these instructions:<\/strong><\/p>\n

      \n
    1. Click into the “name” field and enter the new name Qtr1+Qtr2.<\/li>\n
    2. Click into “formula” field, remove the 0, double click on Qtr1 in the “Items” field, add + then double click on the Qtr2 from the “Items” field.\u00a0 Here you are entering a formula which is Qtr1 + Qtr2.<\/li>\n
    3. Click the “Add” button and then OK<\/li>\n<\/ol>\n

      You will now see that\u00a0this new column has been added to your PivotTable in Microsoft Excel 2010.<\/p>\n

      Repeat this process for Qtr3 + Qtr4 and adjust the background colours to match those already on the pivot table.\u00a0 All going well you should have a pivot table that resembles the one I have pasted below:<\/p>\n

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

      You\u00a0now have a pivot chart showing you the totals for both halves of the year.\u00a0\u00a0Take note that your grand total includes your two new columns so its\u00a0best to remove that. To learn how to\u00a0remove the\u00a0total column in Microsoft Excel 2010, well that’s for the next blog.<\/p>\n

      Good luck!<\/p>\n

       <\/p>\n

       <\/p>\n","protected":false},"excerpt":{"rendered":"

      Above is an example of a standard pivot table in Microsoft Excel 2010.\u00a0 It is set up with financial quaters as column headers and products as Row labels.\u00a0 I’m interested in seeing the results for the combined sales for the first half and the second half of the year. As you can see I have […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[84,173,174,192,220,224,292,418,433],"class_list":["post-217","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-calculated-field","tag-excel-2010","tag-excel-2010-courses","tag-excel-courses","tag-excel-training-y","tag-excel-training-london","tag-how-to-calculate-pivot-table","tag-pivot-talbe","tag-pr"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=217"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/217\/revisions"}],"predecessor-version":[{"id":6793,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/217\/revisions\/6793"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}