How to convert wide tables to long format in Google Sheets

If you work with data in Google Sheets, you’ve probably come across wide tables — where each column represents a time period, category, or variable. While this format is fine for data entry, it’s not ideal for analysis or visualization.

To create pivot tables, charts, or import your data into tools like Tableau or Power BI, you’ll often need to convert wide data to long format.

This article will show you step-by-step how to reshape your data in Google Sheets using formulas and Apps Script — no coding experience required.

🔍 Understanding Wide vs. Long Data

Here’s a quick look at what we mean by “wide” and “long.”

Wide Format (Before)

Region	January	February	March
East	120	135	150
West	100	115	130

Long Format (After)

Region	Month	Sales
East	January	120
East	February	135
West	January	100

🧮 Method 1: Use FLATTEN and SPLIT (No Add-ons Required)

You can use a combination of formulas to “unpivot” your data.

If your dataset is in A1:D4, with regions in column A and months in columns B to D, enter this formula in a new sheet:

=QUERY(
  {
    FILTER(A2:A, A2:A <> ""),
    SPLIT(
      FLATTEN(B1:D1 & "♦" & B2:D),
      "♦"
    )
  },
  "SELECT * WHERE Col2 IS NOT NULL",
  1
)

How it works:

• FLATTEN() stacks all values from your month columns into one column.

• SPLIT() separates the month names from the sales values.

• QUERY() removes blank rows and formats the result neatly.

✅ Result:
You’ll get a long-format table with columns for Region, Month, and Sales.

⚙️ Method 2: Using Google Apps Script (For Larger Datasets)

For larger spreadsheets, formulas can become slow. Apps Script lets you automate the process with just a few lines of code.

1. Go to Extensions → Apps Script.

2. Paste the following code:

function unpivot() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var output = [["Region", "Month", "Sales"]];

  for (var i = 1; i < data.length; i++) {
    for (var j = 1; j < headers.length; j++) {
      output.push([data[i][0], headers[j], data[i][j]]);
    }
  }

  var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("Long Format");
  newSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}

1. Click Save, then Run → Authorize → Run again.

2. A new sheet called “Long Format” will appear.

💡 Why This Matters

Long-format data is easier to:

• Build pivot tables and dynamic charts.

• Filter or group data using functions.

• Import into analysis tools like Power BI, Tableau, or Looker Studio.

✅ Summary

Approach	Best For	Advantages
FLATTEN + SPLIT Formula	Small datasets	No coding required
Apps Script	Large or automated workflows	Fast and repeatable

You can also transform your datasets from wide to long formats using Python or Excel.