How to Reshape Data in Excel

Receiving data formatted for a presentation instead of analysis is a rite of passage for anyone who works with spreadsheets. You get a report with months spread across columns, merged cells, and colorful headers - perfect for a human to read, but a total nightmare for a Pivot Table. Before you can find any insights, you have to completely reshape it. This article will show you how to stop tiresome copy-pasting and start using Excel's built-in tools to transform your data from wide to tall, making it ready for any analysis you throw at it.

Why Is Data Shape So Important?

Before fixing the problem, it helps to understand what the "right" shape for data actually is. In the world of data analysis, the gold standard is called tidy data. Tidy data has a simple, consistent structure that makes it incredibly easy for software like Excel to work with. The rules are straightforward:

• Every column is a variable (e.g., Date, Product, Region, Sales Amount).

• Every row is a single observation (e.g., a specific sale of a product in one region on a given day).

• Every cell contains a single value.

The most common issue people face is dealing with "wide" data when they really need "long" (or tall) data for analysis.

Wide data is often used for human readability. It spreads values across multiple columns. For example, you might have a column for each month.

Example of Wide Data:

Imagine your sales tracker looks like this:

Try putting that in a Pivot Table. How would you trend sales over time? How would you filter for February? It’s awkward because your time variable (months) is trapped in the column headers.

Long data, on the other hand, puts all values for a single variable into one column.

Example of Long Data:

Here’s that same data in a long, tidy format:

This format is analytical magic. You can now easily use a Pivot Table to sum sales by product, filter for a specific month, or create a line chart showing the sales trend over time with a few clicks. Your goal, most of the time, is to turn wide data into long data.

The Manual Method: Slow, Risky, and Not Recommended

We’ve all been there. You have a wide dataset and an urgent deadline. You start the soul-crushing process of manually copying and pasting data to reshape it. You copy the January sales figures, paste them under the product names, create a new "Month" column, and type "January" dozens of times. Then you repeat the entire process for February, and then March, and so on.

This approach has some serious drawbacks:

• It's painfully slow: What takes minutes for a few months of data can take hours for a full year or for multiple products.

• It's full of errors: A slip of the mouse, a copy-paste error, or a distraction can corrupt your data in ways you might not notice until it’s too late.

• It's not repeatable: When you get next month's sales report, you have to do it all over again. There is no "refresh" button for manual work.

Some Excel veterans might use a complex web of formulas with INDEX, MATCH, and OFFSET to achieve this, but these solutions are hard to build, difficult to debug, and break easily if the source data's structure changes. There is a much better way that is built into modern versions of Excel.

The Modern Solution: A Beginner's Guide to Excel's Power Query

If you've been overlooking the "Get &amp, Transform Data" section on Excel's Data tab, you've been missing out on its most powerful feature: Power Query. Power Query is an engine designed to help you connect to, clean, and reshape data from a huge variety of sources. It's built into all modern versions of Excel (Excel 2016 and later for Windows, and Excel for Microsoft 365).

The real beauty of Power Query is that it records every step you take to clean and reshape your data. These steps form a "query" that you can save. The next time you get an updated version of your source data, you don't repeat the steps. You just go to your final, clean data table and click "Refresh." Power Query will re-run all your transformation steps automatically in seconds. It turns a manual, repetitive process into a one-click update.

Step-by-Step: Reshaping Wide Data to Long with "Unpivot Columns"

Let's walk through turning our wide sales data into the long format using Power Query. The core function we will use is called "Unpivot." Unpivoting is the term for turning columns into rows.

Step 1: Load Your Data into Power Query

First, you need to tell Power Query where your data is. The best way to do this is by turning your data range into an official Excel Table.

1. Click anywhere inside your data range.

2. Press Ctrl + T (or go to the Insert tab and click Table). This makes your data a dynamic range, so if you add new rows or columns, they are automatically included.

3. With your cursor in the new table, go to the Data tab on the Ribbon.

4. In the "Get &amp, Transform Data" group, click From Table/Range.

This will open the Power Query Editor, a new window where all the data transformation happens. Your table will be displayed inside it.

Step 2: Select Your Columns and "Unpivot"

