Tall data can be more efficient for storing and transferring than wide data. In [[pandas]] this is called [[melt]]. However, Google Sheets does not have a built-in function for transforming wide data to tall data. I've experimented with [[base/Google Apps Script/Google Apps Script]] functions for this but they are very slow. [[Ben Collins]] has a great explainer on a (recently updated) approach [here](https://www.benlcollins.com/spreadsheets/unpivot-in-google-sheets/). In the below example, `data!B1:D1` represents the header row, where each header (or column name) represents a variable name. `data!A2:A` represent the row labels. `dataB2:D` contains the data. ``` =ArrayFormula(SPLIT(FLATTEN(data!A2:A&"🦊"&data!B1:D1&"🦊"&data!B2:D),"🦊")) ``` > [!Tip] > You can include any number of identifier columns in your result by including them after `&data!A2:A&"🦊"` like `&data!A2:A&"🦊"&data!B2:B&"🦊"`. This will take a table like this | | A | B | C | D | | --- | --------- | ---------- | ---------- | ---------- | | 1 | | Customer 1 | Customer 2 | Customer 3 | | 2 | Product 1 | 61 | 97 | 98 | | 3 | Product 2 | 37 | 20 | 92 | | 4 | Product 3 | 35 | 98 | 67 | and transform it to | | A | B | C | | --- | --------- | ---------- | --- | | 1 | Product 1 | Customer 1 | 61 | | 2 | Product 1 | Customer 2 | 97 | | 3 | Product 1 | Customer 3 | 98 | | 4 | Product 2 | Customer 1 | 37 | | 5 | Product 2 | Customer 2 | 20 | | 6 | Product 2 | Customer 3 | 92 | | 7 | Product 3 | Customer 1 | 35 | | 8 | Product 3 | Customer 2 | 98 | | 9 | Product 3 | Customer 3 | 67 | Paste the formula in cell A2 of a new sheet and make sure to include headers in the return table. For indefinite ranges, you can wrap the output in a `QUERY` function and drop null values. ``` =ArrayFormula(QUERY( SPLIT(FLATTEN(data!A2:A&"🦊"&data!B1:D1&"🦊"&data!B2:D),"🦊"), "SELECT * WHERE Col3 IS NOT NULL" ) ) ``` > [!Warning] > If the return is not as expected, it may be because QUERY reacts unexpectedly when a column has mixed data types. Constrain each column to one data type.