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

Facebook

Twitter

Google+

Free Pivot Table Webinar Hey there,I have been having a huge response to this highly acclaimed Pivot Table webinar and will continue to give these lessons in to the new year! If a date is not suitable for you, you can always come back to the Registration link below on a future date. Happy Pivot...

Customize an Excel Slicer Excel Slicers are very boring looking and there are only 14 different Slicer Styles to choose from, ranging from Light to Dark and with a few colors to choose from.What if you wanted to create your own Slicer using your creative mind with super cool colors and f...

How To Fix Pivot Table Empty Cells I am sure that you have come across a Pivot Table which has empty cell values and thought "What the hell is happening here?"This is because your data source has blank cells for certain items, which happens from time to time.This can be fixed in your Pivot Table and you ca...

How To Insert a Pivot Table Slicer Slicers in Excel are visual filters or interactive buttons that allow you to see what items have been chosen within a Pivot Table.They are a new feature from Excel 2010 onward and Mac for Excel 2016 and a must for anyone wanting to wow their boss by adding interactivity i...