Predetermined number formatting

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:

Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline

What happened? Why did your formatting not take effect on the second sales column? I have the perfect workaround for adding predetermined number formatting in Pivot Table. Read on below!

 

Exercise Workbook:

DOWNLOAD EXCEL WORKBOOK


Here is our current Pivot Table setup:

Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline

STEP 1: Let us select the entire Pivot Table. Go to PivotTable Analyze > Actions > Select > Entire PivotTable

Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline

STEP 2: Now to select the sales numbers column, go to PivotTable Analyze > Actions > Select > Values

Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline

STEP 3: Let us apply our number formatting! Open the Format Cells dialog by pressing CTRL + 1

Select Number and tick the Use 1000 Separator (,)

Click OK

Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline

STEP 4: The number formatting is applied on our first Sales column. Now drag SALES to Values

Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline

The formatting is also applied to our second Sales column!

Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 101 Best Excel Tips & Tricks:

Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline

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