How to Format a Pivot Table in Excel

Pivot Tables are one of Excel’s most powerful features, but let’s be honest - the default design is a bit of a snoozefest. You do all the hard work summarizing thousands of rows of data, only to end up with a messy, confusing block of numbers. This guide will walk you through how to format your Pivot Table step-by-step, turning it from a cluttered data dump into a clean, professional-looking report that’s easy to read and understand.

First Things First: Getting Your Data Ready

Before you can format a Pivot Table, you need to create one. The key is to start with well-structured source data. This means your data should be in a simple tabular format: a single header row at the top with unique column names, and each subsequent row containing a single record. There should be no blank rows or columns within your data set.

For our examples, we’ll use a simple sales data table with columns like Date, Region, Product, Sales Rep, and Revenue.

To create the Pivot Table:

1. Click anywhere inside your data range.
2. Go to the Insert tab on the Ribbon and click PivotTable.
3. Excel will automatically select your data range. Choose where you want to place the Pivot Table (a new worksheet is usually best) and click OK.

Now you have a blank Pivot Table, ready to be built and formatted.

Step 1: Choose the Right Report Layout

When you first drag fields into your Pivot Table, Excel defaults to what’s called “Compact Form.” This layout tries to save space by cramming multiple fields into a single column. It’s functional, but not always the easiest to read. You have better options.

With your Pivot Table selected, go to the Design tab on the Ribbon.

Click on Report Layout. You’ll see three main options:

• Compact Form: The default. Saves horizontal space but can be hard to read when you have many row labels.
• Outline Form: Displays each row field in its own separate column and includes classic subtotals at the top of each group. It’s a nicely balanced view.
• Tabular Form: This is a favorite for many analysts. It puts each field in its own column, just like a standard table, making it incredibly easy to read and copy-paste elsewhere if needed.

For a clean, clear report, start by switching your layout to Tabular Form. For most situations, it’s the most intuitive view.

Extra Tip: Repeat All Item Labels

Once you switch to Tabular or Outline Form, you might notice some blank cells. For instance, if you have “East” as a region, it only appears on the first row of that group. To fill in these gaps for better readability:

1. Go to the Design tab.
2. Click Report Layout.
3. Select Repeat All Item Labels.

This simple change makes your data much easier to scan, sort, and filter.

Step 2: Format Numbers for Clarity

Raw numbers in a Pivot Table are just that - raw. A column showing “15000” is much less meaningful than one showing “$15,000.00.” You need to add number formatting, but there’s a right way and a wrong way to do it.

The Wrong Way: Selecting the entire column in the worksheet and applying formatting from the Home tab. This formatting will break or get misapplied as soon as you refresh, filter, or rearrange your Pivot Table because the cell references will change.

The Right Way: Format the numbers through the Pivot Table’s field settings. This ensures the formatting sticks with the data, no matter how you pivot.

How to Format Pivot Table Numbers Correctly

1. In the Values area of the Pivot Table Fields pane, click the field you want to format (e.g., “Sum of Revenue”).
2. Select Value Field Settings from the menu that appears.
3. In the new dialog box, click the Number Format button at the bottom left.
4. Now you’ll see the familiar Format Cells window. Choose your desired format, such as Currency, Number (with a thousand separator), or Percentage.
5. Click OK twice to close the windows.

Your numbers will now be formatted correctly and will remain so even when the table structure changes.

Step 3: Customize the Look & Feel with PivotTable Styles

Excel’s default blue-and-white design is fine, but you can do better. The Design tab gives you complete control over your Pivot Table’s color scheme and overall style.

Using Pre-Built Styles

Under the Design tab, you’ll find the PivotTable Styles gallery. It has a huge collection of pre-set designs categorized under Light, Medium, and Dark. Hover over any style to see a live preview on your table.

This is the quickest way to apply a clean, professional design that matches your company’s branding or your dashboard’s color scheme.

Fine-Tuning Your Design

Once you’ve picked a style, you can refine it further using the PivotTable Style Options on the right side of the Design tab:

• Banded Rows/Banded Columns: This adds alternating colors to rows or columns, making large tables much easier to read by guiding the eye across the sheet.
• Row Headers/Column Headers: Adds special formatting (like bolding) to your headers to make them stand out.

Playing with these simple checkboxes can dramatically improve your table’s readability in just a few clicks.

Step 4: Rename Fields to Be More Intuitive

By default, when you add a field like “Revenue” to the values area, Excel names it “Sum of Revenue.” This is accurate, but it’s not very elegant. You can easily change this.

There are two simple ways to rename a field header:

1. Directly in the Cell: Simply click on the header cell in the Pivot Table (e.g., the cell that says “Sum of Revenue”) and type your new name (e.g., “Total Revenue” or just “Revenue”). Hit Enter.
2. In Value Field Settings: Go back to the Value Field Settings for your field. At the top, you’ll see a Custom Name box. Change the name here and click OK.

Note: Excel won’t let you rename a field to the exact same name as a field in your source data. A simple workaround is to add a space at the end. For example, if you want to rename “Sum of Sales” to “Sales” but already have a “Sales” column in your source data, just name it “Sales ” (with a trailing space). Excel will accept it, and no one will notice the difference.

Step 5: Control Totals and Subtotals

Pivot Tables automatically add grand totals and subtotals, which is often helpful. But sometimes, they just create clutter. You can easily turn them on or off.

On the Design tab, you’ll find two controls:

• Subtotals: Click this to choose whether to show subtotals at the top or bottom of each group, or not to show them at all.
• Grand Totals: Here, you can decide whether to show grand totals for rows, columns, both, or neither.

Turning off subtotals when you have many nested row labels can make your report feel much cleaner and more focused.

Step 6: Elevate Your Report with Slicers

Filters are good, but slicers are fantastic. A slicer is an interactive button that lets you (or your audience) filter the Pivot Table data with just a click. They are more user-friendly and make your report feel more like a dynamic dashboard.

How to Add a Slicer

1. Click anywhere inside your Pivot Table.
2. Go to the PivotTable Analyze tab (this tab appears when your pivot table is selected).
3. In the Filter group, click Insert Slicer.
4. A dialog box will appear showing all your fields. Check the box for each field you want to create a filter for (e.g., Region, Sales Rep).
5. Click OK.

You’ll now have floating filter panels that you can move and resize. Clicking on a button in the slicer will instantly filter your Pivot Table. To select multiple items, hold down Ctrl while you click. To clear a slicer’s filter, click the little funnel icon in its top-right corner.

Final Thoughts

Turning raw data into an insightful report is about more than just summarizing numbers - it’s about presenting them clearly. By using smart layouts, proper number formatting, thoughtful design, and interactive tools like slicers, you can transform a standard, clunky Pivot Table into a professional report that is easy for anyone to understand and use.

Of course, building and endlessly formatting reports in spreadsheets is often a time-consuming, manual process. If you find yourself exporting CSV files and rebuilding the same pivot tables every week just to keep tabs on your business performance, there’s an easier way. We built Graphed to connect directly to your data sources – like Google Analytics, Shopify, or Salesforce – so you can say goodbye to manual grunt work. Instead, simply describe the dashboard or report you want in plain English, and have a live, professional-looking dashboard ready in seconds.