How to Unpivot Data in Excel

Wrestling with data organized in a "wide" format can feel like trying to fit a square peg in a round hole when it comes to analysis. If you have a table with months, products, or categories stretched out across columns, you’ve probably discovered how hard it is to use that data in a PivotTable or a chart. This article will show you how to quickly fix that common problem by unpivoting your data in Excel, transforming it from a wide layout into a tidy, analysis-ready format.

So, What Does “Unpivot Data” Even Mean?

Unpivoting data sounds technical, but the concept is simple. It’s the process of changing your data’s structure from a wide format to a long format.

Most of the time, data is organized in a wide format because it’s easy for humans to read. For example, imagine a sales report:

Example of Wide (Pivoted) Data:

While this is scannable, Excel struggles to analyze it. You can't easily answer questions like "What was the average sale in February?" or "Who was the top rep in Q1?" without some complicated formulas. To make this data useful, you need to convert it to a long format.

Example of Long (Unpivoted) Data:

Each row in this long format represents a single observation (one rep, one month, one sales figure). This structure is what analytical tools like PivotTables and databases were designed for. It’s far more flexible and powerful for reporting.

Method 1: The Best and Fastest Way - Power Query

For any user of Excel 2016 or newer (including Microsoft 365), Power Query is the absolute best way to unpivot data. It's built into the Data tab under the "Get & Transform Data" section. It's fast, reusable, and doesn't change your original source data.

Step 1: Send Your Data to Power Query

First, make sure your data is in a named Excel Table. If it's not, just click anywhere inside your data range and press Ctrl + T. Confirm that your table has headers and click OK.

Once it's an Excel Table:

• Click on any cell inside your table.
• Navigate to the Data tab on the Ribbon.
• Click on From Table/Range.

The Power Query Editor will launch in a new window, displaying your data.

Step 2: Use the Unpivot Columns Feature

This is where the magic happens. You need to tell Power Query which columns to transform from horizontal to vertical. You have two main ways to do this:

Option A: Unpivot Other Columns (Easiest)

This approach is the most efficient. Select the column(s) that you want to remain as fixed identifier columns. In our example table, that's just the 'Sales Rep' column.

• Click the header of the 'Sales Rep' column to select it.
• Go to the Transform tab in the Power Query Editor.
• Click the dropdown arrow on Unpivot Columns.
• Select Unpivot Other Columns.

Instantly, Power Query will take all the other columns (Jan Sales, Feb Sales, Mar Sales) and unpivot them, leaving 'Sales Rep' untouched.

Option B: Unpivot Selected Columns

Alternatively, you could select only the columns you want to unpivot.

• Hold Ctrl and click to select the headers of 'Jan Sales', 'Feb Sales', and 'Mar Sales'.
• Right-click on any of the selected headers.
• Choose Unpivot Columns.

Both methods achieve the same result. The data will transform into two new columns, typically named 'Attribute' and 'Value'.

Step 3: Rename and Load Your New Table

Your data is now in a lovely long format, but let's clean it up before loading it back into Excel.

• Double-click the header named Attribute and rename it to something meaningful, like Month. Do the same for the Value column, renaming it to Sales.
• Check the data types: Power Query is smart, but it's good practice to ensure the data types are correct. Click the icon next to each column header. 'Sales' should be a number (1.2), 'Month' should be text (ABC), and so on. If anything is wrong, select the correct type.
• Once everything looks good, click the Home tab in the Power Query Editor. Click Close & Load.

Excel will create a new worksheet with your unpivoted data. The best part? If your original source data changes, just right-click your new table and select Refresh—Power Query will re-run all the steps and update it!

Method 2: A Hidden Trick Using the PivotTable Wizard

If you're using an older version of Excel or just want a quick and dirty method without Power Query, this clever trick uses a legacy wizard to achieve the same goal.

Step 1: Launch the PivotTable and PivotChart Wizard

You can't find this wizard in the Ribbon, so you'll need to use a keyboard shortcut:

• Press Alt + D, then release and press P.

This summons the old-school PivotTable Wizard from a forgotten era of Excel.

Step 2: Navigate Through the Wizard

Work your way through the prompts:

• On the first screen, select Multiple consolidation ranges and click Next.
• On the next screen, choose I will create the page fields and click Next.
• Now, under Range:, select your entire data set including the headers. After selecting, click the Add button.
• Click Next.
• Choose where to create the PivotTable (a new worksheet is usually best). Click Finish.

Step 3: Uncover the Unpivoted Data

