How to Flatten Data in Excel Pivot Table?

Standard

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

BEFORE

After I Flattening it, It should look this:

After Pivot Table Tabular Form Flattened

AFTER

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.

Advertisements

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?

    Like

Thank this author by sharing the article on social media. If you have any questions or comments, please leave a reply below:

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s