Excel Pivot Table Formatting: A Practical Guide

Charts & pivot tables · Updated November 21, 2026

To format an Excel PivotTable in Microsoft 365 for Windows, click inside it, open the contextual Design tab, and choose a style or adjust its layout; for durable number formatting, use Value Field Settings > Number Format rather than formatting cells by hand. These choices control the table’s appearance, structure, and how well its formatting survives a refresh.

What PivotTable formatting controls

A PivotTable summarizes data by arranging fields into rows, columns, values, and filters. Formatting makes that summary easier to scan, but it is not the same as changing the underlying data or the calculation. For example, applying a currency format changes how a sum is displayed; it does not convert the source values or alter the total.

There are several layers of formatting, and they serve different purposes:

  • PivotTable styles set coordinated colors, borders, and emphasis for the whole report.
  • Report layout determines whether row fields appear in compact, outline, or tabular form.
  • Field and value settings control subtotals, labels, and number formats for particular fields.
  • Conditional formatting adds visual signals, such as highlighting the largest values.
  • Manual cell formatting changes selected cells, but may be less reliable when the PivotTable changes size or refreshes.

Knowing which layer to use helps prevent a familiar problem: a table looks correct at first, then loses its number format or visual arrangement after new data is added.

Apply a PivotTable style

Click any cell in the PivotTable to show its contextual tabs. Choose Design, then select a style from the PivotTable Styles gallery. The gallery includes light, medium, and dark designs. If the visible choices are limited, use the gallery’s More button to see additional styles. A style is a quick way to establish consistent heading, banding, and total-row treatments without formatting every cell individually.

In the PivotTable Style Options group on the Design tab, turn options such as Row Headers, Column Headers, Banded Rows, and Banded Columns on or off. The available effects depend on the selected style and the shape of the report. Banded rows can make it easier to follow a long list across the screen, while banded columns can help distinguish multiple value fields.

For a useful style, keep contrast high enough that labels and totals remain legible, and avoid using color as the only way to communicate meaning. If a workbook has an established color scheme, choose a style that fits it rather than adding a competing palette. You can also use the style gallery’s Clear option to remove the applied PivotTable style and return to a plainer appearance.

Choose a layout that fits the report

Layout affects how readers interpret a PivotTable. On the Design tab, use Report Layout to choose among Compact Form, Outline Form, and Tabular Form. Compact Form places multiple row fields in one column and is space-efficient, but can make it less obvious which label belongs to which field. Outline Form gives row fields separate columns with a stepped, hierarchical appearance. Tabular Form places each row field in its own column, which is often easier to read or copy into another report.

For example, a sales summary with Region and Salesperson as row fields may be compact in Compact Form, with the field labels nested in one column. In Tabular Form, Region and Salesperson have separate columns, making each record-like row clearer. The choice is about presentation; it does not change the source records or the values being summarized.

Other useful controls are grouped on the Design tab:

  • Subtotals can be shown at the top or bottom of a group, or turned off when they add clutter.
  • Grand Totals can be displayed for rows, columns, both, or neither, depending on the report.
  • Blank Rows can insert spacing after items, which may help in a presentation but makes a long report less compact.
  • Repeat All Item Labels, under Report Layout, repeats row labels on each line in supported layouts, useful when readers export or print the table.

Use these settings with a specific reading task in mind. A compact dashboard may need fewer subtotals, while a printed hierarchy may benefit from visible subtotals and repeated labels.

Format values, dates, and labels

Number formats are among the most important PivotTable settings. To format a value field, right-click one of its values and choose Value Field Settings. Select Number Format, choose a category such as Number, Currency, Percentage, or Date, set the decimal places and other options, and confirm the dialogs. This associates the format with the value field and is generally more dependable across refreshes than selecting the displayed cells and using the Home tab.

Suppose a PivotTable sums revenue. Applying a currency format with two decimal places makes the result read as money, while a count of orders usually calls for a whole-number format. A percentage field might need one decimal place. Pick a format that matches the meaning of the measure, and avoid showing unnecessary decimal places that distract from the comparison.

For dates, distinguish a date field used as a row or column label from a date or number used as a value. If a date label looks like a serial number, apply an appropriate date format to the field or its source data, then refresh if needed. Excel can also group dates into periods: right-click a date label and choose Group, then select intervals such as months or years where available. Grouping changes the report’s categories; it is not just a display format.

To rename a value heading, select the heading cell and type a clearer label, such as Revenue instead of Sum of Revenue. Excel may reject a name identical to an existing source field name, so use a distinct caption if that happens. Keep labels concise but descriptive, especially when the table has several measures.

Keep formatting after a refresh

A PivotTable can be refreshed when its source data changes, but refresh behavior can affect its appearance. For a table that grows or shrinks, formatting individual cells may not extend as expected, and column widths may change. Prefer PivotTable styles and field-level number formats for recurring reports.

To review the key refresh options, right-click inside the PivotTable and choose PivotTable Options. On the Layout & Format tab, check Preserve cell formatting on update if you want manual formatting to remain, and consider clearing Autofit column widths on update if refreshes keep changing carefully set widths. These options can help, but they do not make every manual change equally robust; a style or value-field number format is still preferable for formatting that should apply consistently.

Refresh the PivotTable with PivotTable Analyze > Refresh, or use Refresh All on the Data tab to update workbook connections and PivotTables together. If the source is an Excel Table, new rows added to that Table are generally included when the PivotTable is refreshed. If the source is a fixed cell range, rows outside that range may not be included; check the source with PivotTable Analyze > Change Data Source or convert the source range to a Table before building the report.

Use conditional formatting carefully

Conditional formatting can draw attention to values that meet a rule, such as sales below a target or the highest monthly totals. Select a value in the PivotTable, then use Home > Conditional Formatting to choose a rule, such as data bars, color scales, or a highlight rule. For a PivotTable, Excel may offer scope choices for applying the rule to selected cells, all cells showing a particular value field, or the corresponding cells for a row or column field. Choose the scope that matches the report rather than assuming the rule will automatically cover every future item.

After creating a rule, open Home > Conditional Formatting > Manage Rules to inspect its range and, where offered, its PivotTable application setting. A rule tied to the relevant value field is often more useful than one tied only to the current cell addresses, because PivotTables can expand or rearrange. Still, check the result after changing fields or refreshing. Conditional formatting should reinforce the message, not obscure the values: use a small number of meaningful rules and ensure the text remains readable.

Common formatting problems and fixes

A number format disappears. The cells may have been formatted manually rather than through Value Field Settings. Reapply the format using the value field’s Number Format button, then refresh to confirm it remains.

The columns keep changing width. Open PivotTable Options > Layout & Format and clear Autofit column widths on update. Set the widths after refreshing, then test another refresh.

Row labels appear only once. Choose Design > Report Layout > Repeat All Item Labels if repeating labels suits the layout. This is especially helpful for printing, exporting, or reading rows independently.

The report has too many totals. Use Design > Subtotals and Design > Grand Totals to adjust which levels are displayed. Removing a subtotal changes the report’s presentation, not the underlying source data.

A style or custom color seems missing. Check whether the intended PivotTable is selected and whether a style is applied. Manual cell fills can also make a table look inconsistent with its selected style. Reapply a style, then use field-level or conditional formatting only where the additional emphasis is needed.

Formatting does not cover new items. This can happen with manually formatted ranges or conditional-formatting rules restricted to old cell addresses. Review the rule scope and refresh behavior, and prefer options linked to the PivotTable field when available.

Excel on Mac and the web

In Microsoft 365 for Mac, PivotTable formatting concepts are similar, but ribbon organization and dialog details can differ from Windows. Select the PivotTable to reveal its contextual tabs, then look for the Design or PivotTable options and the style gallery. When following Windows instructions, use the equivalent command shown in the Mac ribbon or its dialog rather than assuming every control is in precisely the same location.

Excel for the web supports many common PivotTable tasks, but some layout, formatting, and configuration controls may be limited or arranged differently from the Windows desktop app. If a setting such as preserving cell formatting on update is not available in the web interface, open the workbook in the desktop app to manage it. These differences are about where controls are available, not a reason to use a different source-data format.

Frequently Asked Questions

How do I format a PivotTable without changing the source data?

Use the PivotTable’s Design tab for styles and layout, and Value Field Settings for number formats. These settings change how the summary is presented, not the original source records or the calculation itself.

Why does my PivotTable lose formatting when I refresh?

Cell-by-cell formatting may not follow a changing PivotTable range. Apply number formats through Value Field Settings, use a PivotTable style, and check PivotTable Options > Layout & Format for Preserve cell formatting on update.

How do I show currency or percentages in a PivotTable?

Right-click a value in the relevant field, choose Value Field Settings > Number Format, then select Currency, Percentage, or the appropriate category. Setting the format there is usually more reliable than formatting the visible cells directly.

Can I format one row or one cell differently?

Yes. Select the cell or area and apply a manual format, or use conditional formatting for a rule-based effect. Such formatting may be affected when the PivotTable expands, contracts, or refreshes, so test it and use field-level formatting for anything that must remain consistent.

How do I make PivotTable labels easier to read when printing?

Try Tabular Form and Repeat All Item Labels, then review subtotals and column widths. Repeated labels make each row easier to interpret on its own, particularly when a report spans multiple pages.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.