Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to unpivot data from wide to long format in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Heads up: Excel and Google Sheets do this differently.
Excel
=LET(data,A1:B3,headers,INDEX(data,1,),rows,COUNTA(data)-1,cols,COUNTA(headers),INDEX(TOCOL(SEQUENCE(rows*cols,,1,1)),SEQUENCE(rows*cols,,1,1))&"_"&INDEX(headers,MOD(SEQUENCE(rows*cols,,0,1),cols)+1),TOCOL(INDEX(data,INT((SEQUENCE(rows*cols,,0,1))/cols)+2,MOD(SEQUENCE(rows*cols,,0,1),cols)+1)))Google Sheets
=ARRAYFORMULA(FLATTEN(IF(A2:A3<>'',A2:A3&"_"&B1:C1,""))&"="&FLATTEN(IF(A2:A3<>'',B2:C3,"")))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In Excel, use Power Query to unpivot your data by selecting your range, going to Data > From Table/Range, then Transform > Unpivot Columns.
- 2In Google Sheets, select your range, go to Data > Pivot table, choose 'Unpivot' as the setup type, and configure your pivot table accordingly.
- 3For manual methods, use formulas like TOCOL and SEQUENCE in Excel or ARRAYFORMULA and FLATTEN in Google Sheets.
Tips
- Ensure your headers are unique to avoid confusion in the unpivoted data.
- Check for any empty cells in your data range as they can affect the unpivot process.
- Locale settings might affect formula syntax; ensure consistency across your workbook.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is the difference between unpivoting in Excel and Google Sheets?
Excel uses Power Query for unpivoting, which offers more flexibility and options, while Google Sheets uses a simpler pivot table method.
Can I unpivot data with multiple header rows in Excel?
Yes, but you need to handle them manually or use advanced Power Query techniques to merge header rows before unpivoting.
Is there a limit to the amount of data I can unpivot in Google Sheets?
Google Sheets has a limit of 5 million cells per sheet, so ensure your unpivoted data does not exceed this limit.
More on Pivot-style summaries (formula approach)
See all →Reference
GETPIVOTDATA
Extract a specific value from a PivotTable using field names and item values as explicit identifiers.
How-toHow to build a pivot-style summary with formulas
Build a formula-driven pivot-style cross-tab in Excel or Google Sheets using SUMIFS with mixed references — live totals by row and column without inserting a pivot table.
How-toHow to build a pivot-style summary with QUERY
Use Google Sheets QUERY with GROUP BY, SUM, and LABEL to build a pivot-style summary table without a manual pivot table — Excel alternative uses SUMIFS.
How-toHow to use GETPIVOTDATA to pull values from a pivot table
Learn how GETPIVOTDATA extracts specific values from a pivot table in Excel and Google Sheets, avoiding broken references when the pivot refreshes.
How-toHow to write formulas for a pivot table calculated field
Understand how pivot calculated fields work and when to replace them with SUMIFS-based formulas in Excel and Google Sheets for more control and flexibility.
How-toHow to calculate percent of total without a pivot table
Use SUMIF and SUM to calculate each category's percent of grand total in Excel and Google Sheets — no pivot table required.