• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

10 Common Excel Mistakes to Avoid

You are here: Home / Excel / 10 Common Excel Mistakes to Avoid
September 30, 2021 by Mynda Treacy

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

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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.

center 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:

Excel merge and center

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:

non-tabular data

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.

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

tabular data

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:

dates formatted as text

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:

identifying dates formatted as text

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.

the problem with dates formatted as text

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

4. External Links

excel external link error

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:

excel functions that don't work on closed 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.

formatting whole columns

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

table 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:

cell fill to encode data

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

conditional formatting to encode data

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

custom number format to hide numbers in cells

You can still reference the numbers to calculate the count:

sum encoded data

7. Formatting Colours

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

garish colours

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:

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:

multiple records in one cell

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:

separate cells for data

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:

SUM omitting rows

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

SUM with OFFSET

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.

More Excel Posts

tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.


Category: Excel
Previous Post:new Excel featuresCool New Features in Excel for Microsoft 365
Next Post:Pivot an Unknown Number of Rows into Columnspivot unknown variable number of rows to columns

Reader Interactions

Comments

  1. Sunny Kow

    October 21, 2021 at 8:10 pm

    My final result actually looks more like a multi-colored waffle chart .
    https://www.myonlinetraininghub.com/excel-waffle-charts-with-conditional-formatting

    Reply
  2. SunnyKow

    October 21, 2021 at 7:55 pm

    I did one project a few years back where vehicle status (downtime etc) were tracked on a daily basis. It involves hundreds of vehicles. Management require that the monthly dashboard must be color coded.
    What I did was to convert their text status to numeric codes (via VLOOKUP) and then use a Pivot Table with conditional formatting to achieve the required results.
    Days (1 – 30) in the Columns area, vehicle registration numbers in the Rows area and Status number in the Values area. The color coding actually allows management to spot issues at a glance very easily (although the final result looks like a colored Rubik cube ) The technique is similar to your number 6 mistake.

    Reply
    • Mynda Treacy

      October 21, 2021 at 8:31 pm

      Nice! Thanks for sharing, Sunny 🙂

      Reply
  3. jim

    October 16, 2021 at 2:01 am

    I would now also like to add:

    long-winded formulae (extending to several lines on a wide screen),
    – involving many layers of nested IFs, IFERRORs, VLOOKUPs to whole columns, INDIRECTs and other volatiles, and links to long-gone documents
    – which have similar versions over the next few dozen columns that don’t use helper columns for repeated sections
    – that are then copied down the next 50,000 columns – just in case (and because the file wasn’t big or slow enough)

    it’s been a long day

    Reply
    • Mynda Treacy

      October 16, 2021 at 6:08 pm

      🙂

      Reply
  4. jim

    October 10, 2021 at 7:54 am

    I have a few to add:

    over-use of conditional formatting (including doing every cell in a range individually or applying to whole rows and columns)
    merging cells (over columns as well as rows)
    whole-column VLOOKUP formulae
    zooming out the sheet to 65% to see more, then increasing the font size to make it readable
    merging cells
    summing many individual cell refs when SUBTOTAL or SUMIF could be used
    CONCATENATE
    scrolling down thousands of rows to get to the end of a range or sheet (it’s just so painful to watch, I get twitchy)
    merging cells
    not using Tables (why do some people have such an aversion to using them?)
    adding a new sheet to an already bloated spreadsheet when a new month starts
    alternatively, adding a new COLUMN to a sheet every day for the past few years (with a weekly summary column)
    adding extra blank columns as spacers
    not being consistent
    clinging on to various older versions of Excel (my 1st interview question is usually what version do you use? – I can still use 2003, and earlier, but it’s painful)
    writing (uncommented) vb code to cope with many of the above and expecting it to always work

    and did I mention cell-merging? – save it for the final report if you really must (even then, just don’t)

    “but we’ve always done it this way”

    jim

    Reply
    • Mynda Treacy

      October 10, 2021 at 1:28 pm

      Nice list, Jim! I particularly liked “zooming out the sheet to 65% to see more, then increasing the font size to make it readable”!

      Reply
      • jim

        October 11, 2021 at 6:38 am

        yes, it seems to be a standard Finance trick (I’ve even put the “zoom to 100%” button on my QAT – I took the zoom slider off my status bar as I kept clicking it instead of horizontal scroll)

        sorry about the rant, but that does feel better!

        Reply
        • Mynda Treacy

          October 11, 2021 at 8:35 am

          Rants welcome 🙂

          Reply
  5. carsto

    October 3, 2021 at 12:08 am

    I have been railing at coworkers for years! My #1 rule in Excel is NEVER merge cells, #2 Never EVER merge cells and #3 if you MUST merge cells, go use word because you’re not spread sheeting.
    I was so irritated at MS for putting Merge Cells on the ribbon – why not just put Center Across Selection!

    Reply
    • Mynda Treacy

      October 3, 2021 at 9:32 am

      😀 glad we’re on the same page, Carsto! Please share the video with your co-workers.

      Reply
  6. MF

    October 2, 2021 at 12:41 am

    This is a very good topic and contents. Thanks for the list!
    May I add one to the list –
    Never add leading spaces for the effect of indentation. Use the indentation properly!
    Indeed we should always try to avoid unnecessary spaces. I am sure you know what i mean

    Reply
    • Mynda Treacy

      October 2, 2021 at 10:00 am

      Good one, MF! I’ve never seen someone do that before, but I’m not surprised 🙂

      Reply
  7. Andrew Evans

    October 1, 2021 at 4:12 am

    Wonderful list, many I too have seen with our users, especially the merging of cells for headers, the non-tabular data – sometimes data is being export from a system then they need it analysed but cannot due to it’s layout. Gaulish highlighting of data manually that means something just to them and the whole row/column and they wonder why the file is so large.
    I learnt something new the sum OFFSET trick, that I will use now a lot more often than taking the lazy way out.
    I am sure others have other Do Not’s as well – I hate no data validation being built in to worksheets and then have to analyse the data entered and you find multiple ways to spell the same item, once we had to go through amend many, many records where the person entering a comment would put the client’s name in the sentence rather than using the word client, but the rest of the sentence was basically the same – so Do Not leave out data validation.
    Do not forget to format all your data consistently and correctly.
    I wonder what others may come up with.

    Reply
    • Mynda Treacy

      October 1, 2021 at 10:44 am

      Great additions, Andrew! Thanks for sharing 🙂

      Reply
  8. SIMON SMITH

    October 1, 2021 at 1:59 am

    Brilliant!

    Reply
    • Mynda Treacy

      October 1, 2021 at 8:59 am

      Glad you liked it, Simon!

      Reply
  9. William Field

    September 30, 2021 at 11:02 pm

    This is some of the most helpful instruction on the basics of Excel use I’ve read. Every user should know these.Thanks, Mynda, for pointing the things out.

    Reply
    • Mynda Treacy

      October 1, 2021 at 9:00 am

      Thanks so much, William!

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.