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+

Group Sales with a Pivot Table A Pivot Table allows you to Group your transactional sales values by any range that you want. You can then summarize each sales group and see the total sales and the number of transactions that they have produced. This is useful for pricing analysis.DOWNLOAD WORKBOOK...

Show The Difference From Previous Years With Excel... Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the DIFFERENCE FROM calculation.You can show the values as the Difference From previous months, years, day etc. This is just great when your boss asks you how you ...

Classic Pivot Table Layout View For all of you old schoolers out there who used to use the drag and drop Pivot Table option in Excel 2003 or prior, well you still have that option in Excel 2010 and onwards.To activate this option you need to Right Click in your Pivot Table and choose Pivot Table Options. ...

50 Things You Can Do With Excel Pivot Tables Pivot Tables in Excel are one of the most powerful features within Microsoft Excel. A Pivot Table allows you to analyze more than 1 million rows of data with just a few mouse clicks, show the results in an easy to read table, "pivot"/change the report layout with the ease of dr...