Now we’ll tell Power Query how to reshape the table. We need to identify which columns are static (the ones we want to keep as they are) and which ones need to be unpivoted (the monthly sales columns).

1. In the Power Query Editor, select the column(s) that you don't want to unpivot. In our example, this is the Product column. If you had other identifier columns like "Region" or "Department," you would hold Ctrl and click to select them all.

2. Go to the Transform tab in the Power Query ribbon.

3. Click the dropdown arrow on the Unpivot Columns button.

4. Select Unpivot Other Columns.

This command tells Power Query: "Keep the columns I've selected as they are, and take all the other columns and turn them from columns into rows." Using "Unpivot Other Columns" is often more robust because if a new month (e.g., "April Sales") is added to your source data later, your query will automatically include it when you refresh, without you having to edit the query.

Instantly, you'll see your data transform. You now have just three columns. The original "Product" column, a new column called "Attribute" (which contains the former column headers: "January Sales", "February Sales", etc.), and a column called "Value" (which contains the sales amounts).

Step 3: Rename and Clean Your New Columns

"Attribute" and "Value" aren't very descriptive names. Let's fix that.

1. Double-click the header of the "Attribute" column and rename it to something meaningful, like Month.

2. Double-click the header of the "Value" column and rename it to Sales.

3. You may also want to clean up the new "Month" column. For example, you can remove the word " Sales" from each entry. To do this, right-click the "Month" column header, choose Replace Values, find " Sales" and replace it with nothing.

4. Finally, you can check that the data types are correct. Power Query is usually good at guessing, but you can set them manually from the Transform tab. Ensure "Sales" is a Currency or Decimal Number and "Month" is Text.

Step 4: Close &amp, Load

Once you are happy with the shape and cleanliness of your data, you can load it back into Excel.

1. Go to the Home tab in the Power Query ribbon.

2. Click the top half of the Close &amp, Load button.

Power Query will close, and your newly shaped, tidy data will appear in a brand-new worksheet, perfectly formatted within an Excel Table. You can now build Pivot Tables, charts, and formulas off this clean table, and your analysis will be ten times easier.

The Payoff: The One-Click Refresh

This is where the magic happens. Let's say it's next month and you get new data. You add a new "April Sales" column to your original source data table.

You don't need to repeat any of the steps above. Simply go to the green query output table, right-click anywhere inside it, and click Refresh. Power Query runs all of your recorded steps in the background and adds the new April data to your tidy table, correctly formatted. This is how you automate your reporting and save hours of your life.

Going the Other Way: From Long to Wide with Pivot Columns

While long data is best for analysis, sometimes a report requires data in a wide format. Power Query can handle this just as easily with the "Pivot Column" feature.

Let's say you want to turn our "long" sales table back into the "wide" format.

1. Either start a new query from your long data table or go back into your existing query (from the Queries &amp, Connections pane) to edit it.

2. Select the column that contains the values you want to become a new set of column headers. In our case, this is the Month column.

3. Go to the Transform tab and click Pivot Column.

4. A dialog box will appear. You need to tell it which column contains the actual values that will fill the new columns. For us, this is the Sales column. Select it from the "Values Column" dropdown.

5. Under "Advanced options," you can specify the aggregation function. Since each product has only one sales value per month, choose "Don't Aggregate."

6. Click OK.

Your tall data will be instantly pivoted into the wide format again. This powerful feature allows you to reshape your data into any format you need for your final report.

Final Thoughts

Making your data work for you starts with getting it into the right shape. Manually reshaping data is a recipe for frustration and errors, but Excel's Power Query transforms it into an efficient, automated process. By using concepts like "Unpivot" and "Pivot," you create a repeatable workflow that keeps your data clean and your reports up-to-date with a single click.

As powerful as this is, collecting and preparing data for analysis can still involve many tedious steps. We built Graphed to take this automation even further. Instead of pulling data into Excel and running transformation queries, our platform connects directly to your marketing and sales tools like Google Analytics, Shopify, and Salesforce. We streamline the entire process, allowing you to connect your sources once, then instantly create dashboards and get insights just by asking questions in plain English - no manual steps required.