Unpivot Data in Google Sheets (Reverse Pivot Formula Explained)

In this tutorial, you’ll learn two simple ways to unpivot data in Google Sheets. One method uses the SPLIT function — which can mess with formatting like hyperlinks — and the other keeps your original formatting intact. Both are beginner-friendly and work great for converting wider data to taller data in Google Sheets.

Table of Contents

What Does It Mean to Unpivot Data in Google Sheets?

If your data is spread out across columns (like years, months, or categories), it’s considered “pivoted.” When you unpivot it — also called a reverse pivot in Google Sheets — you rearrange it so those values stack vertically in rows.

Here’s what that looks like:

Pivoted data:

Country	2019	2020	2021	2022
China	133.6	134.3	136.9	137.7
India	103.6	107.9	109.6	107.7
Russia	74.5	85.9	76.1	104.2
United States	52.6	49.8	44.8	44.9

Unpivoted data:

Let’s look at two ways to make this transformation.

Note:
If a category cell is completely empty, the formula will skip that row — even if values are present in other columns. To make sure such rows are included, insert a placeholder like a hyphen (-) in the category cell. This tells the formula to treat it as non-empty and include it in the unpivoted output.

Method 1: Easy Formula to Unpivot Data in Google Sheets (But Loses Formatting)

This is the quicker method. But here’s the catch — it combines values and splits them later, which can break formatting like dates, currencies, and links.

So while it’s a solid go-to for quick jobs, it’s not ideal if you need to unpivot and keep formatting in Google Sheets.

Here’s the formula:

=LET(
  cat, A2:A,
  val, B2:E,
  val_header, B1:E1,
  combined, FILTER(cat & "|" & val_header & "|" & val, cat <> ""),
  flattened, FLATTEN(combined),
  ARRAYFORMULA(SPLIT(flattened, "|"))
)

What This Formula Does

Joins Country, Year, and Value using a | separator
Filters out empty rows
Flattens everything into a single column
Then splits the text back into three separate columns

Simple — but again, formatting like hyperlinks won’t survive.

Unpivoting Data with Two Category Columns in Google Sheets

Let’s say you also want to include the Continent for each country.

Here’s an updated dataset:

Country	Continent	2019	2020	2021	2022
China	Asia	133.6	134.3	136.9	137.7
India	Asia	103.6	107.9	109.6	107.7
Russia	Asia / Europe	74.5	85.9	76.1	104.2
United States	North America	52.6	49.8	44.8	44.9

And here’s the modified formula:

=LET(
  cat, A2:A,
  subCat, B2:B,
  val, C2:F,
  val_header, C1:F1,
  combined, FILTER(cat & "|" & subCat & "|" & val_header & "|" & val, cat <> ""),
  flattened, FLATTEN(combined),
  ARRAYFORMULA(SPLIT(flattened, "|"))
)

Only change? We’re now combining both category columns before splitting them.

Unpivoted data with category and subcategory in Google Sheets — Image #2

Method 2: Unpivot and Keep Formatting in Google Sheets

If your data includes formatting like hyperlinks or currency, and you want to preserve it, this method is the way to go. It doesn’t use SPLIT, so nothing gets stripped. You’ll just use a few neat formulas, and everything stays intact.

Let’s say you’re using the same sample dataset from earlier, located in columns A to E, with:

Column A: Country (Category)
Columns B to E: Yearly data (Values)

You’ll use three formulas to convert wider data to taller data in Google Sheets — one each for Country, Year, and Value.

In Cell G2 (Country / Category):

=ArrayFormula(FLATTEN(IF(COLUMN(B1:E1), TOCOL(A2:A, 1))))

What it does:
Duplicates each country name for every year column — so “China” appears once for each year in the dataset. The result is a vertical list that matches the number of data points.

In Cell H2 (Year):

=ArrayFormula(TOCOL(IF(TOCOL(A2:A, 1) <> "", B1:E1)))

What it does:
Repeats the year headers (2019, 2020, etc.) for each country, stacking them into a single column to align with the values.

