How To: Show pivot table data in flat format

As a seasoned Excel user, you are no doubt familiar with Pivot Tables and how they work. (If not, here's a great place to get started on them: Perhaps you've wanted to see your pivot table data differently. We know how to pivot the tables to display our data as needed, but what if we need to see all of the data for a field in a single flat format? How can we do this?

Consider the following sample pivot table:

In order to show the data in flat file format, we need to be showing Grand Totals as we are with this table. If you aren't seeing grand totals, right click in your pivot table, left click PivotTable options. Click the 'Totals & Filters' tab and make sure both 'Show grand totals for rows' and 'Show grand totals for columns' are checked.

To display the data in a flat format, just double click the Grand total. What you get is the flat data (like the sample below) on a new tab added to your workbook.

Comments (10)

  1. sam says:

    This doesn't give you a flat file though – it just gives you the data you stuck into the table in the original format.  if you want to do sum/count/any aggregation (the reason you create a pivot in the first place) then this doesn't seem to work.

  2. Dave says:

    I agree with Sam, this didn't solve my problem at all.

  3. Sofia says:

    It did solve my problem to see all data behind a pivot table I did not create

  4. Figured this out

    It's under PivotTable Tools > Design > Report Layout > Show in Tabular Form

    1. Sakshi says:

      Wow, I love the results. Got what I wanted.

  5. vbdotnet says:

    Exactly what I was looking for.
    Thank you!

    1. David says:

      – In the source, use the CONCATENATE function to put all the fields in a single field with delimiters. E.g., =CONCATENATE(I2,”,”,N2,”,”,G2,”,”,A2)
      – Do your Aggregating in the Pivot table against that single concat field
      – Copy Pivot Table to a new excel
      – Save as CSV
      – Open in a text editor and remove the “” around the Concate field and add the headers. If you have actual commas in yuor content use a different delimiter and then find/replace it accounting for the quotes.

  6. Andrea Westbrook says:

    This is genius – thank you! So simple. 2 clicks.

  7. Anthony Yarrell says:

    This works great when using pivot tables as a poor man’s query.

Skip to main content