10 Common Excel Mistakes to Avoid (2024)

I’ve received a lot of Excel files from our members over the years and I see the same mistakes time and again.

In this post I’m going to list the 10 things you should never do in Excel and what to do instead to avoid trouble when working in Excel.

Watch the Video

Download Workbook

1. Merge & Centre Cells

99% of the time you don’t need to merge and centre cells!

The problem with merged and centred cells is they interfere when selecting a range of cells, which is super annoying.

Plus, they can prevent you from sorting data and copying and pasting and can cause formula errors.

Solution: use Centre Across Selection.

It looks identical to merged cells, but it doesn’t have any of the limitations.

In the image below you can’t tell the difference between the merged cells and those using centre across selection:

Note: there’s no equivalent for centre across selection for vertical alignment, so you may still need to use Merge and Centre for merging across rows.

2. Non-Tabular Layout

One of the BIGGEST mistakes I see is data in the wrong layout like that below:

At first glance the layout actually looks quite good, however the year values are split across multiple columns.

When you do this, it makes it difficult to use the built in tools like PivotTables and functions designed to work with data in a tabular layout.

This is just one example of the wrong data layout. There are many more here examined here : Excel tabular data format.

Solution: The correct layout is a tabular format with just one column for the values and another for the year:

Thankfully, you can easily fix this data layout using Power Query to unpivot the year columns.

3. Dates Formatted as Text

Dates formatted as text, which usually come from files exported from external systems, can be tricky to detect because they look the same on the face of the cell and even in the formula bar:

However, if you use the keyboard shortcut CTRL+Back Quote, dates entered correctly will display their date serial number as shown below in column D:

To revert back to the date format, CTRL+Back Quote again.

The reason dates formatted as text are problematic is because you can’t reference them in formulas.

For example, if I wanted to calculate the number of days between the first and last dates I get an error with the text dates, but the proper date serial numbers correctly calculate.

Solution: Convert text dates to date serial numbers. Click here for several ways to fix dates formatted as text in Excel.

4. External Links

That error warning is so frustrating that I could just leave it at that, but it’s also important to point out that the following functions do not work when referencing closed external workbooks:

Solution: If you need to reference data in an external file, then a better option is to use Power Query to get the data and bring it into your file. You can then refresh the query without opening the external file to get any updates and your formulas won’t break.

5. Formatting Whole Columns/Rows

It’s quick and easy to apply formatting to the whole column or row, but this just adds unnecessary data to your file, making your file size bigger than it needs to be.

Solution: Format your data in an Excel Table (CTRL+T) and choose a format from the style gallery:

Or if you prefer to set your own format, choose ‘None’ (top left option in the style gallery). When you apply formats to the column of a table, they’re automatically applied to any new rows you add.

6. Formatting to Encode Data

It’s common practice to use cell fill colours to encode data.

The problem with this is you can’t reference cell fill colour in formulas, which makes it difficult to count data that matches a fill colour.

Take the table below, we can’t easily count how many days of the week are shaded blue:

Solution: use numbers or text in the cells and apply conditional formatting that shades the cells containing a specified value:

I’ve used custom number formats in the conditional formatting rule to hide the numbers on the face of the cell:

You can still reference the numbers to calculate the count:

7. Formatting Colours

There’s no need to use garish colours to highlight data:

Instead of bringing attention to the data, it makes your reader uncomfortable and the information difficult to read.

Solution: keep it simple and use complementary colours:

8. Multiple Records in One Cell

It can be tempting to shove as much data in a single cell as possible, but it’s difficult to then analyse that data with formulas.

Taking the example below, you might want to know how many people are listed for Wednesday:

With the above layout, it’s very difficult do anything but count by eye. Whereas a layout with separate cells for each day, you can easily add a formula to count the number per day:

Note: the above layout is the final 'report' view. Ideally you will store your data in a tabular format as per point 2, then use a PivotTable or formulas to generate the report view above.

9. Sum Ranges Omitting Cells

Adding new rows of data above a SUM formula can sometimes result in the new row being omitted from the SUM.

To be fair, this was more of a problem in earlier versions of Excel, however it does still happen in Microsoft 365, as you can see in the example below:

Solution: Use OFFSET to return the last cell reference in the range:

10. Using .xls File Type

The .xls file type was replaced with .xlsx from Excel 2007 onward, however many third party systems still have an option to export data to .xls.

.xls files are based on the Binary Interchange File Format (BIFF) and store information in binary format. Whereas .xslx files are based on Office Open XML format that stores data in compressed XML files in ZIP format. The underlying structure and files can be examined by simply unzipping the .xlsx file.

Solution: make a copy of the file and save it as a .xlsx file type.