Excel: can I create a "reverse" pivot table?

Factual Questions
1

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:



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.



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.

2

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. May have to pay for that one though.

3

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 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.

4

You are welcome. There are a few things in that page that I haven’t used before, so I will be checking them out.

5

Here is a method I have used in the past that worked well for me: