Demystify Excel Errors and How to Fix Them

Cleaning data in Excel is something that I enjoy very much these days (don’t judge). While I am pondering on writing something in-depth for better management and analysis in Excel, it seems much easier to write about Excel errors first as a warm up.

So, here goes:

Problem: Frankly, 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.

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

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

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

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” (command + z in macOS) 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).

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.

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 how to fix it.

Related posts:

If you think Bitcoin is an anonymous cryptocurrency… you are totally wrong. Despite the high level of anonymity of Bitcoin, you should understand that thanks to the blockchain technology, the use of…

We all know that ChatGPT is capable of producing human-like text and may be used for a variety of natural language processing (NLP) activities, such as text production, text completion, language…

Vehicle maintenance is crucial to vehicle ownership as it ensures that your car runs efficiently and safely. Regular service not only helps to prolong the life of your vehicle but also helps to…