Split Column Headers from Values
I was recently asked how to separate multiple column headers from their values. My favorite tool for transforming data like this is Power Query. So, this post walks through the steps with Power Query. Thanks Ron for your question!
Before we get into the steps, let’s visualize the start and end points. Ron states that the data combines each header and value in the same cell separated by a colon. I imagine it looks a bit like this:
Next, Ron asks how to remove the colon in each cell, place the headers in row 1, and place the values in row 2. I image the end result should look something like this:
The good news is that Power Query can help us with this. Let’s get to it.
We’ll perform these steps:
- Get data
- Transform data
- Load data
Let’s just jump right in.
First we need to get the data into Power Query. To do so, we head to the Data > Get Data command. Since our data is already in Excel, we select From Table/Range.
Note: if your data was somewhere else you’d pick the corresponding source option.
We are then presented with the following Create Table dialog, which is basically asking us to confirm the conversion of our data from an ordinary range to a Table.
Since our data doesn’t have a header row, we leave the My table has headers box clear and click OK.
We see our data in the Power Query editor, like this:
With this step complete, it is time to perform our transformations.
The transformations we’ll perform are split, transpose, and promote. Let’s take them one at a time.
The first thing we’ll tackle is splitting the column headers (First, Last, City, and State) from their values (Brandon, Mann, Greensboro, NC). We right-click the Column1 header and select Split Column > By Delimiter. The following dialog appears, where we can confirm the default options or make any changes needed.
In our case, the defaults are fine so we just click OK
We see the results immediately in the editor:
Next, we click the Transform > Transpose command, and bam:
Finally, we need to move the header labels into the header row. We click Transform > Use First Row as Headers, and bam:
With the transformations complete, it is time to load the data back into Excel.
We send the data back to Excel by clicking the Home > Close & Load To command, and opt to send it back to a Table … bam:
Yay … we did it!
The benefit of this approach is that we do not need to manually enter the column headers, they are automatically derived from the data. Plus, this query can be refreshed at anytime by right-clicking the results table and selecting Refresh.
Hope it helps … and thanks Ron for your question.
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.