{"id":23727,"date":"2022-02-09T11:03:18","date_gmt":"2022-02-09T10:03:18","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=23727"},"modified":"2024-03-20T22:42:36","modified_gmt":"2024-03-20T21:42:36","slug":"predetermined-number-formatting","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/predetermined-number-formatting\/","title":{"rendered":"Predetermined Number Formatting in Excel Pivot Tables"},"content":{"rendered":"

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

Let us say you have one sales column that you formatted as a number with the commas, then you add a new sales column again<\/strong>, expecting the same numerical formatting would apply… Then you get this:<\/p>\n

\"Predetermined<\/p>\n

What happened? Why did your formatting not take effect on the second sales column<\/strong>? I have the perfect workaround for adding predetermined number formatting in Pivot Table<\/a>. Read on below!<\/p>\n

 <\/p>\n

Exercise Workbook:<\/strong><\/em><\/p>\n

\n

download excel workbook<\/strong><\/i><\/span>Predetermined-number-formatting.xlsx<\/span><\/a><\/p>\n

\n<\/div>\n

\n

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

\"Predetermined<\/p>\n

STEP 1:<\/strong><\/span> Let us select the entire Pivot Table. Go to\u00a0PivotTable Analyze > Actions > Select > Entire PivotTable<\/strong><\/em><\/p>\n

\"Predetermined<\/p>\n

STEP 2:<\/strong><\/span> Now to select the sales numbers column, go to\u00a0PivotTable Analyze > Actions > Select > Values<\/strong><\/em><\/p>\n

\"Predetermined<\/p>\n

STEP 3:<\/strong><\/span> Let us apply our number formatting<\/a>! Open the Format Cells<\/strong> dialog by pressing\u00a0CTRL + 1<\/strong><\/p>\n

Select\u00a0Number\u00a0<\/strong>and tick the\u00a0Use 1000 Separator (,)<\/strong><\/p>\n

Click OK<\/strong><\/p>\n

\"Predetermined<\/p>\n

STEP 4:<\/strong><\/span> The number formatting is applied on our first Sales column. Now drag\u00a0SALES\u00a0<\/strong>to Values<\/strong><\/p>\n

\"Predetermined<\/p>\n

The formatting is also applied to our second Sales column!<\/p>\n

\"Predetermined<\/p>\n

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

Let us say you have one sales column that you formatted as a number with the commas, then you add a new sales column again, expecting the same numerical formatting would apply… Then you get this: What happened? Why did your formatting not take effect on the second sales column? I have the perfect workaround […]<\/p>\n","protected":false},"author":2,"featured_media":25380,"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":[880,879,878],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/23727"}],"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=23727"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/23727\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/25380"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=23727"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=23727"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=23727"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}