Learn how to unpivot data in Microsoft Excel by following simple yet effective methods!

Mostly, you get normalized form data from databases, which you use in Microsoft Excel to create report form data using PivotTable, Power Query, etc.

What if you get report form data from any software or database and you need to use that dataset for data analytics? You can’t directly use a report form data to create further reports, like creating a PivotTable from another PivotTable.

Here comes the need to transform report form data to normalized form data, which is often known as unpivoting datasets. Follow along with me as I show the tried and tested methods to unpivot data in Excel. Let’s dive in!

Using the PivotTable and PivotChart Wizard

You can effortlessly use the PivotTable and PivotChart Wizard tool to unpivot a dataset from a report from data in Excel. To follow this method, first, you need to enable the PivotTable and PivotChart Wizard in your Excel desktop app.

Go to the Insert tab and check if the PivotTable and PivotChart Wizard tool is available in any commands block in the ribbon menu. If you don’t find it, the tool is disabled in the Excel desktop app.

To enable the tool, go to the source data worksheet and press Alt + F + T to launch the Excel Options dialog box.

Click on the Customize Ribbon category below the Excel Options column.

The Customize the Ribbon screen with two columns of Excel tools will open on the right side.

Click on Choose commands from the drop-down menu and select the All Commands option.

Now, scroll down to the Excel tools section that starts with P and slowly scroll through the list to find the PivotTable and PivotChart Wizard tool.

Click on it to select the command.

Select the Insert commands block in the Customize the Ribbon column.

Now, below the Customize the Ribbon column on the right side, click on the New Group button.

The New Group (Custom) group field will show up. Click on the Rename button.

The Rename dialog box will show up. Use it to rename the custom group. Also, choose a custom icon for the commands group.

Once added, the PivotTable and PivotChart Wizard tool will show up in the newly created group in the Insert tab.

Click on the PivotTable and PivotChart Wizard tool.

Select the Multiple consolidation ranges option in the PivotTable and PivotChart Wizard tool. Click Next.

Select the I will create the page fields option and click Next.

Step 2b of PivotTable and PivotChart Wizard will show up. Hit Finish to create a PivotTable.

Find the PivotTable and double-click on the final grand total value to generate normalized form data from the PivotTable as soon and as accurately as possible.

This normalized form of data will show up in a new worksheet. Copy this table and paste it somewhere else. You’ll, however, need to remove table formatting for the selected dataset.

Select the copied table and click on the Clear drop-down in the Clear Formats command.

The table will be transformed into a raw data format, and the Filter & Sort tool will appear.

Press Ctrl + Shift + L to disable the Filter & Sort tool.

That’s it! You’ve successfully converted report form data to normalized form data in Excel.

Using an Excel Formula

If you prefer using an Excel formula, you can use a combination formula created from INDEX, SEQUENCE, and INDIRECT functions.

Firstly, navigate to the worksheet where you’ve got the report form or PivotTable dataset that needs conversion into normalized form data.

Suppose your source dataset looks something similar to the dataset given above. The source data is in the A1:G6 cell range.

Create the following column headers anywhere in the source data worksheet:

• Region
• Months
• Sales

Let’s consider you’ve created the above column headers in the I1:K1 cell range.

Select I1, type in the following formula into the cell and hit Enter:

=INDEX($A$2:$A$6,INT((ROWS($I$2:I2)-1)/6)+1)

Here’s how you can customize this formula when using it in your worksheet:

• $A$2:$A$6: The cell range of the source dataset.
• $I$2:I2: The destination for the values of the first column, Region, from the source dataset.

Replace the above cell ranges according to your own dataset.

Now, select the cell J2 and type in the following formula:

=INDEX($B$1:$G$1,MOD(ROWS($J$2:J2)-1,6)+1)

Don’t forget to customize the cell range references in the above formula syntax. Here are the definitions of the references:

• $B$1:$G$1: Fetches data from the first month column of the used dataset.
• $J$2:J2: Destination for the month name column.

Hit Enter to calculate the cell and get the month’s name.

Finally, select K2 and enter the following formula into it:

=INDEX($B$2:$G$6,INT((ROWS($K$2:K2)-1)/6)+1,MOD(ROWS($K$2:K2)-1,6)+1)

Make sure you’re changing the cell range references according to your own input dataset.

Hit Enter to calculate the cell.

