Cleaning data in Excel is something that I enjoyed very much these days (don’t judge). While I am pondering on writing something more in-depth about how to organise your data for management and analysis, it seems much easier to write about Excel errors first as a warm up.
So, here goes:
- ######
- Problem: This is not even an error. It simply means the column width of the spreadsheet is too narrow to show the data within a cell.
- Solution: Give your column some breathing space by dragging the width of the column wider.
- #NULL!
- Problem: This is possibly due to a space found in your excel formula.
- Example: Say you want to divide cell A1 by A2, instead of typing =A1/A2, you omitted “/“ and typed =A1 A2 instead. Excel will then throw you a #NULL! error.
- Solution: Look for any space in your formula to fill it back with the appropriate symbol (e.g. +, -, *, /) or range (e.g. A2:A5 function that cover cells of A2, A3, A4 and A5).
- #NAME!
- Problem: More commonly occur than #NULL!, it means either you misspelled something in your function or “typed” something wrongly. (“Typed”something wrongly as in describing the type of data, e.g. is “20210126” a serial number or a date “26th January, 2021”?), you will need to define it for excel to understand.
- Example:
- Instead of =SUM(A2:A5), you entered =SUN(A2:A5)
- Instead of =UPPER(“radio”), you entered UPPER(radio). BTY UPPER() is used when you want to make all letters of a word to become uppercase. For example, when you want to make sure all last names put in a column are uppercase.
- Solution: Check your spelling. Most function in Excel can be read like English, a closer look at the formula should be easy enough to find the typo. For typing error, anything that is a word within a function should be enclosed with a pair of “double quote”.
- #VALUE!
- Problem: Any function that can’t get you a readable result could fall into this error.
- Example:
- Function =1+”one” won’t get you 2, but #VALUE!.
- Function =Spider+-man won’t get you Spider-man, but #VALUE! (For this you need to use =concat(“Spider”, “-man”).
- Solution: Check the referenced source of your function, are they “apple to apple” (or “🍎 to 🍏”) ? Sometimes, Excel seems smart enough to understand =1+”1” (where “1” is a text, not a number) means 2, but this is not recommended. Instead, you should use =VALUE(“1”) to convert “1” into a number, hence =1+VALUE(“1”)).
- #REF!
- Problem: The cell you referenced in a function was lost, this mostly happened when you either move or delete the source of a function. Hence, the function cannot be run (sometimes, it could because of VLOOKUP, see here)
- Solution: This is a tricky one, because you might already lost the source that you originally referenced in a function. If you found it on the spot, hit “Control + z” to undo. If you pass that, it is very likely you need to rewrite your function. To save your future work from this error, remember to create a backup of your raw data before start writing any fancy function for analysis, i.e. to create two files: one is raw data that you never write function in it, another is a working file for analysis).
- #DIV/0!
- Problem: It is a mathematical error, anything divided by 0 will result in error, hence shown as #DIV/0! in Excel.
- Solution: Check your function to see if any values were wrongly divided by 0.
- #SPILL!
- Problem: The calculated result was meant to be displayed across a number of cells. However, one of those was filled with text/function that results of the function cannot “spill” over to the designated cells.
- Solution: Delete/Cut away cells that already filled with values.
Of course, these examples are not exhaustive. It meant to provide a general idea on how would Excel throw you an error and some ideas on how to fix it.
Reference: https://exceljet.net