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

Column headers in Excel data sets provide a context for data from each column, making information easier to read and understand. However, column headers covering two or more lines can cause problems. Fortunately, Power Query can help you solve this problem.

Why each column should have a header

As a rule, each Excel column should be a field, which is a coherent type of data, such as identification numbers, names or profit. On the other hand, each line should be a recording, containing different types of data on an element, such as an employee, a product or a sports team.

In the example below, each column is a unique field (departments of the B column, cost price in the column E, units sold in the G column, etc.), and each line is a recording for an individual product.

Once you have established this arrangement, you must make sure that each column has a short and descriptive header so that you can easily see what the data of each column represent. In addition, you can add filter buttons to the header line, which means that they will not hinder the first line of data.

In addition, the addition of a clear column header is invaluable when you format your data as an Excel table. More specifically, when you refer column headers in formulas (also called structured references), they are easier to understand and help out than direct cells or columns with undefined headers. For example, this formula clearly removes the price of the cost of the crude profit cost column in the sales column:

=[@Sales]-[@Cost]

Why should column headers be a single line

In some scenarios, it could be tempting to use two lines as column headers. In this example, the first column header contains years and the second contains quarters of the year.

However, the structuring of your data in this way can be problematic for various reasons.

First of all, the insertion of several column headers makes your work sheet Excel less accessible to people with visual deficiencies. Indeed, screen readers are based on one -dimensional arrangements to help the individual understand the relationships between columns and lines. Deviation of this standard provision will only make things more complicated.

In fact, it is not only screen readers and their users who feel the advantages of a single row of header. Other calculation sheet software, database programs and Excel tools also prefer to structure your data in this way. For example, if you format your data set as an Excel table, you can only have one header line. If you have more than one, the second will be considered a row of data.

Above all, if your data set has several column headers, it could be a sign that it is not optimally structured. To return to the screenshot above, where the years are in the first row of header and the districts are in the second, the set of data has eight columns containing comparable financial figures.

As I mentioned earlier, the common data types should be in a single column with a single header, these financial values ​​must therefore be grouped as an individual field. This is what the data would ideally look like:

This structure means that the table can be easily sorted and filtered, and values ​​as for the types can be easily compared, which allows a more effective analysis.

To restructure your Excel data so that two or more header lines become one, you will need to use Power Query. If you have never used Power Query before, you might think that it is a complex tool that only experts can use. In fact, on the contrary, it is designed for conviviality with a simple interface, it is therefore a data cleaning platform ideal for everyone, from beginners to power users.

First of all, place your cursor in any cell of the data set with two column headers and in the Home tab on the ribbon, click on “Format as a table”. Then select a table provision.

Then, in the Create a Table dialog box, uncheck “My table with headers” and click on “OK”.

At this stage, Excel could be confused, because it does not recognize that you have two columns of header. Thus, to avoid any problem resulting from this confusion, in the table design tab, uncheck the “header line”.

Now that your data set is formatted as a table, you are ready to load it in Power Query Editor. To do this, after selecting any cell in the table, click on “in the table or the beach” in the data tab of the ribbon.

Then, in the transformed tab of the Power request editor, click on “Transpose” to return the columns and the lines of the table.

Notice how the two header lines have become full columns. In other words, the line that formed the first column header (year) is now in column 1, and the line that formed the second column (district) header is now in column 2.

Before taking other structural steps, take a moment to fill the whites from these newly formed header columns by right-clicking on a column header, overflowing “filling” and selecting “Down”.

Now you can promote the first line in column headers. Click on the icon in the upper left corner of the power query network and select “Use the first line as heads”.

The last major step is to flatten the data set so that each column contains a common data type. To do this, select the columns that were previously your header lines (in my case, columns 1 and 2) by clicking on the header of the first, maintaining Shift and by clicking on the header of the last. Then right-click on the header of one of the selected columns, then click on “Univot of other columns”.

Now, each line contains a year, a quarter and a store number, and each column is a coherent data type. Double-click each column header to rename it, then click on the number format icons to choose the correct data type.

Finally, click on the upper half of the “Close and load” button in the Home tab on the ribbon.

And that’s it! You now have a two-dimensional table with a header line, different fields in each column and individual recordings in each line.

The supply request is not the only way to reshape your data in Microsoft Excel. For example, seal seals allow you to display columns, lines and values ​​specified, the Tocol and Torow functions convert a table into a single column or line, and the Pivotby function is useful for grouping and aggregating your data in a single formula.

BONE

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office Applications such as Word, Excel and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.