Formatting and refresh Updated November 2026

Copy a pivot table as values

Click inside the pivot, press Ctrl+A to select it, Ctrl+C, go to the destination and use Paste Special, Values and Number Formats (Ctrl+Alt+V, then A, Enter). You get ordinary cells with the same numbers and formats and no link to the source; set Tabular Form and Repeat All Item Labels first for a clean list.

Syntax

Click in pivot > Ctrl+A > Ctrl+C > destination > Ctrl+Alt+V > Values and Number Formats > OK

Part	What it is
`Values (V)`	Numbers and text only, no formats
`Values and Number Formats (A)`	Numbers plus their currency or percent formats
`Keep Source Formatting`	Paste option that preserves the pivot style's fills and borders as plain formatting

Examples

Design > Tabular Form; Repeat All Item Labels; Subtotals off; Grand Totals off
Ctrl+A, Ctrl+C, Paste Special > Values

Result: A flat table with Region, Rep, Revenue on every row, ready for VLOOKUP or another pivot.

Ctrl+A, Ctrl+C, Paste > Keep Source Formatting

Result: A copy that looks like the pivot (banding, bold totals) but is static.

Select only the values block > Ctrl+C > Paste Special > Values

Result: Just the numbers, for pasting into an email or another report.

Common mistakes

Paste gives you a second live pivot: a normal paste copies the pivot itself; use Paste Special, Values.
The outer labels are blank on most rows: turn on Repeat All Item Labels before copying.
The header says Row Labels: switch to Tabular or Outline form first so real field names are copied.

Practice Copy a pivot table as values

These drills check your answer on live data, free, no account needed.

Region, then repShow total Revenue by Region, and within each region by Rep.

Questions

How do I convert a pivot table to a normal range?

Copy it and Paste Special as Values in place (same top-left cell). The pivot is replaced by static cells.

Does copying a pivot keep the formatting?

Values and Number Formats keeps number formats. For colors and borders use Paste with Keep Source Formatting, then Paste Special Values over it to break the link.

Tabular layoutSwitch a pivot table to Tabular Form so every row field gets its own column, compare Compact, Outline and Tabular, and make Tabular the default layout.
Repeat row labelsFill the blank cells under an outer row label so every row shows its region: Design, Report Layout, Repeat All Item Labels, plus the per-field option.
GETPIVOTDATAGETPIVOTDATA(data_field, pivot_table, field1, item1) pulls one value out of a pivot table by its labels, so it survives rearranging. Syntax and examples.
Pivot table shortcutsThe keyboard shortcuts that matter for pivot tables: insert, refresh, refresh all, group and ungroup, open the field list, select the whole pivot.