Excel - Get and Transform Data | Unpivot Columns with Two Header Rows

Posted on: 06/29/2022

Excel's Unpivot Columns, part of Power Query, is an awesome feature. It is the easier way to fix data that isn't in a tabular format.

You want data in a tabular format so it can be sorted, filtered, and PivotTables created. In this article, we will look at data with one header row and then move on to data with two header rows, which is a big no-no in Excel.

Recent Microsoft Teams articles

YouTube Video

Chapters/Bookmarks

0:00 Intro
0:55 One Header Row
4:07 Two Header Rows
5:18 Transpose in Power Query
6:05 Use the First row as Header
7:25 Check data

Steps to get to the Power Query Editor

Click inside your data
Click the Data tab
Click From Table/Range in the Get & Transform Data group
Your data will be be created in a Table. Click Yes if you only have one header row.
The Power Query Editor opens.

**From Table/Range - Get & Transform Data**

Recent Excel articles

How to Remove Duplicates Running Left to Right in Excel Using Power Query

Removing duplicates in Excel is a common task, but it can be challenging when the duplicates are running horizontally (left to right) instead of vertically. In this blog post, we'll explore an advanced Excel technique using Power Query to efficiently remove duplicates across rows. This method is particularly useful when dealing with complex data structures where traditional duplicate removal methods fall short.

Become an Expert at Using the TOCOL Function in Excel to Merge Columns

The TOCOL function in Excel is a powerful tool for combining data from multiple columns into a single column.

5 Essential Excel Tips Everyone Should Know

Excel is a powerful tool used across various industries, and mastering its features can significantly boost your productivity. In this blog post, we'll explore five essential Excel tips that every user should know.

Excel Focus Cell: Enhancing Active Cell Visibility and Navigation

Microsoft Excel has introduced an exciting new feature called Focus Cell, designed to improve the visibility of the active cell and enhance navigation within spreadsheets. This powerful tool highlights the current cell's row and column, making it easier to track your position in large datasets.

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.