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”).

- Function
**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