Unpivoting data in Google Sheets is a process of converting a wide format table (where data is spread across multiple columns) into a long format table (where the data is presented in rows).
If you have a simple dataset and want to unpivot it without scripting, you can use a combination of FLATTEN, SPLIT, and array formulas. This method works well for smaller datasets and requires a bit of manual setup.
Assuming you have a dataset where A1 is the top-left cell:
Determine the range of your data that you want to unpivot. Let's say your data is in A2:C4, where A2:A4 are your row identifiers, and B1:C4 are the columns you want to unpivot.
In a new column, concatenate the row identifier with each value you want to unpivot, using a unique separator. For example, in A7, you might use:
=ArrayFormula(B1:C1&" | "&A2:A4&" | "&B2:C4)
To convert the combined data into a single column, wrap the previous formula with the FLATTEN function:
=ArrayFormula(FLATTEN(B1:C1&" | "&A2:A4&" | "&B2:C4))
This step stacks all the data into one tall, single column, as shown above.
To finalize the unpivot process, you'll split the flattened data into separate columns:
=ArrayFormula(SPLIT(FLATTEN(B1:C1&"|"&A2:A4&"|"&B2:C4),"|"))
The SPLIT function here uses the | character to divide the combined data back into distinct columns: one for your original column headings, one for your row headings, and one for the data points.
And that's it! You've successfully transformed your data from a wide format to a long format using Google Sheets, without needing any special scripts or tools, just clever use of formulas.
We hope that this article has helped you and given you a better understanding of how to unpivot a table in Google Sheets. If you enjoyed this article, you might also like our articles on how to set up conditional formatting for dates within 30 days in Google Sheets and how to make a tournament bracket in Google Sheets.
