How to Unpivot Data in Google Sheets

Transforming messy data into something neat and analyzable is a quiet superpower for any marketer or sales professional. You often get data that's wide and sprawling, with categories or time periods stretched across columns, making it a nightmare to summarize. This article will show you exactly how to "unpivot" your data in Google Sheets - turning it from a wide, hard-to-use format into a tall, tidy list that's perfect for dashboards and pivot tables.

What is Unpivoting Data (and Why Does It Matter)?

Unpivoting, sometimes called "normalizing," flips your data structure. Instead of having descriptive headers for your columns (like months, regions, or product categories), you turn those headers into their own dedicated column.

Imagine your data looks like this, a typical "wide" format common in exports:

BEFORE (Wide Data):

• Column A: Campaign Name
• Column B: Clicks Jan
• Column C: Clicks Feb
• Column D: Clicks Mar

This format is easy for humans to scan, but terrible for spreadsheet functions, charts, and pivot tables. If you add a new month, you have to add a new column and update all your formulas. Creating a simple chart showing clicks over time is surprisingly clunky.

After unpivoting, your data looks like this, a much cleaner "tall" format:

AFTER (Tall / Unpivoted Data):

• Column A: Campaign Name
• Column B: Month
• Column C: Clicks

See the difference? We now have a clean, database-style list. This structure is incredibly flexible and is the format most analysis tools expect. With tall data, you can:

• Build a Pivot Table in Seconds: Drag 'Month' into the columns area, 'Campaign Name' into rows, and 'Clicks' into values. Done.
• Use the QUERY or FILTER Functions Easily: You can write simple formulas like QUERY(data, "select B, sum(C) group by B") to get total clicks by month. This is impossible with the wide format.
• Create Dynamic Charts: A single chart can now visualize performance over time, and it will automatically update as you add new rows for subsequent months. No need to constantly adjust chart ranges.

Now that you know the why, let's get into the how. We'll cover two powerful, formula-based methods to do this automatically in Google Sheets.

Method 1: The Stack and QUERY Combination

This is a classic and robust method that works by manually stacking each of your data columns on top of each other using curly braces {} and then cleaning it up with the QUERY function.

Let's use a sample dataset. Pretend your sales data is in a sheet named 'Monthly Sales' that looks like this, with fixed identifiers in column A and the values you want to unpivot in columns B, C, D, and E.

A1: Product | B1: Jan | C1: Feb | D1: Mar | E1: Apr A2: Product Alpha | B2: 150 | C2: 175 | D2: 200 | E2: 180 A3: Product Beta | B3: 95 | C3: 110 | D3: 105 | E3: 120 A4: Product Gamma | B4: 210 | C4: 230 | D4: 250 | E4: 225

Step 1: Understand Array Literals {}

In Google Sheets, curly braces {} let you build custom arrays or tables directly inside a formula. A comma , separates columns, and a semicolon , separates rows. For example, {1, 2, 3, 4} creates a 2x2 grid with 1, 2 in the first row and 3, 4 in the second. We'll use this to stack our data vertically.

Step 2: Build the Stacked Array

In a new tab or an empty area of your sheet, we will build a formula that creates a new virtual table. For each month, we need to grab the Product column, the Sales values for that month, and then add a new column containing the month's name.

Here’s how you'd construct it for January:

{'Monthly Sales'!A2:A, 'Monthly Sales'!B2:B, "Jan"}

But there's an issue. This formula only works if the ranges are the same size. If someone types "Jan" directly into the formula, it's only one cell, which will cause an error when combined with the A2:A range. The fix is to use a trick with ARRAYFORMULA and IF to create a column of "Jan" text that is the same height as our data:

{'Monthly Sales'!A2:A, 'Monthly Sales'!B2:B, ARRAYFORMULA(IF('Monthly Sales'!A2:A<>"", "Jan", ""))}

Now, we just need to stack each month on top of the other using the semicolon ,.

The full stacked array looks long, but the pattern is simple repetition:

={ {'Monthly Sales'!A2:A, 'Monthly Sales'!B2:B, ARRAYFORMULA(IF('Monthly Sales'!A2:A<>"", "Jan", ""))}, {'Monthly Sales'!A2:A, 'Monthly Sales'!C2:C, ARRAYFORMULA(IF('Monthly Sales'!A2:A<>"", "Feb", ""))}, {'Monthly Sales'!A2:A, 'Monthly Sales'!D2:D, ARRAYFORMULA(IF('Monthly Sales'!A2:A<>"", "Mar", ""))}, {'Monthly Sales'!A2:A, 'Monthly Sales'!E2:E, ARRAYFORMULA(IF('Monthly Sales'!A2:A<>"", "Apr", ""))} }

Step 3: Clean up with QUERY

Pasting the formula above will give you the right data, but it will also include lots of empty rows from the blank parts of your A:A, B:B, etc., ranges.

That's where QUERY comes in. We wrap our entire array formula in QUERY to filter out any rows where the sales value column is blank.

Since we're querying a virtual table created by our formula, we refer to the columns as Col1, Col2, Col3, etc.

The Final Formula:

=QUERY({ {'Monthly Sales'!A2:A, 'Monthly Sales'!B2:B, ARRAYFORMULA(IF('Monthly Sales'!A2:A<>"", "Jan", ""))}, {'Monthly Sales'!A2:A, 'Monthly Sales'!C2:C, ARRAYFORMULA(IF('Monthly Sales'!A2:A<>"", "Feb", ""))}, {'Monthly Sales'!A2:A, 'Monthly Sales'!D2:D, ARRAYFORMULA(IF('Monthly Sales'!A2:A<>"", "Mar", ""))}, {'Monthly Sales'!A2:A, 'Monthly Sales'!E2:E, ARRAYFORMULA(IF('Monthly Sales'!A2:A<>"", "Apr", ""))} }, "select * where Col2 is not null")