In Cell I2 (Value):

=ARRAY_CONSTRAIN(FLATTEN(B2:E), COUNTA(G2:G), 1)

What it does:
Flattens all the actual values into one column and trims the list to match the number of rows in your unpivoted result (based on the country column in G).

This gives you a clean, unpivoted output — with formatting intact.

Two Category Columns? Here’s What You Do

If you have both a Category (e.g., Country) and a Subcategory (e.g., Continent), you’ll just need one more formula.

| A (Country) | B (Continent) | C–F (Years) |

Use:

=ArrayFormula(FLATTEN(IF(COLUMN(C1:F1), TOCOL(A2:A, 1))))  // Country
=ArrayFormula(FLATTEN(IF(COLUMN(C1:F1), TOCOL(B2:B, 1))))  // Continent
=ArrayFormula(TOCOL(IF(TOCOL(A2:A, 1) <> "", C1:F1)))       // Year
=ARRAY_CONSTRAIN(FLATTEN(C2:F), COUNTA(H2:H), 1)            // Value

Done!

Why Reverse Pivot a Dataset in Google Sheets?

So why bother unpivoting your data?

Because taller data is easier to work with. Once your dataset is unpivoted, you can use powerful functions like:

SUMIF
QUERY
FILTER
VLOOKUP
Charts
Dashboards

Here are two quick examples to show what you can do:

1. Use SUMIF to get totals

Want to know how much wheat China produced from 2019 to 2022?

=SUMIF(G2:G, "China", I2:I)

This formula adds up all values in column I where the country in column G is “China”.

👉 For the sample dataset used here, scroll up to Image 1.

2. Use QUERY to group and sum by country

=QUERY(G2:I, "SELECT Col1, SUM(Col3) WHERE Col1 IS NOT NULL GROUP BY Col1")

This groups the data by country (column G) and totals the values (column I) for each one.

Result:

Country	Total
China	542.5
India	428.8
Russia	340.7
United States	192.1

👉 This also uses the sample dataset shown in Image 1.

Conclusion

So that’s how you unpivot data in Google Sheets — whether you’re after a quick formula or one that keeps formatting safe.

Both methods are useful depending on your need. Just pick the one that works best for your data.

Sample Sheet

21 COMMENTS

Ravi September 29, 2021 At 4:41 pm

Hi Prashanth,

Thanks a lot for your post. It helped me a lot and worked well. I can’t thank you enough.

It worked very well until I started receiving files which don’t have all the values filled.

Current Formula:

=ARRAYFORMULA( split(flatten(A2:A11&"|"&B2:B11&"|"&C2:C11&"|"&D1:K1&"|"&D2:K11) ,"|"))

Expected: I don’t want any row which has null in the value columns.

- Prashanth September 30, 2021 At 2:04 pm
  
  Hi, Ravi,
  
  You can try the below formula.
  
  =query( ARRAYFORMULA( split( flatten(A2:A11&"|"&B2:B11&"|"&C2:C11&"|"&D1:K1&"|"&D2:K11) ,"|")), "Select * where Col5 is not null")
  
  The outer Query will filter out the unwanted rows.
  
Vic June 26, 2021 At 1:15 am

Hi, thank you so much for this article! It is fantastic!
I’m trying to implement something like the final example where there is more than one fixed column, but it is also an infinite range.
When I try to apply the note for the infinite range to the multiple columns concept, it keeps giving me a literal array error. I’m not sure what I’m doing wrong.

Could you show an example formula for the multiple fixed columns with infinite range added?

- Prashanth June 27, 2021 At 12:35 pm
  
  Hi, Vic,
  
  In my example sheet, in the tab “More than one Fixed Column,” I have added some notes in the range M1:N5. That will guide you in the correct direction.
  
  - Vic June 29, 2021 At 10:13 pm
    
    OMG! THANK YOU SO MUCH! This was exactly what I needed and worked beautifully in my sheet! Can’t thank you enough! This article has saved my team so much work!
    
Aayush June 17, 2021 At 11:53 pm

