Seeking help with common Excel errors

[hannah87]

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

 

[Glove_Man]

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.

 

[hannah87]

Thank you for your list, and also for taking the time to respond !

 

[Toadstool]

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]

• 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.