Unpivot In Google Sheets With Formulas, Or How To Turn Wide Data Into Tall Data

Unpivot in Google Sheets is a method to turn “wide” tables into “tall” tables, which are more convenient for analysis.

Suppose we have a wide table like this:

Wide Data Table

We want to transform that data — unpivot it — into the tall format that is the way databases store data:

Unpviot in Google Sheets

But how do we unpivot our data like that?

It turns out it’s quite hard.

Much harder than going the other direction, pivoting tall data into wide data tables.

This article looks at how to do it using formulas, which is challenging and obtuse.

The formulas are complex and difficult to read so it’s hard to recommend this method in a production setting.

But it’s a fascinating look at advanced formulas in Google Sheets and I’m certain you’ll learn something new along the way.

If you need to do this in a production setting, then you might want to consider using the Apps Script code or example sheet from the first answer of this Stack Overflow post.

But if you’re ready for some complex formulas, let’s dive in…

Unpivot in Google Sheets – Solution 1

We’ll use the wide dataset shown in the first image at the top of this post, in Sheet1 of our Google Sheet.

Remember, what we’re trying to do is transform the wide data table into the tall data table. The output of our formulas should look like the second image in this post.

In other words, we need to create 16 rows to account for the different pairings of Customer and Product, e.g. Customer 1 + Product 1, Customer 1 + Product 2, etc. all the way up to Customer 4 + Product 4.

Of course, we’ll employ the Onion Method to understand these formulas.

Template

Click here to open the Unpivot in Google Sheets template

Feel free to make your own copy (File > Make a copy…).

(If you can’t open the file, it’s likely because your G Suite account prohibits opening files from external sources. Talk to your G Suite administrator or try opening the file in an incognito browser.)

Customers Column

To start, create a second Sheet and add a simple header row in row 1, with “Customer”, “Product” and “Value” in cells A1, B1 and C1 respectively.

Let’s create an array formula to populate the customers column. In cell A2, enter this:

=COUNTA(Sheet1!$1:$1)

This formula gives the count of the number of columns — 4 — in our wide dataset (assuming cell A1 in our original dataset is empty, per the first image of this post).

QUICK NOTE: when copy-pasting these formulas into your own Google Sheets, paste them directly into the formula bar to avoid any issues.

Similarly, this next formula would give the count of the number of rows — 3 — in our wide dataset (again assuming cell A1 in our original dataset is empty, per the first image of this post).

=COUNTA(Sheet1!$A:$A)

Multiplying these two together gives us the number of values in our table — 12 — which corresponds to the number of rows we’ll need in our new tall data table:

=COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1)

So let’s create those 12 rows!

Wrap this with the SEQUENCE function, starting from 1:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)

Now divide that by the count of rows:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)

Hmm, it gives an answer of 0.3333333 but we’ve lost our 12 rows…

…so turn it into an Array Formula silly!

=ArrayFormula(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))

Ah, that’s better. “But how does it help us?” I hear you ask.

Let’s round all those decimals up to the nearest integer, like so:

=ArrayFormula(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)))

Nice!

We now have the column vector 1,1,1,2,2,2,3,3,3,4,4,4 with repeating positions, which is exactly what we needed.

For the moment, leave this formula alone and let’s move to cell B2 to construct the next piece. We want to create a table of the column headings that we can “lookup” with those repeating positions. Don’t worry, it’ll make more sense in a moment!

Ok, so start with this formula in B2:

=ArrayFormula(COLUMN(Sheet1!$1:$1))

And try this formula in B3:

=ArrayFormula(Sheet1!$1:$1)

Can you see what we’re doing yet?

Let’s combine these in cell B2 as follows:

=ArrayFormula({COLUMN(Sheet1!$1:$1);Sheet1!$1:$1})

and delete the formula in cell B3.

The output should look the same, but it’s created with a single formula.

Now we can use the HLOOKUP function to lookup those positions into this data array we’ve created.

Change our formula in cell A2 to:

=ArrayFormula(HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A)),{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2))

It’s nearly right, but the answer is offset slightly. Hmm.

Ah ok, it’s that blank cell in A1 of the original data that we didn’t account for. Our repeating positions really start from 2. It’s a simple fix to just add 1 to them.

=ArrayFormula(HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1,{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2))

That’s the customers populated in column 1.

What about the products in column 2?

Products Column

Well, it’s an almost identical formula, so I’ll just share it here and leave it to the reader to use the Onion Method to build it in steps.

Actually, no I won’t, that’s just me being lazy. Let’s walk through it together.

It’s a similar idea, but it looks a little different because we do a vertical lookup.

So, if you haven’t already, clear out cells B2 and B3.

Start with this SEQUENCE formula in cell B2:

=SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)

This time we want a sequence that looks like 1,2,3,1,2,3,1,2,3 etc. i.e. repeating. This calls for the MOD squad, I mean MOD function.

=MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1),COUNTA(Sheet1!$A:$A))

Oops, make it an Array Formula:

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1),COUNTA(Sheet1!$A:$A)))

Ah, that’s better. But it gives 1,2,0,1,2,0 etc. so it’s not quite right. Fix the ordering by subtracting 1 from the dividend of the MOD function:

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A)))

Now we have 0,1,2,0,1,2 etc.

Add 2 to this to get the repeating positions we want 2,3,4,2,3,4 (again, we start from 2 to account for the blank cell in A1 of our original dataset).

=ArrayFormula(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2)

Leave this formula sitting pretty for a moment, and begin a new one in cell C2. Build an array for the vertical lookup with this formula (feel free to build in steps, I’m jumping straight to the array version):

=ArrayFormula({ROW(Sheet1!$A:$A),Sheet1!$A:$A})

Now we can combine this into the formula in cell B2, using a VLOOKUP:

=ArrayFormula(VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A:$A),Sheet1!$A:$A},2))

Woohoo!

There’s our products in repeating order and paired correctly with the customer column.

That leaves the values associated with each pair.

Values Column

Thankfully this is much simpler, using a standard INDEX / MATCH / MATCH construction to look up each pair.

The row offset in the INDEX function is found by matching the product with the product categories in column A of our original data, i.e.

=MATCH(B2,Sheet1!$A:$A,0)

The column offset is found by matching the customers, i.e.

=MATCH(A2,Sheet1!$1:$1,0)

Plug these both into the INDEX function:

=INDEX(Sheet1!$1:$1000,MATCH(B2,Sheet1!$A:$A,0),MATCH(A2,Sheet1!$1:$1,0))

which gives the value of 61 for the first pair, Customer 1 and Product 1.

Drag this formula down the column to fill in all the rows.

“Wait, what? Where’s the array formula? Can’t I just wrap this INDEX / MATCH / MATCH with an array formula wrapper?”

No bueno, I’m afraid.

The INDEX function does not play well with the Array Formula, so this option cannot be turned into an array formula.

Be patient, in solution 2 we’ll generalize this to use an array formula, but we have to approach it in a different, more verbose way.

Unpivot in Google Sheets – Solution 2

Leaving the Customer and Product array formula columns well alone, let’s focus purely on the Values column.

We left solution 1 with a somewhat unsatisfactory INDEX / MATCH / MATCH formula for the values that, ahem, had to be dragged down the column — oh the horror! — because it wasn’t an array formula.

Gasp! We don’t like such manual work.

So let’s create an array formula to grab the values we need.

Think of the standard VLOOKUP:

=VLOOKUP( search_key, data, column_index, false )

The search_key is the repeating array 2,3,4,2,3,4,2,3,4 etc. created using the same formula construction as the first part of the Products formula from Solution 1.

The column_index is the repeating array 2,2,2,3,3,3,4,4,4, etc. created using the same formula construction as the first part of the Customers formula from Solution 1.

When you plug these into the VLOOKUP, you’re searching for 2 and returning column 2, then searching for 3 returning column 2, searching 4 returning column 2, then searching 2 returning column 3, etc.

In other words, traversing the array of values and grabbing each one in turn.

The data needs to be setup by adding a search column at the front, which is done using the curly brackets array literal construction, like so:

=ArrayFormula({ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000})

All that’s left is to combine them into the VLOOKUP, like so:

=ArrayFormula(VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1,2)-2,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000},ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1))

Voila! Clear as mud, huh?

Unpivot in Google Sheets – Solution 3

In this solution, all we do is combine the three columns together into a single, giant array formula, using the curly bracket array literal construction.

Starting with the three columns combined:

= ArrayFormula({ Customer_Formula, Product_Formula, Values_Formula })

Next, we’ll wrap it with a QUERY function to remove null values:

= ArrayFormula( QUERY( { Customer_Formula, Product_Formula, Values_Formula } , "SELECT * WHERE Col3 IS NOT NULL" ))

The full array construction, with a static header row added, is:

=ArrayFormula( {"Customer","Product","Value";
QUERY( { Customer_Formula , Product_Formula , Values_Formula } , "SELECT * WHERE Col3 IS NOT NULL" )})

We can then simply plug in the Customer_Formula, Product_Formula and Values_Formula to create a one-stop shop for unpivoting our data:

=ArrayFormula({"Customer","Product","Value"; QUERY({HLOOKUP(ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1,{COLUMN(Sheet1!$1:$1);Sheet1!$1:$1},2),
VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)-1,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A:$A),Sheet1!$A:$A},2),
VLOOKUP(MOD(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1,2)-2,COUNTA(Sheet1!$A:$A))+2,{ROW(Sheet1!$A$2:$A),Sheet1!$B$2:$1000},ROUNDUP(SEQUENCE(COUNTA(Sheet1!$A:$A)*COUNTA(Sheet1!$1:$1),1)/COUNTA(Sheet1!$A:$A))+1)
},"SELECT * WHERE Col3 IS NOT NULL")})

Unpivot in Google Sheets – Solution 4

This one really blew my mind when I first saw it and picked it apart.

Kudos to this person on Stack Overflow for the original amazing answer.

I’ve modified it slightly, but have merely contributed a minor update to an ingenious and original solution.

Here it is, in all it’s mysterious detail:

=ArrayFormula({"Customer","Product","Value";
QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )), , 500000)), , 500000)),"🌶"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)})

First off, what on earth are those fish 🐠 and chili peppers 🌶 doing in this formula? Is this some kind of joke?

No, no, my friend. Read on and you’ll find out!

But before we do that, let me show you the amazing trick with the QUERY function that is key to this formula.

Taking our dataset again:

Unpivot in Google Sheets wide table

Try this formula in cell H1:

=QUERY(A1:E4,"SELECT A",4)

See what it does?

Query headers trick

It joins the values in column A into a single string, because we’ve told the QUERY function to treat all 4 rows as headers. Crazy!

Even better, you can skip the SELECT statement altogether, like this:

=QUERY(A1:E4,,4)

which results in all of the columns being concatenated:

Query headers trick

Now that is interesting!

And it’s at the heart of how this crazy formula works.

Let’s build it up in steps, following the Onion Method.

The innermost IF function is (note the value_if_false argument is empty):

=ArrayFormula(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", ))

which gives the following output:

Array If formula

For every row of data, the formula joins them such that each cell has a unique combination of product, customer and value.

Next we transpose this array and join using the funky QUERY-header row trick above:

=ArrayFormula(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000))

This gives a #REF! error, with the message “Result was not automatically expanded, please insert more columns (699).”

The array output is too wide for our current Sheet.

Wrap it with a transpose function to fix this and get all the data in a single column:

=ArrayFormula(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000)))

Use a second QUERY function with this headers trick to bring these values together:

=ArrayFormula(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000)),,500000)))

Now we basically just split this up based on the fish “🐠” and chili pepper “🌶” symbols that we used to separate the data packets.

Here’s the first split and transpose:

=ArrayFormula(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000)),,500000)),"🌶")))

By now, our data looks like this, which is getting closer:

Unpivot in Google Sheets

Use the TRIM function to fix those unsightly spacing issues.

Next, split it again across the tropical fish:

=ArrayFormula(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000)),,500000)),"🌶"))),"🐠"))

Unpivot in Google Sheets

Nearly there now!

Remove the #VALUE! error with an IFERROR wrapper function. Use a QUERY wrapper to re-order the rows and columns as required. The OFFSET removes a blank row from showing up in the table. The formula now looks like this:

=ArrayFormula(QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )),,500000)),,500000)),"🌶"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1"))

And the output like this:

Split function Google Sheets

The final step is borrowed from Solution 3 above, namely combining a static header row with array literals.

=ArrayFormula({"Customer","Product","Value"; MAIN_FORMULA })

Now we can insert our formula into this construction, in place of the MAIN_FORMULA placeholder:

=ArrayFormula({"Customer","Product","Value";
QUERY(IFERROR(SPLIT(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(Sheet1!B2:Z<>"", Sheet1!A2:A&"🐠"&Sheet1!B1:1&"🐠"&Sheet1!B2:Z&"🌶", )), , 500000)), , 500000)),"🌶"))),"🐠"),""),"SELECT Col2, Col1, Col3 ORDER BY Col2 OFFSET 1",0)})

Crazy formula in Google Sheets

Further Reading

For more information on the shape of datasets, have a read of Spreadsheet Thinking vs. Database Thinking.

Leave a Reply

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