Excel will generate a funky-looking PivotTable. It's not our end goal—it's just a byproduct of the process. Your actual goal is the grand total value, typically located in the bottom-right corner of this table.

Double-click on the Grand Total cell.

Just like that, Excel will burst open a new sheet containing a properly structured, unpivoted table of the underlying data.

Step 4: Clean Up and Rename

The table you get will have generic headers: 'Row', 'Column', and 'Value'. Simply overwrite these with your actual categories, like 'Sales Rep', 'Month', and 'Sales', and you're good to go.

Method 3: The Manual Method (Not Recommended)

For the sake of completeness, you can unpivot data manually with clever copying and pasting. It's incredibly tedious, prone to human error, and completely impractical for data sets with more than a few rows.

The process involves:

• Building an empty table with your new column headers ('Sales Rep', 'Month', 'Sales').
• Copying each "Identifier" value (e.g., 'Anna') and pasting it down multiple times (once for each month).
• Copying the value for each corresponding month (e.g., Anna's Jan Sale) and pasting it into the 'Sales' column.
• Repeating this for every single row and column in your original data.

It's a long, mind-numbing process that Power Query can do in less than 30 seconds. So while you can do it, we strongly recommend one of the two methods above!

Advanced Method: Unpivoting with a Simple VBA Script

For those who love to automate and work with macros, VBA offers another repeatable way to unpivot data. This can be useful if you're building a larger automated workflow within Excel.

Step 1: Open the VBA Editor and Insert a Module

First, get the VBA editor open by pressing Alt + F11. In the editor window, find your workbook in the Project Explorer (top-left panel). Right-click on it, hover over Insert, and choose Module.

Step 2: Paste the VBA Code

Copy and paste the following code into the new module window:

Sub UnpivotData()
'Declare variables
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
Dim sourceRange As Range
Dim lastRow As Long
Dim lastCol As Long
Dim i As Long, j As Long
Dim destRow As Long

'Set your source worksheet and data range
'Change "Sheet1" to the name of your sheet with data
Set sourceSheet = ThisWorkbook.Sheets("Sheet1") 

'Add a new sheet for the unpivoted data
Set destSheet = ThisWorkbook.Sheets.Add(After:=sourceSheet)
destSheet.Name = "Unpivoted Data"

'Find the last row and column of your data in the source sheet
lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row
lastCol = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column

'Create headers on the destination sheet
destSheet.Cells(1, 1).Value = sourceSheet.Cells(1, 1).Value 'e.g. Sales Rep header
destSheet.Cells(1, 2).Value = "Attribute" 'Generic header
destSheet.Cells(1, 3).Value = "Value" 'Generic header

'Initialize the starting row for destination data
destRow = 2

'Loop through each row and column of the source data
For i = 2 To lastRow 'Start from row 2 to skip headers
    For j = 2 To lastCol 'Start from column 2 to skip the first ID column
        'Copy the identifier column (column 1)
        destSheet.Cells(destRow, 1).Value = sourceSheet.Cells(i, 1).Value
        
        'Copy the column header from the source data
        destSheet.Cells(destRow, 2).Value = sourceSheet.Cells(1, j).Value
        
        'Copy the current value
        destSheet.Cells(destRow, 3).Value = sourceSheet.Cells(i, j).Value
        
        'Move to the next row in the destination sheet
        destRow = destRow + 1
    Next j
Next i
End Sub

Step 3: Run the Macro

Close the VBA editor and return to your Excel sheet. Press Alt + F8 to open the macro dialog, select 'UnpivotData', and click Run. The macro will instantly create a new worksheet named "Unpivoted Data" with your table perfectly transformed.

Final Thoughts

Transforming your data from a "wide" to a "long" structure is a foundational skill for anyone wanting to do serious analysis in Excel. Whether you use the powerful and modern Power Query, the old PivotTable Wizard trick, or an automated VBA script, unpivoting unlocks the true potential of your data for building insightful reports and dashboards.

While mastering tasks like unpivoting in Excel is valuable, it highlights the manual data prep work often standing between you and your insights. Much of this work can be automated completely. At Graphed, we designed a tool to eliminate these tedious steps by connecting directly to your marketing and sales platforms (like Google Analytics, Shopify, Facebook Ads, etc.). The data is prepared for analysis automatically, so you can skip the CSV downloads and table transformations. Instead, you can just ask questions in plain English - like "create a chart comparing Facebook Ads conversions to Shopify sales by campaign this month" - and get a real-time dashboard built in seconds.