How to Unstack Data in Excel

Wrangling spreadsheet data can sometimes feel like trying to solve a puzzle. One of the most common challenges is dealing with "stacked" data - long lists where information is organized vertically in just a few columns. This article will show you several ways to unstack that data in Excel, transforming it from a long list into a wide, easy-to-read table.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly is Stacked Data?

Stacked data, often called a "long" or "narrow" format, organizes information vertically. Imagine a simple sales report. In a stacked format, each sales transaction gets its own row, resulting in two main columns: one for a category (like 'Product') and one for a value (like 'Sales').

Here’s a quick example of stacked data:

This format is great for data entry and is how many databases and applications store information. However, it's not always ideal for reporting or analysis. You often want to see this data in an "unstacked" or "wide" format, where each unique category gets its own column.

Here’s the same data in an unstacked format:

Or, more commonly, it might be summarized (or "pivoted") into a format that’s easier to read:

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Why Would You Want to Unstack Data?

Unstacking turns your data into a more reader-friendly layout and is often a necessary step for specific types of analyses. Here are a few common reasons to do it:

• Better Summaries: It makes it easier to compare categories side-by-side at a glance.
• Charting: Certain Excel charts, like clustered column charts comparing products each month, require data in an unstacked format.
• Cross-Tab Analysis: You can see the relationship between two different variables, such as 'Month' and 'Product'.
• Specific Reporting Needs: Some management reports or dashboards require a specific wide table layout.

Now, let's get into the practical, step-by-step methods to unstack your data in Excel.

Method 1: Using a PivotTable (The Easiest & Fastest Method)

For most situations, a PivotTable is the quickest and most powerful way to reorganize your data without complex formulas or manual work. It’s an interactive table that automatically summarizes large amounts of data.

Let's use a slightly more detailed dataset:

Step-by-Step Instructions:

1. Click anywhere inside your data range.
2. Go to the Insert tab on the Ribbon and click PivotTable. Excel will automatically select your data range, and you can choose to place the PivotTable in a new worksheet. Click OK.
3. You'll now see the PivotTable Fields pane on the right. This is where you build your report. Drag and drop the fields into the four areas at the bottom (Filters, Columns, Rows, Values).
4. Drag the field you want for your row labels into the Rows area. In our example, we'll drag Date.
5. Drag the field whose values you want to become the new column headers into the Columns area. Here, we'll drag Product.
6. Drag the numerical field you want to analyze into the Values area. We'll drag Sales here. By default, Excel will probably show Sum of Sales.

Your resulting PivotTable will instantly show the unstacked data:

Pro Tip: By default, PivotTables have a compact layout. To make it look more like a standard table, click on your PivotTable, go to the Design tab > Report Layout > Show in Tabular Form.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: Using Power Query (The Automated & Repeatable Method)

Power Query is Excel’s data transformation engine, perfect for tasks you need to perform regularly. It lets you build a repeatable query that unstacks your data automatically. Each time your source data changes, you just need to click "Refresh." This method is incredibly powerful for large datasets or messy data that needs cleaning first.

Step-by-Step Instructions:

1. Select your data range, and ideally, format it as a table first (select data > Ctrl + T).
2. Go to the Data tab. In the "Get & Transform Data" group, click From Table/Range.
3. This will open the Power Query Editor window, showing a preview of your data.
4. Select the column containing the values you want to become your new column headers. In our example, select the Product column by clicking its header.
5. Go to the Transform tab. In the "Any Column" group, find and click the Pivot Column button.
6. A dialog box will appear. Here, you need to tell Power Query which column contains the actual values that should fill the new columns.
7. Click OK. Power Query will instantly transform your data into the unstacked format.
8. To finish, go to the Home tab in the editor and click Close & Load. Your new, unstacked table will be loaded into a new worksheet in Excel.

The best part? If you add new rows to your original data table, you just need to right-click on your Power Query output table and hit Refresh to see it magically update.

Method 3: Using Dynamic Array Formulas (The Modern & Flexible Method)

If you have a modern version of Excel (Microsoft 365 or Excel 2021) that supports dynamic arrays, you can use a combination of formulas to create a fully dynamic unstacked report that updates in real-time as your source data changes.

This approach uses the UNIQUE, TRANSPOSE, and SUMIFS functions together.

Step-by-Step Instructions:

Let's assume your original stacked data is in cells A1:C7 (with headers).

1. Create the Row Headers: In a blank part of your sheet (e.g., cell E2), list the unique row identifiers. With dynamic arrays, this is simple. For our example, we want unique dates as rows.
2. Create the Column Headers: Next, we need to create the new column headers from our 'Product' list. In cell F1, use this formula:
3. Bring It All Together with SUMIFS: Now for the magic. In cell F2, we'll write one formula that will fill in the entire data section. This formula uses SUMIFS and references the dynamic arrays we just created (using the # spill operator).

This one formula in F2 automatically populates the entire table with the correct summed sales figures, creating a perfect, unstacked view that updates instantly if you change any part of the source data.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Which Method Should You Choose?

Feeling overwhelmed by the options? Here's a simple guide to help you decide:

• Use a PivotTable if... you need a quick, interactive way to summarize and analyze data. It's the go-to choice for most users for its speed and flexibility. Perfect for ad-hoc analysis.
• Use Power Query if... you have a large dataset, a repetitive reporting task, or data that needs cleaning up before being unstacked. It's the best for building a robust and refreshable data pipeline.
• Use Dynamic Formulas if... you want a final report built directly into your spreadsheet that updates in real-time without needing a refresh. This offers the most seamless integration into custom dashboards and financial models.
• Manual Copy & Paste if... you have a very tiny, one-time task and don't feel comfortable with any of the other methods. Be cautious as it's slow and prone to errors.

Final Thoughts

Unstacking data is a fundamental skill in Excel for anyone who works with reports. Mastering tools like PivotTables, Power Query, and modern formulas empowers you to transform raw data into clear, actionable insights in just a few clicks. Choose the method that best fits your task and your comfort level, and you'll save yourself countless hours of manual work.

While Excel is incredibly powerful, stitching together marketing and sales data from different platforms (like Google Analytics, Shopify, Salesforce, etc.) for reporting still requires a ton of manual exporting and data wrangling. At Graphed, we automate all of this tedious work. We believe you should be able to get answers from your data instantly, without building complex reports from scratch. Just connect your sources and ask questions in plain English, and we'll generate real-time, interactive dashboards for you in seconds.