# Excel: can I create a "reverse" pivot table?
**URL:** https://boards.straightdope.com/t/excel-can-i-create-a-reverse-pivot-table/611346
**Category:** Factual Questions
**Created:** [January 31, 2012, 10:20pm UTC](https://boards.straightdope.com/t/excel-can-i-create-a-reverse-pivot-table/611346 "2012-01-31T22:20:45Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@TroutMan](https://boards.straightdope.com/u/TroutMan)
#### Post date: [January 31, 2012, 10:20pm UTC](https://boards.straightdope.com/t/excel-can-i-create-a-reverse-pivot-table/611346/1 "2012-01-31T22:20:45Z")
I use PivotTables a lot in Excel. Great things. Now I have a table where each row/column intersection is a datapoint, and I want to deconstruct it into a format more like PivotTable source data where each row represents a datapoint.
Here’s a simple example. My source data looks like this:
```auto
Industry Size Q1 Q2 Q3 Q4
-------- ------ -- -- - - --
Retail 1-10 2 1 5 6
Retail 11-50 5 6 7 8
Healthcare 1-10 8 8 9 10
Healthcare 11-50 12 13 14 15
```
I want to flatten this as shown below.
```auto
Industry Size Quarter Amount
-------- ---- ------- ------
Retail 1-10 Q1 2
Retail 1-10 Q2 1
Retail 1-10 Q3 5
...
```
By putting the data in this structure, I could then pivot it in other ways (for example, by size then industry).
Anyone know any easy way to do this? I’m good with VBA so I could write a script to scrape the data from the table if that’s the only way.
---
### Author:  [@mcgato](https://boards.straightdope.com/u/mcgato)
#### Post date: [January 31, 2012, 10:45pm UTC](https://boards.straightdope.com/t/excel-can-i-create-a-reverse-pivot-table/611346/2 "2012-01-31T22:45:24Z")
That would be a snap in SAS JMP, as it has a stack data option for data tables.
A quick google gives results for “stack data excel.” [One seems to have macros.](http://www.qimacros.com/qiwizard/restack.html) May have to pay for that one though.
---
### Author:  [@TroutMan](https://boards.straightdope.com/u/TroutMan)
#### Post date: [January 31, 2012, 10:59pm UTC](https://boards.straightdope.com/t/excel-can-i-create-a-reverse-pivot-table/611346/3 "2012-01-31T22:59:46Z")
Thanks, it does look like I’d have to pay for that.
Your response also helped me figure out how to search for it, and I found [this](http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-do-i-stack-columns-of-data-in-excel-2007/ba186dd9-b47d-4c57-8921-94ec3e491734) in Excel online help. The macro response is what I figured I’d have to do, but I’m curious to try out the answer using indexes (2nd one down) and see if that works for my case.
---
### Author:  [@mcgato](https://boards.straightdope.com/u/mcgato)
#### Post date: [January 31, 2012, 11:21pm UTC](https://boards.straightdope.com/t/excel-can-i-create-a-reverse-pivot-table/611346/4 "2012-01-31T23:21:13Z")
You are welcome. There are a few things in that page that I haven’t used before, so I will be checking them out.
---
### Author:  [@homeskillet](https://boards.straightdope.com/u/homeskillet)
#### Post date: [January 31, 2012, 11:23pm UTC](https://boards.straightdope.com/t/excel-can-i-create-a-reverse-pivot-table/611346/5 "2012-01-31T23:23:46Z")
Here is a method I have used in the past that worked well for me:
> **[17 MVP tips, tricks and shortcuts for Excel » The Spreadsheet Page](https://spreadsheetpage.com/use-excel-tips-tricks/)**
>
> We reached out to 154 MVPs and asked them to share their best tips, tricks, and shortcuts. Here's what we got back. Let’s venture into expert territory.