Pivot table helps you to summarize your data quickly and easily. By the help of Pivot Table, you can analyze your data perfectly. In this article, you will get to know about the Excel Pivot Table Formatting. There are different ways of doing that. The processes are discussed below.
Table of Contents
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.
The following dialog box will appear when you click Number Format… in the above shortcut menu.
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.
More Pivot Table Designs
If these styles don’t fulfill your purpose, you can choose more styles clicking on the following button.
You will find lots of built-in styles for you to use in your pivot tables.
Tuning Pivot Table Styles
You can fine-tune your styles using controls in PivotTable Tools ➪ Design ➪ PivotTable Style Options group.
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.
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.
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 followings.
- 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.
How to Lock Pivot Table Format
- First, select the entire Pivot table and click on the right button of your mouse to press 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.
- 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.