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.


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

tabular form excel

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 excel

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 excel pivot table

Repeat All Item Labels



If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Group Dates with a Pivot Table Grouping Dates is very easy with a Pivot Table.  All you have to do is Right Click on your Date values (which are either in the Row or Column Labels of your Pivot Table), then choose the Group option.  From the dialogue box you can choose to Group by Days, Months, Quarters or Yea...
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 ...
Different Ways to Filter an Excel Slicer Excel Slicers are the best thing since sliced bread! They are a new feature from Excel 2010 onward and Mac for Excel 2016 and are visual buttons that shows you what items have been filtered or selected in a Pivot Table. There are several ways that you can filter an Exce...
Sorting a Pivot Table There are a few ways that you can Sort a Pivot Table.  You can Sort the Row/Column Labels as well as Values within a Pivot Table.  Below I show you three quick ways... DOWNLOAD WORKBOOK Helpful Resource: