Seeking help with common Excel errors
Results 1 to 5 of 5

Thread: Seeking help with common Excel errors

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Seeking help with common Excel errors

    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

  2. #2
    Board Regular
    Join Date
    Feb 2005
    Location
    Melbourne (Australia)
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Seeking help with common Excel errors

    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.
    "I'm almost sure I'm not mad..." - Stoppard

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Seeking help with common Excel errors

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

  4. #4
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    193
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Seeking help with common Excel errors

    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.

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,437
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Seeking help with common Excel errors

    • 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 by xenou; Aug 12th, 2019 at 05:09 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •