Seeking help with common Excel errors

hannah87

New Member
Joined
Aug 8, 2019
Messages
2
Hi Everyone.

I am trying to create an error inclusive training in excel for my research, and would love your help to collect as many beginners errors as possible- those you see the most often - whether it is with formulas, functions or else. The below list is what I compiled based on online resources. These are mostly related to formulas but any thought, idea is welcome.

- errors that are an outcome of wrong data type (eg. text is mistaken as number)
- errors due to not understanding or using absolute vs relative reference
- errors due to (Mathematical and else) operators and order of operations
- errors due to inputs and the order of those any formula syntax require (order of arguments, grouping (matching brackets), separators (comma etc.), typo errors, range (correct range type), reference etc.)

Could you please share any error issue you often see newbie users doing, explain the reason behind and may provide a concrete example?

Thank you so much in advance !!!
Hanna
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Glove_Man

Well-known Member
Joined
Feb 20, 2005
Messages
578
Interesting idea

You current list is good.

- Definitely the reading text as numbers or vice versa is a big one. VALUE() is an awesome function.
- Also get errors where Excel reads dates the wrong way... As an Aussie, we use DD/MM/YY as the most common structure, but many US examples have MM/DD/YY. Usually excel works it out... not always.
- Errors (or perhaps, more correctly, inefficiencies) from not understanding how Excel stores dates. That is, if you want tomorrow's date TODAY()+1 works perfectly.
- Errors that come about from Formulas/Structures which are not robust to inserting rows or columns. VLOOKUP() is a prime candidate here.
- Errors from not realising that what you see may not be what is in the cell. For instance "Example" <> " Example "
- Not an error exactly, but newbies often fail to realise how important proper formatting can be. And how useful it is.
- Not following good practices. Sheets are much easier to use if you have single, golden-source type inputs for variables.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
823
Office Version
2016
Platform
Windows
All good points so far so here's my 2 cents:

  • Design 1: I've seen a lot of 12 worksheet workbooks, one tab for each month. Then they struggle with formulae across 12 worksheets.
  • Design 2: I've seen sheets where columns were used for multiple purposes, commonly a section heading, which makes identifying each row difficult.
  • Design 3: The Text and Number issue looms large and, while the Exceller probably can't decide this, all keys should start with an alpha. "Why didn't the LOOKUP/VLOOKUP/MATCH find my Product Code when I can see they're identical!".
  • Dates: I agree with the previous comments and would add that the Mac 1904 challenge has caught me out!
  • Rounding - Actual versus Visible: Formatting those invoice rows to two decimal places may make =ROUND(125.5,2)*15% look like =ROUND(ROUND(125.5,2)*15%,2) at $18.83 but those halfpennies will add up.


Not really errors but here's suggestions for Newbies:

  • VLOOKUP: I wish I'd started with INDEX & MATCH rather than VLOOKUP. All that wasted time, even after I remembered to include the range_lookup, when I wanted something left of my search column or I inserted a column and all my col_index_num values became wrong.
  • Tables & Names: You must still understand Absolute & Relative addressing but using Tables and Names will make so many sheets easier to develop and simpler to maintain.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,676
Office Version
2013
Platform
Windows
  • Linked workbooks that go wrong when excel files being linked are renamed or moved.
  • Formulas failing when columns or rows are added to the right or past bottom of a formula's extent (you think it's part of the formula but maybe it isn't).
  • And just as in the design issue mentioned above with workbooks using 12 tabs, the same goes for workbook files for each year - it's not always best to start new workbooks at the start of each new year.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,682
Messages
5,488,240
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top