Excel Pivot Table Formatting (The Ultimate Guide)

Pivot table helps you to summarize your data quickly and easily. With the help of Pivot Table, you can analyze your data perfectly. In this article, you will get to know about Excel Pivot Table Formatting. There are different ways of doing that. The processes are discussed below.

Number Formatting

The pivot table uses General number formatting. You can change the number format for all pivot data. To change the data format, right-click any value and choose Number Format from the shortcut menu. Then use the Format Cells dialog box to change the number format of your pivot data.

Formatting the pivot table

Choose Number Format… from this shortcut menu.

The following dialog box will appear when you click Number Format… in the above shortcut menu.

Formatting Pivot Tables

This dialog box appears when you choose Number Format… from the shortcut menu when you right-click on any cells containing values

Using Pivot Table Designs

There are several built-in styles that you can apply to your pivot table. Select any cell in your pivot table and then choose PivotTable Tools ➪ Design ➪ PivotTable Styles to select a style.

Pivot Table Designs

PivotTables Styles Group. You can choose any built-in styles for your pivot tables.

More Pivot Table Designs

If these styles don’t fulfill your purpose, you can choose more styles by clicking on the following button.

Formatting pivot tables

Click this circled button to display more built-in styles.

You will find lots of built-in styles for you to use in your pivot tables.

Formatting Pivot Tables

A huge number of styles are waiting for your use in pivot tables

Tuning Pivot Table Styles

You can fine-tune your styles using controls in the PivotTable Tools ➪ Design ➪ PivotTable Style Options group.

Pivot Table Styles

PivotTable Style Options. You can fine-tune your styles using controls from this group.

Layout Controls

You can also use the controls from the PivotTable ➪ Design ➪ Layout group to control various elements in your pivot table. You can use any of the following controls:

  • Subtotals: Using this control you can add/ hide subtotal, and choose where to display them (above or below the data).
  • Grand Totals: Using this control, you can choose which types, if any, to display.
  • Report Layout: There are three report layouts. You can choose any of three different layout styles (compact, outline, or tabular) using this control. You can also choose to hide/active repeating labels.
  • Blank Row: You can add a blank row between items to improve readability.

More Controls

The PivotTable Tools ➪ Analyze ➪ Show group contains more options that affect the appearance of your pivot table. For example, you use the Show +/- Button to toggle the display of +/- sing in expandable items.

Read More: How to create a pivot table report in Excel

Pivot Table Options Dialog Box

Still, more pivot table options are available from the PivotTable Options dialog box. To display this dialog box, choose PivotTable Tools ➪ Analyze ➪ PivotTable ➪ Options. Or You can right-click any cell in the pivot table and choose PivotTable Options from the shortcut menu.

Pivot Table Options

PivotTable Options Dialog box. You can choose more styling options for your Pivot Tables.

Experiment with styling features is the best way to become familiar with all these layout and formatting options.

Happy Excelling 🙂

Copy Pivot Table Format

If you want to copy the format of your Pivot table in other worksheets or in your existing worksheet. You can do the following.

  • Copy your Pivot Table.

  • select a range in your existing worksheet or new worksheet where you want to paste your data. You can paste the data directly into the available paste option or you can use the paste special feature.

  • In the Paste Special feature, click on the paste option that you want to use and click OK.

Copy Pivot Table Format

How to Lock Pivot Table Format

  • First, select the entire Pivot table and click on the right button of your mouse to press the Format Cells option.
  • In the protection option of the Format Cells box. Uncheck the Locked option and press OK.

  • Then on the Review Tab on top click on the Protect Sheet option
  • Put a tick mark on the Select unlocked cells and set a password.

How to Lock Pivot Table Format

  • After this whenever you want to edit the pivot table a dialogue box will appear like the picture below.

  • If you want to unlock the protected cells click on the Review on top and then click on the Unprotect Sheet.
  • It will ask for a password (if you set a password). Type the password. You will see the sheet is unlocked now.


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply