Restructuring data in Google Sheets: Column to row conversion

Other Questions Google Sheets
1

Hey everyone! I’m working on a Google Sheets project and I need some help.

I’ve got a big table that’s constantly being updated by someone else. It looks like this:

| Item | Jan | Feb | Mar |
|------|-----|-----|-----|
| A    | 10  | 20  | 30  |
| B    | 15  | 25  | 35  |

What I want to do is transform it into this format:

| Item | Month | Value |
|------|-------|-------|
| A    | Jan   | 10    |
| A    | Feb   | 20    |
| A    | Mar   | 30    |
| B    | Jan   | 15    |
| B    | Feb   | 25    |
| B    | Mar   | 35    |

I was thinking about using the query function in Google Sheets but I’m not sure how to approach this. Any tips or tricks would be super helpful! Thanks guys!

2

hey there! i’ve had to do something like this before. you could try using the QUERY function with FLATTEN. here’s a formula that might work:

=QUERY(FLATTEN(A2:A,B1:D1,B2:D), “select Col1,Col2,Col3 where Col3 is not null label Col1 ‘Item’, Col2 ‘Month’, Col3 ‘Value’”)

it’s pretty simple and should update automatically when ur data changes. good luck!

3

I’ve tackled a similar challenge in my work, and I found that using a combination of ARRAYFORMULA and MELT functions can be incredibly effective for this type of data restructuring.

Here’s a formula that should work for you:

=ARRAYFORMULA(QUERY(MELT(A1:D3, A1:A3, B1:D1, “Item”, “Month”, “Value”), “SELECT * WHERE Col3 IS NOT NULL”))

This approach is quite flexible and will automatically adjust if new columns (months) or rows (items) are added to your original data set.

One thing to keep in mind: if your sheet has a lot of data, this formula might slow down performance a bit. In that case, you might want to consider using Apps Script to create a custom function or trigger that runs periodically to update a separate sheet with the restructured data.

Hope this helps! Let me know if you need any clarification on how to implement this.

4

Having dealt with similar data restructuring tasks, I can suggest using the TRANSPOSE function in combination with some array formulas. First, create a helper column with =TRANSPOSE(B1:D1) to extract the months into a column. Then, use the formula =ARRAYFORMULA({A2:A, TRANSPOSE(B1:D1), TRANSPOSE(B2:D)}) to achieve the desired output. This method is straightforward, updates automatically with your source data, and avoids the complexity of queries. Adjust the ranges as needed if your data layout changes, and ensure there are enough empty rows to accommodate the results.