Repeat All Item Labels In An Excel Pivot Table

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.

DOWNLOAD EXCEL WORKBOOK

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

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

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!

HELPFUL RESOURCE:

If you like this Excel tip, please share it

Email this to someone

Related Posts

Lock the Excel Pivot Table but not the Slicer Sometimes when you are sharing an Excel Pivot Table with your colleagues you do not want the other user(s) to mess with your Pivot Table layout and format. What you can do is lock the Pivot Table and only allow the user(s) to select the Slicers, making your report interact...

Errors When Grouping By Dates With Excel Pivot Tab... In our Group By Date post, we grouped our sales numbers by weeks. However there are times when we try to group by Dates and we get an error message! That is frustrating! Instead of checking the dates one by one to find out where the error occurred, I will show you a cool w...

Format Error Values in a Pivot Table Whenever you do a calculation in an Excel Pivot Table you may get an error value like a #DIV/0! This looks ugly when you are presenting important information. Luckily you can override this with a custom value or text. To activate this you need to Right Click in any Value i...

Conditionally Format a Pivot Table With Data Bars Data Bars are a new Conditional Formatting feature in Excel 2010 and they add a colored bar to your values. The length of the data bar represents the value in the cell. A longer bar represents a higher value. You have a Gradient Fill or a Solid Fill to choose from as well ...

CLICK HERE TO SEARCH OVER 300 EXCEL TUTORIALS

Leave a Comment Cancel reply

thanks

SEARCH EXCEL TUTORIALS