How to Flatten Data in Excel Pivot Table?


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)

Before Pivot Table in Compact Form


After I Flattening it, It should look this:

After Pivot Table Tabular Form Flattened


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.

Show in Tabular Form Pivot Table

That’s about it. once you do that, you should see data in flattened format.

4 thoughts on “How to Flatten Data in Excel Pivot Table?

  1. Nadeira

    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?

What do you think? Leave a comment below.