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

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

HELPFUL RESOURCE:

728x90

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Pivot Table Count Transactions The default Value Field Setting when analyzing data with a Pivot Table is to Sum, but you can also analyze a Pivot Table with a Count. This is useful when you want to see how many sales transactions  took place within a region, a month, a year or per business unit. DOWNLOAD...
Fix Column Width in a Pivot Table Each time you Refresh a Pivot Table you will most likely get annoyed at the fact that the column widths that you worked so hard to align - will return back to normal 🙁 Do not fear, Pivot Table Options is here! All you need to do is Right Click in the Pivot Table and choose...
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...
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 ...