5 Ways to Improve Data Structure in Microsoft Excel

Well-structured data in Microsoft Excel improves accuracy, facilitates more effective analysis, and is easier for not only humans but also machines to understand and interpret. Following these five tips is guaranteed to make your life easier, your coworkers happier, and your data pop.

1

Use Fields (Columns) and Records (Rows)

Fields and records are fundamental elements of Excel’s grid structure. Each column should be a field, which is a consistent type of data, like names, email addresses, or sales totals. On the other hand, each row should be a record, containing different types of data about one item.

See the example below, where each column is a unique field of consistent data types, like departments (text values) in column B, cost prices (accounting values) in column E, and units sold (numerical values) in column G. Each row, however, is a record for an individual item.

Think about it this way. When you double-click the fill handle in the bottom-right corner of a cell containing a formula, Excel applies the same principle downward to all the remaining cells in the column. In other words, the program expects you to have like-for-like data in a column and, thus, the same formula applies to all items in a field.

One way to make your data even clearer using this grid structure is to ensure the leftmost field is an identifier, like a name, an ID number, a serial number, or something similar, from which you can then scan across an individual record to see the corresponding values.

To keep your data neat and tidy, each field and record should be unique. Of course, you may have duplicate values, but if you scan across a whole row or down a whole column, there should be no whole-record or whole-field duplicates.

2

Each Cell Should Contain a Single Data Point

A surefire way to break the linear structure of an Excel dataset is to cram more than one data point into a cell.

My rationale for this advice brings me back to my previous point—each cell should contain an individual piece of information representing the intersection of a field and a record. So, if you type more than one piece of information into a cell, you’re breaking this “flat” structure.

In this example, a separate Price column should be added to the data to account for the variable pricing structure.

Because the data is not structured correctly, when it’s visualized as an Excel chart, turned into a PivotTable, or loaded in Excel’s Power Query Editor, it doesn’t work as hoped.

Similarly, if you reference these overloaded cells in formulas or try to sort and filter your data, you’ll probably end up with confusing and unreliable results.

The one-cell-one-data-point principle also applies to merging cells. To me, merging cells in an Excel spreadsheet is like using Comic Sans MS in a formal Word document—they should both be avoided at all costs! As soon as you merge two or more cells into one, you’re moving away from Excel’s fundamental grid structure, meaning you’re going to cause more problems than you solve. If adjacent cells contain the same value, that’s fine! There’s simply no need to merge them together.

3

Avoid Blank Columns and Rows

Each single dataset in Excel should be made up of contiguous columns and rows. In other words, make sure there aren’t any entirely blank columns or rows between fields or records.

There are many benefits to ensuring continuity across a dataset, the most significant being to help Excel understand where a data range begins and ends. As soon as you have a blank column or row in the middle of your range, you’re creating a physical break in the relationships between field and record entries.

Blank columns or rows can lead to problems when you’re presenting your data in charts. Also, they’re a guaranteed time-waster, as you’ll need to manually select all the cells in your dataset when formatting it as an Excel table, loading it in Power Query, or turning it into a PivotTable. On the other hand, to do these tasks with a completely contiguous dataset, you only need to select a single cell.

Even if you’ve inserted a new column or row that will be populated at a later date, find a way to tell Excel that it’s meant to be there and has value, like adding a column header or inputting a series of temporary zeros.

“But how about when grouping columns or rows?” I hear you ask. It’s true that grouped columns must be separated by at least one column, and grouped rows must be separated by at least one row. However, these don’t have to be blank: instead, turn them into valuable ranges by calculating subtotals.

4

Format Your Data as an Excel Table

One way to keep your Excel data structurally sound and in line with what the program expects you to do is to format your dataset as an Excel table. Aside from their structural benefits, Excel tables offer many other advantages, like auto-expanding ranges, structured references, quick sorting and filtering, and many more.

First, ensure each column is a separate field, each row is a record, each cell has one data point, and there are no blank rows or columns. You will also benefit from having column headers at the top of your data. Also, if there’s more than one dataset in your workbook, make sure there’s at least one clear column or row between them.

Next, select any cell in the dataset, click “Format As Table” in the Home tab on the ribbon, and choose a table style.

Then, in the Create Table dialog box, check “My Table Has Headers,” and click “OK.”

The data is now nicely formatted as an Excel table, and you can change its style or options in the contextual Table Design tab on the ribbon.

5

Use a Single Row for Column Headers

Adding a column header row to a dataset in Excel is a good way to provide context for the data in each column, making the information easier to read and understand. A header row also provides an extra row for a filter button, saving you from having to add it to the first row of data and potentially causing confusion.

What’s more, clearly defined column headers are invaluable when you format your data as an Excel table. Indeed, when you reference column headers in formulas—also known as structured references—they’re easier to understand and parse than direct cell references or columns with generic header names.

For example, it’s clear to see that this formula subtracts the cost price in the Cost column from the gross profit in the Sales column to produce a net profit:

=[@Sales]-[@Cost]

That’s all well and good, but why should column headers only be a single row?

One of the main reasons is that screen readers rely on clear, one-dimensional layouts to understand relationships between columns and rows. Indeed, multiple header rows can complicate navigation, causing those with visual impairments difficulty in understanding your worksheet’s structure and contents.

In fact, it’s not just screen readers and their users who will benefit from a single header row—other machine-based systems within Excel itself also prefer your data to be structured this way. For example, if you format your data as an Excel table, you can only have a single header row. Indeed, if you have more than one, the second could be considered a data row (record).

If your dataset already has two or more header rows, don’t fret—you can fix this using Power Query.

In this example, there are two column headers—the first is the year, and the second is the quarter.

As I mentioned earlier, common data types would ideally be in a single column as a field with a single header, and each row would be a separate record. So, this is how the data should look when structured properly:

To achieve this, place your cursor in any cell in the dataset, and in the Home tab on the ribbon, click “Format As Table.” Then, choose a style.

Next, in the Create Table dialog box, uncheck “My Table Has Headers,” and click “OK.”

At this point, Excel doesn’t recognize that you have two header columns, so to remove this confusion, in the Table Design tab, uncheck “Header Row.”

Now, you need to load the table in Power Query Editor. After selecting any cell in the table, click “From Table Or Range” in the Data tab.

In the Transform tab of the Power Query Editor, click “Transpose” to effectively flip the table’s columns and rows.

As a result of this action, the two header rows have now become columns in their own right. Here, the row that formed the first column header (year) is now in column 1, and the row that formed the second column header (quarter) is now in column 2.

Now, fill in any blanks in your columns by right-clicking a column header and selecting Fill > Down.

You’re now ready to promote the first row to column headers by clicking the icon in the top-left corner of the grid, and selecting “Use First Row As Headers.”

Next, select the columns that were previously your two header rows by holding Ctrl as you click them. Then, right-click the selected columns, and click “Unpivot Other Columns.”

Notice how each row contains a year, a quarter, a shop number, and a profit. Double-click each column header to rename it, and click the icons to choose the correct data type.

Finally, click the top half of the split “Close And Load” button in the Home tab on the ribbon.

And there you have it! A two-dimensional table with one header row, correctly defined fields in each column, and individual records in each row.

As well as developing good practices when structuring your Excel data, it’s just as important to ditch some of your bad spreadsheet formatting habits, like manually aligning numbers, not using a recognized date format, and using different fonts. Get a handle on these two areas, and you’ll be proud of your Excel workbooks!