Unpivot Data in Google Sheets (From Wide to Long Format) using Formulas or Scripts

Have you ever struggled with analyzing data that’s spread across columns in a Google Sheet? That’s called “wide” format — and while it looks clean, it’s not ideal for charts, filters, or pivot tables. To fix this, we need to “unpivot” the data — turning it into a “long” format where each row represents a single observation. Luckily, this can be done easily in Google Sheets using built-in formulas or Google Apps Script.

In this article, you’ll learn what unpivoting is, why it’s so useful, and how to do it step-by-step with both formulas and a script. Whether you’re managing survey responses, monthly sales reports, or attendance logs, this trick will simplify your analysis — even if you’re new to spreadsheets.

What Is Unpivoting and Why Should You Care?

Unpivoting transforms your data layout from wide (many columns) to long (many rows). It’s super helpful for:

Creating charts that compare values over time
Using pivot tables more effectively
Cleaning data for import into databases or apps

Example: Monthly Sales Data

Here’s a simple “wide” format of sales by salesperson across months:

Salesperson	Jan	Feb	Mar
Alice	1000	1200	1100
Bob	900	950	1050

We want to convert this to a long format like:

Salesperson	Month	Sales
Alice	Jan	1000
Alice	Feb	1200
Alice	Mar	1100
Bob	Jan	900
Bob	Feb	950
Bob	Mar	1050

Method 1: Use Google Sheets Formula (No Code)

Step-by-Step Instructions

Assume your data is in A1:D3
In a new sheet, paste the following formula in cell A1:


=ARRAYFORMULA(SPLIT(FLATTEN(A2:A3 & "♦" & B1:D1 & "♦" & B2:D3), "♦"))

This formula uses FLATTEN to collapse your 2D data into a single column and SPLIT to separate each part (Salesperson, Month, Sales).

Pro Tips

Use a unique delimiter (like ♦) to avoid conflicts
Wrap in IFERROR to ignore empty rows

Method 2: Use Apps Script (For More Control)

Want to automate unpivoting with a button or onEdit trigger? Use this script:


function unpivotSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("WideData");
  const data = sheet.getDataRange().getValues();
  const headers = data[0].slice(1); // Months
  const result = [["Salesperson", "Month", "Sales"]];

  for (let i = 1; i < data.length; i++) {
    const name = data[i][0];
    for (let j = 1; j < data[i].length; j++) {
      result.push([name, headers[j - 1], data[i][j]]);
    }
  }

  const outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LongData") || 
                      SpreadsheetApp.getActiveSpreadsheet().insertSheet("LongData");
  outputSheet.clearContents();
  outputSheet.getRange(1, 1, result.length, 3).setValues(result);
}

How to Use It

Go to Extensions > Apps Script
Paste the script above and click the disk icon to save
Run unpivotSheet() manually or attach to a button

Key Benefits of Unpivoting

Makes your data analytics-friendly
Works better with charts and pivot tables
Easy to automate and reuse for new data

Quick Cheat Sheet

Task	Recommended Method
Unpivot small data (under 500 rows)	`FLATTEN + SPLIT` formula
Automate with a button or custom trigger	Apps Script
Export cleaned data to another sheet	Use `getDataRange().getValues()` and `setValues()`

Unpivoting data might sound technical, but it’s one of the most useful tricks to master in Google Sheets. Whether you use a simple formula or a reusable script, you’ll find that your data becomes easier to filter, summarize, and visualize. Try it out on one of your sheets and see how much smoother your workflow becomes!