How to flatten data in Excel Pivot Table?
By default, its hierarchical but I want the data in a Tabular/Flattened format. How do I do that? I am going to show how to do that with Excel 2010.
Default View (Note the Hierarchical view of Pivot Tables)
After I Flattening it, It should look this:
Note: it also depends on data, not every data can be represented in the flattened view. In above example, we’re analyzing each product (evident by product id) and so it supports the flattened view.
here are the steps:
1. Select Pivot Table
2. From the Toolbar, Turn off Subtotals. Go to Pivot Table Options > Design > Sub Totals > Do not show Subtotals
3. Go to Pivot Table options > Design > Report Layout > Show in Tabular Form.
That’s about it. once you do that, you should see data in flattened format.
Thanks Paras .. I was struggling to get this done in EXCEL. Appreciate your blog.
I am glad this was helpful!
Hi Paras..
Do you know what happened to formatting function for Excel 2010? In prior versions, there was a format button that allowed you to select Crop 1, Crop2, etc.. did the +/- feature replace those formats?
Hi Nadeira,
Thanks for the comment – I am not familiar with the formatting function for Excel 2010.