Hello all and welcome back to the Excel Tip of the Week! This week, we have a Creator level post in which we’re looking at how to take a column of data and unfold it into a table – or to do the reverse and flatten a table into a single column. These are some common data transformation tasks but there’s no single feature or function for doing them (with one notable exception) – but both are possible with some careful work.
Unfolding a column
Here’s our first example:
We have some data, exported from some system or other, that has unhelpfully returned everything in a single column. We want to unfold this into a proper table format so we can analyse and work with it going forward.
The key insight here is that there is a regular pattern – the four columns of data (and in our example one blank) repeat every five rows. We are going to transform this data using an INDEX formula, which will pull the nth row, based on the row/column that the formula is in.
For the easiest possible example, we are going to use an INDEX on a separate sheet that starts in A1. If your table starts somewhere else, you will need to adjust the ROW/COLUMN formulas in the following appropriately.
To make this work, each column we move to the right must represent moving one row further down the data, and each row we move down the table must represent moving five rows down (to the next data set). We end up with:
=INDEX('Unfolding before'!$A$1:$A$24,5*(ROW()-1)+COLUMN())
This can be copied down and right to produce our desired output:
We can then paste-values over these formulas to create a final tabular set of data.
It’s also possible to do this using Power Query – check out the accompanying file for a demo of that approach.
Flattening a table
Sometimes, we might need to do the reverse – taking a 2D table and flattening it into a single column. This can be necessary for e.g. inputting into one of those same annoying systems that outputs the kind of flat data we were working on unfolding previously.
For maximum consistency, we’ll use the exact same data as before, and show how to flatten it back again (although we’re going to skip the blank rows). Essentially we are going to use the same approach with an INDEX and a use of ROW, but now we need to be a bit more careful about how we treat the ROW. We need to supply both a row number and a column number to the INDEX. If we think about it, these need to go as follows:
Row numbers: 1, 1, 1, 1, 2, 2, 2, 2, 3…
Column numbers: 1, 2, 3, 4, 1, 2, 3, 4, 1…
These patterns can both be made using some simple other functions. To get the repeating numbers, we will divide the row number by the length of the pattern – in this case 4 – and then round up to the next integer with ROUNDUP. For the repeating pattern, we can use MOD – although we need to do a little manipulation as MOD(ROW(), 4) would give us 1, 2, 3, 0, 1… and we want 1, 2, 3, 4, 1…
The final formula (again starting at A1) is:
=INDEX('Flatten before'!$A$1:$D$5,ROUNDUP(ROW()/4,0),MOD(ROW()-1,4)+1)
This is also possible in Power Query – in fact, it’s much easier. You just demote the headers, unpivot the whole table, and then delete the excess column. Once again, you can see this in the attached file.
There’s one more way to flatten data – in Google Sheets, with the dedicated FLATTEN function:
Hopefully this one will come to Excel some day – and perhaps we can get an UNFOLD function as well!
As a reminder, you can check out both the formula and Power Query solutions to both problems in this file.
Excel community
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.