And there you have it! A perfectly unpivoted table that automatically updates when you change the original data.

Pros: This method is very explicit and easy to troubleshoot because you can see each stacked block. It's compatible with all versions of Google Sheets. Cons: The formula gets very long and repetitive if you have dozens of columns to unpivot. You also have to manually type the name of each month/category.

Method 2: The SPLIT, FLATTEN, and QUERY Trio

This is a more modern and scalable approach that uses a few clever functions, including the powerful FLATTEN function. It looks a little more complex at first, but once you understand it, it's actually much easier to maintain, especially for tables with many columns.

We'll use the same sample data from Method 1.

The core idea here is to create two main virtual columns: one with the repeating product names, and another with the combined month/value pairs. Then we'll split the month/value column into two.

Step 1: Combine Headers and Values Then FLATTEN

The FLATTEN function is designed for this exact problem. It takes a two-dimensional range of cells and collapses it into a single vertical column.

First, we'll virtually "join" each month header with every corresponding sales figure below it, using a unique character like the pipe | as a separator. The syntax 'Monthly Sales'!B1:E1 & "|" & 'Monthly Sales'!B2:E references the headers in row 1 and all the data below in columns B through E.

'Monthly Sales'!B1:E1 & "|" & 'Monthly Sales'!B2:E

This creates a virtual grid where each cell contains something like "Jan|150" or "Feb|175". When we wrap this in FLATTEN, it turns that grid into one long column:

=FLATTEN('Monthly Sales'!B1:E1 & "|" & 'Monthly Sales'!B2:E)

Now we just need to split these items back out into their own columns.

Step 2: Use SPLIT

We can wrap the result from step 1 in ARRAYFORMULA and SPLIT to break each "Month|Value" item back into two columns:

=ARRAYFORMULA(SPLIT(FLATTEN('Monthly Sales'!B1:E1 & "|" & 'Monthly Sales'!B2:E), "|"))

Step 3: Create the Repeated "Identifier" Column

We need a corresponding column of product names. We can do this in a single formula by telling Google Sheets to create columns by splitting repeated text. First, we REPT (repeat) each product name by the number of months in our header (4 in this case, COUNTA(B1:E1) makes this dynamic).

The formula REPT('Monthly Sales'!A2:A&"|", COUNTA('Monthly Sales'!B1:E1)) will create results like: "Product Alpha|Product Alpha|Product Alpha|Product Alpha|".

We wrap this in ARRAYFORMULA, SPLIT it by the "|" delimiter SPLIT(..., "|"), and then FLATTEN it to turn it from a wide grid into a single, clean column of repeated product names.

=FLATTEN(ARRAYFORMULA(SPLIT(REPT('Monthly Sales'!A2:A&"|", COUNTA('Monthly Sales'!B1:E1)), "|")))

Step 4: Combine and Clean Up

Now we combine our generated columns using the curly braces {} and wrap it all in QUERY to filter out any blank rows.

The Final Formula:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN('Monthly Sales'!A2:A & "|" & 'Monthly Sales'!B1:E1 & "|" & 'Monthly Sales'!B2:E), "|"), "select * where Col3 is not null"))

Actually, there's an even more elegant way to combine these steps into one super-formula. This version nests the logic so you create one large flat list first and then split it.

=ARRAYFORMULA(SPLIT(FLATTEN('Monthly Sales'!A2:A & "|" & 'Monthly Sales'!B1:E1 & "|" & 'Monthly Sales'!B2:E), "|"))

Then, simply wrap that impressive formula in a QUERY to remove any lingering blank or zero-value rows.

The Final Optimized Formula:

=QUERY( ARRAYFORMULA(SPLIT(FLATTEN('Monthly Sales'!A2:A & "|" & 'Monthly Sales'!B1:E1 & "|" & 'Monthly Sales'!B2:E), "|")), "select * where Col1 is not null and Col3 is not null" )

Pros: This formula is far more scalable. If you add more months to your source data (e.g., column F for May, G for June), you only need to change E1 to G1 and E to G in one place. No lengthy stacking needed. Cons: The formula is less intuitive at first glance. It relies on the FLATTEN function, which might not feel familiar to all users.

Choosing the Right Method for You

So, which approach should you use?

• For tables with just a few columns that rarely change, Method 1 (QUERY and {}) is perfectly fine and might be conceptually easier to grasp.
• For tables with many columns or a dynamic number of columns that you may add to over time, Method 2 (FLATTEN and SPLIT) is the superior choice. It will save you a massive amount of time in the long run.

Don't Forget Headers!

One final tip: both of these formulas create the data output, but they don't create headers. You can easily add them manually in the row above your formula. Or, for a fully automated solution, you can add them to the formula itself by stacking a header row right inside:

={"Product", "Month", "Sales", QUERY(...your previous formula here...)}

Mastering this skill turns Google Sheets from a simple grid into a powerful data manipulation tool, letting you prepare any messy export for proper analysis, visualization, and reporting.

Final Thoughts

Unpivoting data is a fundamental step in data preparation that turns confusing, wide tables into clean, analysis-ready lists. While Google Sheets provides a couple of powerful, formula-based ways to accomplish this, the process itself highlights the manual, time-consuming work often required before you can even begin asking questions of your data.

We designed Graphed to eliminate these manual steps entirely. Instead of struggling with formulas to wrangle exported CSVs, you can connect directly to your data sources like Google Analytics, Shopify, or Facebook Ads. We handle the data structuring automatically, letting you go from raw data to a real-time dashboard faster. Just describe what you want to see in plain English, and our tool builds the charts and calculates the metrics for you, skipping the unpivoting headache altogether.