Pinterest Pixel

Predetermined Number Formatting in Excel Pivot Tables

Let us say you have one sales column that you formatted as a number with the commas,... read more

Download Excel Workbook
Bryan
Posted on

Overview

Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline

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

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 workbookPredetermined-number-formatting.xlsx


Here is our current Pivot Table setup:

Predetermined Number Formatting in Excel Pivot Tables

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

Predetermined Number Formatting in Excel Pivot Tables

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

Predetermined Number Formatting in Excel Pivot Tables

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

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

Predetermined Number Formatting in Excel Pivot Tables

The formatting is also applied to our second Sales column!

Predetermined Number Formatting in Excel Pivot Tables

 

If you like this Excel tip, please share it
Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline
Predetermined Number Formatting in Excel Pivot Tables | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Show Field and Value Settings in Excel Pivot Tables

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...