Dynamic restructure (wide -> long) in Excel

Posted on


I have a table in wide format, here’s a simplified version:

enter image description here

I’ve restructured it to long format like this (using Tableau add in):

enter image description here

The thing is, I’ve had to start over with the restructure every time the table is updated.
My question is if there is a way to create the long format table using formulas so it’s dynamic and will update with the data?

I can’t use power-query as suggested in some answers because this is part of a project that’s going to sit on a few different computers where I cannot rely on Power-Query being installed/activated. I already created a VBA program to do this before I found out I couldn’t use macros in some of the computers where I’ll be sending this. Therefore I really need to do this with good old built-in excel functions only.

The actual data has two more category variables and about 40 more data columns, but the structure is constant, only the data within will be updated.


Use PowerQuery to get the result:

enter image description here

You can refresh directly if you change the data source Table.

Hopefully you found an answer but for anyone else googling this there are some formulas halfway down this page: https://trumpexcel.com/source-data-for-pivot-table/ that can be used instead of Power Query.

Leave a Reply

Your email address will not be published. Required fields are marked *