Fixing Excel Datasets With Multiple Header Rows Is Easier Than You Think

Abstract

Column headers in Microsoft Excel datasets enhance readability, allow straightforward filtering, and can be utilized in structured references.
Having a number of column headers on the high of a dataset adversely impacts accessibility and will point out that your information must be restructured.
Energy Question’s transposing and unpivoting capabilities may help you repair datasets with two or extra header rows.

Column headers in Excel datasets present context for the info in every column, making the data simpler to learn and perceive. Nevertheless, column headers spanning two or extra rows could cause issues. Fortunately, Energy Question may help you repair this.

Why Each Column Ought to Have a Header

As a rule, every Excel column ought to be a discipline, which is a constant sort of knowledge, like ID numbers, names, or revenue. However, every row ought to be a document, containing several types of information about one merchandise, like an worker, a product, or a sports activities workforce.

Within the instance beneath, every column is a novel discipline (departments in column B, price costs in column E, models offered in column G, and so forth), and every row is a document for a person product.

A Microsoft Excel table detailing various products and their production and sales details.

As soon as you’ve got established this structure, it’s best to be sure that every column has a brief, descriptive header with the intention to simply see what the info in every column represents. Additionally, you’ll be able to add filter buttons to the header row, that means they will not get in the best way of the primary row of knowledge.

What’s extra, including a transparent column header is invaluable once you format your information as an Excel table. Particularly, once you reference column headers in formulation (often known as structured references), they’re simpler to grasp and troubleshoot than direct cell references or columns with undefined headers. For instance, this method clearly subtracts the price value within the Price column from the gross revenue within the Gross sales column:

=[@Sales]-[@Cost]

Why Column Headers Ought to Be a Single Row

In some situations, it is perhaps tempting to make use of two rows as column headers. On this instance, the primary column header accommodates years, and the second accommodates 12 months quarters.

An unformatted range in Excel containing two column headers—a year in row 1, and quarters in row 2.

Nevertheless, structuring your information this manner could be problematic for varied causes.

First, inserting a number of column headers makes your Excel worksheet much less accessible to folks with visible impairments. It’s because display screen readers depend on one-dimensional layouts to assist the person perceive the relationships between columns and rows. Deviating from this customary structure is simply going to make issues extra difficult.

In actual fact, it is not simply display screen readers and their customers who really feel the profit from a single header row. Different spreadsheet software, database applications, and Excel instruments additionally favor your information to be structured this manner. For instance, should you format your dataset as an Excel desk, you’ll be able to solely have a single header row. If in case you have a couple of, the second might be thought-about a row of knowledge.

Above all, in case your dataset has a number of column headers, it could possibly be an indication that it is not structured optimally. Going again to the screenshot above, the place years are within the first header row and quarters are within the second, the dataset has eight columns containing comparable monetary figures.

As I discussed earlier, widespread information varieties ought to be in a single column with a single header, so these monetary values ought to be grouped as a person discipline. That is how the info would ideally look:

An Excel table, with Year in column A, Quarter in column B, Shop in column C, and Profit in column D.

This construction means the desk could be simply sorted and filtered, and like-for-like values could be simply in contrast, making for extra environment friendly evaluation.

To restructure your Excel information in order that two or extra header rows change into one, you may want to make use of Energy Question. In case you’ve by no means used Energy Question earlier than, you would possibly suppose it is a advanced software that solely Excel experts can use. In actual fact, on the contrary, it is designed for user-friendliness with a simple interface, so it is a great data-cleaning platform for everybody from rookies to power-users.

First, place your cursor in any cell within the dataset with two column headers, and within the House tab on the ribbon, click on “Format As Desk.” Then, choose a desk structure.

A cell in an unformatted data set in Excel is selected, and the Format As Table drop-down menu is expanded.

Subsequent, within the Create Desk dialog field, uncheck “My Desk Has Headers,” and click on “OK.”

The My Table Has Headers checkbox in Excel's Create Table dialog box is unchecked.

At this stage, Excel would possibly get confused, because it would not acknowledge that you’ve got two header columns. So, to stop any issues arising from this confusion, within the Desk Design tab, uncheck “Header Row.”

Header Row is unchecked in Excel's Table Design tab.

Now that your dataset is formatted as a desk, you are able to load it in Energy Question Editor. To do that, after choosing any cell within the desk, click on “From Desk Or Vary” within the Knowledge tab on the ribbon.

From Table Or Range is selected in the Data tab on Excel's ribbon.

Subsequent, within the Rework tab of the Energy Question editor, click on “Transpose” to flip the desk’s columns and rows.

The Transpose button in the Transform tab of Excel's Power Query Editor.

Discover how the 2 header rows have change into columns in their very own proper. In different phrases, the row that shaped the primary column header (12 months) is now in column 1, and the row that shaped the second column header (quarter) is now in column 2.

Excel's Power Query Editor shows years in column 1 and quarters in column 2.

Earlier than you are taking any extra structural steps, take a second to fill in any blanks in these newly shaped header columns by right-clicking a column header, hovering over “Fill,” and choosing “Down.”

The right-click menu of a column header in Excel's Power Query Editor is expanded, and Down is selected in the Fill menu.

Now, you’ll be able to promote the primary row to column headers. Click on the icon within the top-left nook of the Energy Question grid, and choose “Use First Row As Headers.”

Use First Row As Headers is selected in Excel's Power Query Editor.

Among the column headers could also be incorrect at this stage. Don’t fret about this but—we’ll repair them quickly.

The ultimate main step entails flattening the dataset so that every column accommodates a standard information sort. To do that, choose the columns that have been beforehand your header rows (in my case, columns 1 and a pair of) by clicking the header of the primary, holding Shift, and clicking the header of the final. Then, right-click the header of one of many chosen columns, and click on “Unpivot Different Columns.”

The right-click menu of two selected column headers in Excel's Power Query Editor is expanded, and Unpivot Other Columns is selected.

Now, every row accommodates a 12 months, quarter, and store quantity, and every column is a constant information sort. Double-click every column header to rename it, and click on the quantity format icons to decide on the proper information sort.

A column header in Excel's Power Query Editor is named Shop, and the data type is whole number.

Lastly, click on the highest half of the break up “Shut And Load” button within the House tab on the ribbon.

The top half of the split Close And Load button in Microsoft Excel's Power Query Editor.

And that is it! You now have a two-dimensional desk with one header row, completely different fields in every column, and particular person data in every row.

Energy Question is not the one strategy to reshape your data in Microsoft Excel. For instance, PivotTables allow you to show specified columns, rows, and values, the TOCOL and TOROW features convert an array right into a single column or row, and the PIVOTBY perform is helpful for grouping and aggregating your information in a single method.