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 RESOURCES:

Get access to 30+ Microsoft Excel & Office courses for ONLY $1.

Click here to get this limited-time $1 offer!

Microsoft Excel & Office Online Training Course

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