Hi, This is really great. I was looking for something like this. Now I have an issue that I have multiple Value fields also.

So my data is like 13 fixed fields, 50 value fields (10 value fields with 5 fields each). You can also see it as a multiple-layer row.

Any solution for that?

- Prashanth June 20, 2021 At 9:22 am
  
  Hi, Aayush,
  
  Can you show me a sample of your data and the result you are expecting? I just want 3 fixed fields, 3 value fields, and their sub.
  
  You can leave the URL via comment.
  
Franklin Lima May 14, 2021 At 11:15 pm

Just here to say it was so helpful and enlightening. Thanks, y’all!

Elisa Tormes January 14, 2021 At 12:52 pm

Great solution! Thank you very much!!! I have noticed that some values when unpivoting several fixed columns get a blank space added at the beginning of the string. Looks like it happens to you too. Do you know how to fix it? Thanks!!!!

- Prashanth January 15, 2021 At 9:33 am
  
  Hi, Elisa Tormes,
  
  It’s because of the QUERY.
  
  We can solve it by using the TRIM function within the formula as below.
  
  =ARRAYFORMULA(trim(split(flatten(transpose(query(transpose(A2:B6&"|") ,,9^9))&"|"&C1:F1&"|"&C2:F6),"|")))
  
  It has one issue though!
  
  Adding TRIM will convert the numeric column into a text column.
  
  In my case, the formula will convert columns J and K in the unpivot data to text columns.
  
  So in such case, if you use those columns in other calculations you should use the VALUE function.
  
  Eg.:
  
  =sum(K1:K20)
  
  It won’t work. Use it as
  
  =ArrayFormula(sum(value(K1:K20)))
  
  - Elisa Tormes January 21, 2021 At 4:00 pm
    
    Thank Prashanth! Will definitely try it!
    
Nick September 26, 2020 At 1:16 pm

The formula works really nicely! I’m just wondering: is it possible to make the formula remove null entries?

Let’s say for the example you gave above in Image 01, E4 and E5 do not have any values. So I don’t want them showing up in the output. How would you do that?

- Prashanth September 29, 2020 At 9:25 am
  
  Hi, Nick,
  
  We can use Query to filter out the blanks in the third column (column 1 contains the country name, column 2 contains years and column 3 contains values).
  
  =Query(ARRAYFORMULA(split(flatten(A2:A6&"|"&B1:E1&"|"&B2:E6),"|")),"Select * where Col3 is not null",0)
  
S K Srivastava April 30, 2020 At 7:56 pm

It took me a while to understand what exactly is happening. Brilliant use of headers option of the Query function.
Thanks.

In real life not more than 3-4 fields are fixed in that case simply use of "&" as follows should be sufficient

=ArrayFormula(SPLIT(FLATTEN(ArrayFormula(A6:A13&"♣"& amp;B6:B13&"♣"&C6:C13&"♣"&D6:D13&"♣"& amp;E5:H5&"♣"& E6:H13)),"♣",1,0))

- Prashanth April 30, 2020 At 10:36 pm
  
  Hi, SKS,
  
  I agree with you 🙂
  
- MMC April 30, 2020 At 11:00 pm
  
  It works really nice. Thanks.
  
MMC April 30, 2020 At 7:14 pm

Thank you very much for the update.

I really enjoy your site very much. Keep on the good work.

Kind regards.

- Prashanth April 30, 2020 At 10:37 pm
  
  Hi, MMC,
  
  Thanks for your feedback!
  
S K Srivastava April 26, 2020 At 6:38 pm

Thanx for giving credit to me. I appreciate your gesture.

I have further modified the formula to accommodate more than one fixed column.

- MMC April 30, 2020 At 1:31 am
  
  Hello,
  
  I’m very much interested in a formula to accommodate more than 1 fixed column. Would you be so kind to share it here?
  
  Kind regards.
  
  - Prashanth April 30, 2020 At 11:47 am
    
    Hi, MMC,
    
    Updated the post to incorporate the required changes. I also included my (experiment) sheet.