Now select I2:K2 and drag the fill handle down for up to 29 cells to copy the formula to the rest of the cells. There should be a total of 30 rows since the source data has 5 regions and 6 months, which translates to 30 cells after the transformation.

If your source data is much larger than that, calculate how many rows you need to drag the fill handle down.

Congratulations! You’ve successfully used this formula-based approach to unpivot data in Microsoft Excel.

Using Power Query

Power Query is the most preferred way to unpivot data in Excel. Once you create a data connection in this tool, you can reuse it continuously until you change the overall structure and organization of the source dataset.

There are two ways you can use this data transformation tool to transform report form data into normalized form data. The first one is importing data from an external source.

To follow this approach, click on the Get Data command in the Data tab of the Excel ribbon menu.

Now, hover the mouse cursor over your preferred data source, like From Azure. An overflow menu will open on the right side. There, you can choose the final data connector, like From Azure SQL Database.

You can now follow the onscreen instructions to complete the data import process into the Power Query Editor.

If you wish to export a PivotTable or any other report form data from the active worksheet, select the source data and click on the From Table/Range command.

Click OK on the Create Table dialog box to finalize the data export to the Power Query user interface.

Once the dataset is in Power Query Editor, select the first column that contains the column and row headers.

Right-click on the selected column and select the Unpivot Other Columns option in the context menu.

Power Query will instantly transform the report form data into normalized form data.

Click on the File menu and select the Close & Load To command.

Select the Existing worksheet option on the Import Data dialog box and click on the destination cell.

Click OK to complete the data import process.

You should see all the similar values under the same column in the table created in the active worksheet.

Select the imported data and click on the Clear Formats command in the Clear drop-down menu of the Home tab.

This should remove table formatting from the selected dataset. Now you have a normalized form dataset.

Using Excel VBA

If you prefer using Microsoft Excel using VBA scripting or are willing to learn VBA-based automation in Excel, you should try and practice this technique.

Firstly, go to the following Excel guide and find out how to set up a macro using a VBA script:

📒 Read More: How To Use The VBA Code You Find Online

If you’re ready to create your own macro, use this script:

Sub UnpivotTable()
    Dim ws As Worksheet
    Dim srcRange As Range, destCell As Range
    Dim r As Long, c As Long, outputRow As Long
    Dim headerRow As Range
    
    ' Select source table
    On Error Resume Next
    Set srcRange = Application.InputBox("Select the source table:", Type:=8)
    On Error GoTo 0
    
    If srcRange Is Nothing Then
        MsgBox "No source table selected. Exiting.", vbExclamation
        Exit Sub
    End If
    
    ' Select destination cell
    On Error Resume Next
    Set destCell = Application.InputBox("Select the destination cell for unpivoted data:", Type:=8)
    On Error GoTo 0
    
    If destCell Is Nothing Then
        MsgBox "No destination cell selected. Exiting.", vbExclamation
        Exit Sub
    End If
    
    Set ws = srcRange.Worksheet
    outputRow = destCell.Row
    
    ' Determine headers
    Set headerRow = srcRange.Rows(1)
    
    ' Write new headers
    destCell.Value = "Category"
    destCell.Offset(0, 1).Value = "Attribute"
    destCell.Offset(0, 2).Value = "Value"
    outputRow = outputRow + 1
    
    ' Loop through the data and unpivot
    For r = 2 To srcRange.Rows.Count
        For c = 2 To srcRange.Columns.Count
            ws.Cells(outputRow, destCell.Column).Value = srcRange.Cells(r, 1).Value
            ws.Cells(outputRow, destCell.Column + 1).Value = headerRow.Cells(1, c).Value
            ws.Cells(outputRow, destCell.Column + 2).Value = srcRange.Cells(r, c).Value
            outputRow = outputRow + 1
        Next c
    Next r
    
    MsgBox "Unpivoting completed successfully!", vbInformation
End Sub
 

Once the VBA macro is ready, press Alt + F8 to launch the Macro dialog and select the UnpivotTable macro.

Hit Run to execute the script.

The macro will ask you to choose the source dataset through a dialog box.

Then, it’ll also ask you to select the destination cell for the normalized form dataset.

Once you supply all the input data, the Excel VBA macro will transform the input report form data into an unpivoted dataset.

Conclusions

Today, you’ve learnt how to unpivot data in Microsoft Excel using multiple proven methods, like the PivotTable wizard, an Excel formula, Power Query, and Excel VBA.

Which one did you like the most? Comment below to share your thoughts and feedback.