Excel: Unpivot Data with Formulas—No Power Query Needed!

Key insights

• Unpivoting with Formulas in Excel means changing data from a wide format with many columns into a long format with stacked rows, using only formulas and not Power Query or VBA.
  
• Dynamic Array Functions like MAP, HSTACK, MAKEARRAY, and TEXTSPLIT allow users to unpivot data efficiently. These functions help combine and transform columns into structured row data automatically.
  
• No Power Query Needed: This method is useful for people who cannot use Power Query due to software limits, sharing settings, or version restrictions. All changes update live as the source data changes.
  
• Keeps Source Data Untouched: The original worksheet stays intact because all formula transformations happen in a separate area or sheet. There is no need for helper columns or manual adjustments.
  
• Main Advantages: Formula-based unpivoting gives automatic updates, works without macros, and fits well in shared workbooks. It offers a single formula solution that adjusts to new or changed data instantly.
  
• Recent Innovations (May 2025): New tutorials show how to use advanced formulas for unpivoting even with merged cells or multiple headers. Example files are available to guide users through these updated methods step by step.
  

Introduction: Rethinking Unpivoting in Excel

A recent YouTube video by the content creator Excel Off The Grid explores a modern approach to unpivoting cross-tabulated data in Excel using only formulas—no Power Query or VBA required. Traditionally, unpivoting, or transforming wide data tables into long, analysis-ready formats, has depended heavily on Power Query. However, this method is not always feasible, especially in environments where Power Query is unavailable or when workbook sharing settings restrict its use. The video addresses these challenges and demonstrates a formula-driven solution that leverages the latest Excel functions.

As organizations increasingly seek dynamic, easily maintained solutions for data reshaping, understanding the tradeoffs between formula-based unpivoting and more conventional tools like Power Query is essential. This coverage outlines the key insights from the video, focusing on practical application, technical tradeoffs, and the evolving capabilities of Excel.

The Challenge of Cross-Tab Data and Why Formulas Matter

Cross-tab layouts are common in business reporting but can complicate analysis, especially when multiple header rows and merged cells are involved. While Power Query excels at unpivoting, it is not always the ideal choice. For instance, some workbooks must remain macro-free for security or compatibility reasons, and others may be shared across platforms where Power Query is not supported.

In such scenarios, formulas provide a flexible alternative. By staying within Excel’s calculation engine, users gain live, automatic updates as source data changes, without the need for manual refreshes or additional steps. This is particularly valuable for collaborative environments or when dealing with frequently updated reports.

How Formula-Based Unpivoting Works

The video details a step-by-step process to unpivot data using only formulas, even when faced with complex headers and merged cells. Recent advances in Excel’s formula toolkit—such as MAP, HSTACK, and MAKEARRAY—have made it possible to construct dynamic, spillable arrays that reshape data efficiently.

For example, MAP iterates over rows or columns, applying logic to each element, while HSTACK combines arrays horizontally. MAKEARRAY enables on-the-fly array generation, and together these functions allow users to bypass the need for helper columns or manual adjustments. The result is a single, elegant formula that adapts automatically to changes in the source table, maintaining both structure and accuracy.

Advantages and Tradeoffs of the Formula Approach

One of the main benefits of formula-based unpivoting is its dynamic nature—updates to source data are instantly reflected in the transformed output. Additionally, because no macros or external tools are used, this method is compatible with shared workbooks and environments lacking Power Query support. Keeping all transformations within formulas also preserves the integrity of the original data, as no changes are made to the source sheet.

However, this approach is not without its challenges. Creating and maintaining complex formulas can be daunting for less experienced users, and not all Excel installations support the newest dynamic array functions. There is also a tradeoff between simplicity and flexibility: while a single formula solution is elegant, it may become difficult to troubleshoot or modify as data structures evolve.

What’s New in 2025: Innovations in Formula-Driven Unpivoting

The video, released in May 2025, showcases the latest advancements in Excel’s formula capabilities. By providing free example files and walking through real-world scenarios, Excel Off The Grid demonstrates how users can implement a robust, formula-only unpivot process without altering source data or relying on manual intervention.

Notably, the approach avoids helper columns and uses a single formula that dynamically adjusts to changes. By leveraging new functions such as MAKEARRAY and HSTACK, users can overcome previous limitations and achieve results that were once possible only through Power Query or VBA.

Conclusion: A New Era for Data Transformation in Excel

Formula-based unpivoting marks a significant shift in how Excel users can approach data transformation. With the introduction of powerful dynamic array functions, users now have accessible, flexible options for reshaping data without relying on external tools. This method keeps source data untouched, ensures automatic updates, and sidesteps the technical and administrative hurdles associated with Power Query and VBA.

While there are tradeoffs in terms of complexity and function availability, the approach detailed by Excel Off The Grid opens new possibilities for dynamic, formula-driven data analysis in Excel. As Excel continues to evolve, staying informed about these innovations is crucial for anyone working with data in spreadsheets.

Keywords

Unpivot Excel formulas Unpivot without Power Query Excel unpivot tutorial formula method unpivot data Excel no Power Query unpivot spreadsheet formulas