How to Format the Values of Numbers in a Pivot Table

Excel uses the default format “General” for values in a cell.  This could lead to poor presentation of data especially in a pivot table where we usually deal with massive amount of data in different formats.  

Below is an example of a pivot table showing three different number formats: Number with 1000 separator (,), Currency in dollars ($) and Percentage.  

Figure 1.  Sample pivot table with different formats per field

Setting up the Data

Here we have a table of product orders and sales from January to February, with  corresponding %sales.

Figure 2.  Data for formatting values of numbers in a pivot table

Insert a Pivot Table

Step 1. Select the range of cells that we want to analyze through a pivot table. In this case, we select cells B2:F10.  

Step 2. Click the Insert tab, then Pivot Table. This will launch the Create PivotTable dialog box.

Figure 3. Inserting a Pivot Table

Step 3. In the Create PivotTable dialog box, tick Existing Worksheet.  Click the bar for Location and then click cell H2. This will position the pivot table in the existing worksheet, at cell H2.  

Figure 4. Inserting a pivot table in an existing worksheet

Step 4. In the PivotTable Field List,  tick Product and Orders.  This will show the Sum of Orders for each product from A to D.

Figure 5. Selecting the fields for values to show in a pivot table

Formatting the Values of Numbers

We have now created a pivot table. We want to change the format for Sum of Orders,which is currently in the default format General.

Figure 6. Showing the default format for Excel : “General”

There are two ways to format values of numbers.

Method 1. Using Ctrl + 1

Method 2. Using the Value Field Settings  

Example 1:

We want to present the Sum of Orders in Number form, with commas for 1000 separator, and no decimal places.  

Using Ctrl + 1 Method

Step 1. Select the cells we want to format. In this case, we select I3:I7.

Step 2. Press Ctrl + 1 to launch the Format Cells dialog box.

Step 3. Select the Number Category, then set the decimal places to zero “0”.  Tick the Use 1000 separator (,).

Figure 7. Formatting the values of numbers using Ctrl + 1

We have now changed the format for the Sum of Orders from General to Number.  

Figure 8. Output: Changing the format of values from “General” to “Number”

Example 2:

We want to add the value Sum of Sales in our pivot table and present it in currency form, with two decimal places.  

Method Using the Value Field Settings  

Step 1. Tick Sales in the PivotTable Field List.  This will add the Sum of Sales in our pivot table.

Figure 9.  Adding the field Sum of Sales to our pivot table

Step 2Select the cells that contain the values we want to format (J3:J7), and in the lower right portion of the PivotTable Field List, under Values, click Sum of Sales.

Step 3. Select Value Field Settings.

Figure 10. Formatting the values of numbers using the “Value Field Settings”

Step 4. In the Value Field Settings dialog box, click Number Format.  

Figure 11. Select “Number Format” in “Value Field Settings”

Step 5. Select the format for Currency, set to 2 decimal places, with the symbol for dollar sign   $ English (US).   

We have now changed the format for the Sum of Sales in our pivot table.  

Figure 12. Output: Changing the format of values to currency

Let us try adding one more field, %sales.  

Example 3:

Step 1. Click any value in the pivot table to show the PivotTable Field List.

Step 2. Select the field %Sales to add the Sum of %Sales to our pivot table.  

Figure 13.  Adding more values to our pivot table

Step 3. Select cells K3:K7.

Step 4. Press Ctrl + 1 since it is faster to format the values this way.  

Step 5. Select Percentage and set to 2 decimal places.

Figure 14. Formatting the values to show percentage

Figure 15.  Final result: Pivot table fields in three different formats

We can  now add more values to our pivot table, and change the formatting as we desire.  

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment