A very common scenario in Sheets, is to pivot tables to aggregate information, but sometimes you might want to turn a matrix into a list of combinations. You will find how to in this article.

Pivoting is underrated

The power of Sheets of any breed is mainly table pivoting, where you have a list of combined values in a log record format, and you can pivot to quickly aggregate and transform, and gain insight.

More often than not people underestimate the power of Sheets, and will go with more sophisticated solutions. This happens all the time. I’ve seen analyst experts in Excel using FITLTER(MATCH(INDEX(… instead of just pivoting. And I see all the time really fancy long SQL Queries to aggregate 5K lines of records. It’s just not worth it.

It’s by far simpler (just not so elegant 👔), to export records to a CSV open in Sheets, and pivot the table.

So you can turn this

Into this

Unpivoting is rarely used

In some rare cases, you might want to do the inverse process and unpivot a matrix you already have into a list of items.

In this example, I needed a list to use as a calendar import, so… there must be a way to unpivot right? Well is not that straight forward, but here is how you to turn this

Into this

=QUERY(ArrayFormula(split(FLATTEN(ARRAYFORMULA(A3:A36&"+"&B2:G2&"+"&B3:G36)), "+")), "SELECT * WHERE Col3 <>''")