How to Flatten Data in Excel

If you've ever dealt with data in a "crosstab" or "wide" format, you know how hard it is to analyze in Excel. Flattening that data - turning it from a wide, multi-headed table into a simple, long list - is the essential first step for using PivotTables, filters, or BI tools effectively. This guide will walk you through a few different ways to transform your data from wide to long, focusing on the most powerful and efficient method: Power Query.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Does It Mean to 'Flatten' Data?

Flattening data, also known as "unpivoting," is the process of changing the structure of your dataset without changing its meaning. You’re taking data that is spread out across many columns and reorganizing it into fewer columns with more rows.

Imagine you have a report showing monthly sales for different regions. In a "wide" or "crosstab" format, it might look like this:

Before (Wide/Pivoted Data)

This format is easy for humans to read, but it's terrible for analysis. You can't easily filter by month, create a chart showing sales trends over time, or sum up total sales for a quarter without complicated formulas.

When you flatten this data, you reorganize it into a "long" or "normalized" format, where each row represents a single data point (one region, one month, one sales figure):

After (Long/Flattened Data)

This "tidy" format unlocks the full analytical power of Excel.

Why Flatten Data? The Benefits of Tidy Data

Time spent flattening your data is time well spent. A tidy, long dataset is much more flexible and powerful for several reasons:

• PivotTable Ready: This structure is exactly what PivotTables are designed to work with. You can now drag "Month" to your columns, "Region" to your rows, and "Sales" to your values to recreate the original view or slice and dice it in countless other ways.
• Easy Sorting and Filtering: Need to see sales just for February? Just filter the "Month" column. Want to find the top-performing regions? Simply sort the "Sales" column in descending order.
• Effortless Charting: Creating charts from flattened data is simple. A line chart showing sales trends over time is a few clicks away, no formula gymnastics required.
• Compatibility with Other Tools: If you plan to import your data into platforms like Power BI, Tableau, or Google Looker Studio, they almost always require data in a long, flattened format.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 1: Using Power Query (The Best Way)

The clear winner for flattening data in Excel is Power Query. It’s a free, built-in tool (in Excel 2016 and newer, labeled as "Get & Transform") that lets you build repeatable data transformation workflows. Once you set up the steps, you can just click "Refresh" to flatten new data.

Here’s how to do it, step by step.

Step 1: Format Your Data as a Table

First, make sure your data is in an official Excel Table. Click anywhere inside your data range and press Ctrl + T. Make sure the "My table has headers" box is checked if it does, then click OK. This makes it much easier for Power Query to recognize your data.

Step 2: Load the Data into the Power Query Editor

With your new Table selected, go to the Data tab on the Ribbon. In the "Get & Transform Data" section, click on From Table/Range.

This will open a new window called the Power Query Editor, which will load a preview of your table.

Step 3: Unpivot the Columns

This is where the transformation happens. Your goal is to keep the "master" columns (like "Region" in our example) and unyoke the columns you want to turn into rows (Jan, Feb, Mar, etc.).

1. Select the column(s) you want to keep as they are. In our example, we click on the header for the Region column. If you had multiple master columns (e.g., Region and Product), you would hold down Shift or Ctrl to select them all.
2. With the master column(s) selected, go to the Transform tab in the Power Query Editor.
3. Click the dropdown arrow for Unpivot Columns and choose Unpivot Other Columns.

Power Query will instantly restructure your data, getting it about 90% of the way to the final flattened format. You'll see two new columns named "Attribute" and "Value" by default.

Step 4: Rename the Columns

The default names "Attribute" and "Value" aren't very descriptive. Double-click on the header of each new column to rename them to something more meaningful, like "Month" and "Sales."

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 5: Load the Data Back to Excel

With your data flattened and renamed, it's time to send it back to your workbook.

1. Go to the Home tab in the Power Query Editor.
2. Click the top half of the Close & Load button.

Power Query will close the editor and load your beautifully flattened data into a new worksheet. Later, if you add new data to your original table (for example, a new "April" column or a new "West" region row), you just have to right-click on your new flattened table and select Refresh. The new data will be automatically flattened and added to the results.

Method 2: Using Slicers and a Dynamic Formula (More Complex)

If you prefer a formula-based approach and have a modern version of Excel (Microsoft 365), you can use a combination of PivotTables and dynamic array formulas like TOCOL and HSTACK to create a dynamic flattened table. This method is more complex upfront but is quite flexible once set up.

Step 1: Create a "Helper" PivotTable

Create a simple PivotTable from your original wide data. Place the primary identifier ("Region" in our example) in the Rows area and all the monthly columns (Jan, Feb, Mar...) in the Values area. This should basically re-create your original wide view.

Step 2: Write the Dynamic Array Formula

Now, in an empty part of your sheet, we will write a formula to flatten the results of this PivotTable. Let's assume your PivotTable rows (Region names) are in A4:A6 and your column headers (Months) are in B3:D3, with the values in B4:D6.

You can use a formula like this:

=DROP(
  REDUCE("", SEQUENCE(ROWS(A4:A6)), LAMBDA(acc, r,
    VSTACK(acc, HSTACK(INDEX(A4:A6, r), TOCOL(B3:D3), INDEX(B4:D6, r, 0)))
  )),
1)

This is an advanced formula, but here's the quick breakdown:

• REDUCE and LAMBDA work together to loop through each row of your PivotTable.
• For each row, HSTACK combines the Region name with two new arrays.
• The first array is the list of month headers, created with TOCOL(B3:D3).
• The second array is the full row of sales data for that region.
• VSTACK appends the results for each region into one long vertical list.
• DROP simply removes the initial blank row created by REDUCE.

The result is a dynamic flattened table. If you add Slicers to your PivotTable to filter regions, this formula output will update automatically. This method is clever but significantly harder to set up and debug than Power Query.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 3: The Manual Copy & Paste Method (Not Recommended)

For the sake of completeness, it's worth mentioning the manual approach. This is an option only for very small, one-off tasks and is highly prone to errors.

The process looks something like this:

1. Set up headers for your new flattened table: Region, Month, Sales.
2. Copy the "North" region cell. Paste it three times in the "Region" column.
3. Transpose-paste the month headers (Jan, Feb, Mar) next to the "North" ones.
4. Transpose-paste the sales data for North next to the months.
5. Repeat the entire process for "South," "East," and every other region.

As you can see, this is incredibly slow and tedious. A single copy-paste error can throw off your entire dataset. We strongly recommend learning the Power Query method to save yourself time and ensure accuracy.

Final Thoughts

Learning how to properly structure your data by flattening it is a fundamental Excel skill. It transforms clunky, hard-to-use reports into flexible datasets ready for PivotTables, charts, and serious analysis. While there are a few ways to get it done, investing a few minutes to learn Power Query provides a powerful, repeatable solution that will save you countless hours in the long run.

This ritual of downloading reports, cleaning them up in a spreadsheet, and painstakingly reshaping them is a universal headache for marketing and sales teams. Instead of manually flattening data every week just to build a simple report, at Graphed , we think you should be able to skip that entire process. By connecting your tools like Google Analytics, Shopify, or Salesforce directly, we let you use simple, natural language to ask for the report you want, and we build the real-time dashboard for you - no CSVs or spreadsheet wrangling required.