Pinterest Pixel

Repeat All Item Labels In An Excel Pivot Table

John Michaloudis
Whenever you create a Pivot Table the default layout is in Compact Form, which puts all the data in one column.  Many people do not like this layout as you cannot copy and paste the data and do further analysis in another worksheet.
The best layout to use is either the Outline or Tabular layouts.

 You can then select to Repeat All Item Labels which will fill in any gaps and allow you to take the data of the Pivot Table to a new location for further analysis.

Whenever you create a Pivot Table the default layout is in Compact Form, which puts all the data in one column.  Many people do not like this layout as you cannot copy and paste the data and do further analysis in another worksheet. The best layout to use is either the Outline or Tabular layouts.  You can then select to Repeat All Item Labels which will fill in any gaps and allow you to take the data of the Pivot Table to a new location for further analysis.

Key Takeaways

  • Improves Readability – Repeating item labels fills in blank cells, making the Pivot Table easier to read, especially when exported or printed.

  • Great for Filtering and Sorting – With labels repeated, filters and sorts work more effectively on full rows of data.

  • Applies to Row Fields Only – This feature works on row fields in a Pivot Table but is not available for column fields.

  • Simple to Enable – Right-click on a row label, go to Field Settings, then check Repeat item labels under the Layout & Print tab.

  • Helpful for Data Exports – If you’re exporting Pivot Table data for reports or further analysis, repeating labels ensures no critical data is missing in flat files.

How to Repeat All Item Labels In An Excel Pivot Table

STEP 1: Click in the Pivot Table and choose PivotTable Tools > Options (Excel 2010) or Design (Excel 2013 & 2016) > Report Layouts > Show in Outline/Tabular Form

Repeat All Item Labels In An Excel Pivot Table

STEP 2: Now to fill in the empty cells in the Row Labels you need to select PivotTable Tools > Options (Excel 2010) or Design (Excel 2013 & 2016) > Report Layouts > Repeat All Item Labels

Repeat All Item Labels In An Excel Pivot Table

STEP 3: Your Pivot Table will show all the item labels and you can now copy & paste it into a new area to do further analysis!

Repeat All Item Labels In An Excel Pivot Table

Frequently Asked Questions

What does “Repeat All Item Labels” do in a Pivot Table?
It fills in all the blank cells in a Pivot Table’s row fields by repeating the labels from higher levels, improving readability and usability.

How do I turn on Repeat Item Labels?
Right-click any row label > Field Settings > go to the Layout & Print tab > check Repeat item labels, then click OK.

Can I repeat labels for column fields too?
No, this feature is only available for row labels, not for column fields in Pivot Tables.

Why are my item labels not repeating even after enabling it?
Ensure your Pivot Table is in Tabular Form or Outline Form—labels won’t repeat in Compact Form.

Is it possible to apply Repeat Item Labels to all fields at once?
Yes, select the entire Pivot Table, go to the Design tab on the Ribbon, then click Report Layout > Repeat All Item Labels.

728x90

If you like this Excel tip, please share it


Founder & Chief Inspirational Officer

at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  Show Multiple Grand Totals

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...