I needed to transform a table with data somewhat summarised (see below), back into a database, or data rows.
Thanks to the internet, I uncovered this little trick.
- Press ALT + D + P to bring up the PivotChart wizard (Didn’t know this existed? Me neither.)
- In the PivotTable dialog box, select the Multiple consolidation ranges option
- In Step 2, choose the I will create the page fields option.
- In Step 2b specify your summary table range in the Range field and then Add it.
- Finish off the rest of the wizard
- Select your newly created Pivot table and uncheck ‘Row’ and ‘Column’
- Double click on the single value cell that is left.