Go Back

How to Add Formatting to Your Pivot Table

We can format Pivot Tables in several ways like refreshing the table when we have new data, doing number formatting, and designing the Pivot Table. The steps below will walk through the process.

Figure 1: Refreshed Pivot Table with New Data

Setting up the data

  • We will use the Pivot Table in figure 2 to illustrate how we will format our Pivot Table

Figure 2: Setting up the Data

Refreshing the Pivot Table

We need to refresh our Pivot Table whenever we add new data to the data table.

  • We can expand the Range of the Table by taking our cursor to the edge as shown in figure 3 and drag down

Figure 3: Expanding the Range of the Data Table

  • We will insert new values into the empty cells

Figure 4: Values inserted into the Expanded Range of the Data Table

  • Our Pivot Table doesn’t automatically update as we add these values
  • We will click on the Pivot Table, right-click, and click on Refresh

Figure 5: Prompt to Replace the Pivot Table

  • We will click Yes since we want to update the current Pivot table

Figure 6: Refreshed Pivot Table with New Data

Number Formatting

  • We can format the Sum of Sales in figure 6 to have a particular currency like the United States Dollar ($)
  • We will click on any cell of the Sum of Sales Column
  • We will right-click and click on Number format

Figure 7: Format Cells Dialog Box


  • We will click on Currency and set the decimal places to 0. We will also set the symbol as $
  • We will click OK


Figure 8: Formatted Cells with Currency ($)

Pivot Table Designs

  • We can design our Pivot Table by clicking on the Pivot table to have access to the Pivot table tools

Figure 9: Pivot Table Tools

We will click on Design

Figure 10: Design styles for our Pivot Table

  • We will click on our choice

Figure 11: Designed Pivot Table

  • With the Design tab, we can also alter the subtotals and grand totals by clicking on the drop-down arrow

Figure 12: Pivot Table tool for altering Subtotals and Grand totals

Instant Connection to an Expert through our Excelchat Service

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment