How to quickly fix 6 common errors when working on Excel
How to quickly fix 6 common errors when working on Excel: Excel users often encounter many error messages, but not everyone knows how to fix them. From accidentally deleting numbers to entering incorrect numbers, a single mouse click can make you start over an entire sheet. However, if you know how to read and understand these error messages, you can quickly fix these “problems” to continue working with Excel spreadsheets. This article will identify 6 common error messages on Excel and their simplest fixes.
1. Error #VALUE!
Excel requires that the formula only contains numbers. Therefore, the #VALUE! error will appear when the formula contains spaces, characters, or text.
The fix for this error is quite simple. You just need to check the formula again and make sure that the formula does not contain characters other than numbers. If you still see this error, check the empty cells, formulas that are missing links to cells, or special characters that you may be using.
2. Error #NAME?
This is a quite complex error but it is also common on Excel. The #NAME? error appears when:
- Excel cannot understand the name of the formula you entered in the cell.
- Excel cannot calculate one or more values entered in the formula.
In the example below, the formula =ROUND(E3;3) is missing the letter “N”. Therefore, Excel cannot read the function name and will return the #NAME? error after you enter and press Enter.
To fix the #NAME? error, first, check the spelling of the formula you are entering. If the formula is correct but still has the #NAME? error, Excel may have confused it with one of the items inside the formula.
The fix for this issue is to ask Excel to insert the formula for you. You can follow the instructions below:
- Mark the cell where you need to enter the formula.
- Select the Formulas tab on the toolbar. Then choose Insert Function.
At this point, the Insert Function window will appear. You select the name of the formula you want to enter, then click Go.
Excel will guide you through entering the components of the formula as below. Thus, Excel can read your function most accurately.
3. Error ######
The ###### error occurs when your cell is not wide enough to display the value you entered. The fix for this error is very simple. You can refer to the fix below.
You just need to extend the width of the column by double-clicking on the border between the column and the next column, or dragging the border to the desired width.
4. Error #REF!
Error #REF! in Excel is often difficult to identify the cause. However, one of the common causes is that the formula you entered contains a reference to an invalid cell. The invalid cell here could be a cell that no longer exists because you accidentally deleted it.
For example, in the following example, cell F3 has the formula =ROUND(E3;3).
If you delete the “Number” column, this error will appear.
If you accidentally delete the referenced cell, you can restore it by one of the following methods:
- Click the Undo button on the Quick Access Toolbar.
- Press CTRL + Z for PC or Command + Z for Mac.
Also, before deleting cells, check if there are any formulas referencing those cells to avoid encountering this error.
The fix for this error is also very simple. You just need to check which cell or row was deleted, then adjust the formula to reference the correct cell or row.
5. Error #DIV/0!
The #DIV/0! error occurs when you enter a formula that divides a number by zero.
To fix this error, you can add an IFERROR function to the formula. This function will check if the formula is dividing by zero and replace the error message with a more meaningful message or a blank cell.
When your formula contains an invalid numerical value, Excel will display the #NUM! error. For example, the =SQRT() function is a function that takes the square root of a number. The number in this function cannot be a negative number. So if you input a negative number into this function, Excel will report the #NUM! error.
In addition, if the function returns a result that is too large or too small, beyond Excel’s computing capabilities, it will also report the #NUM! error. For example, when entering 40000^400, Excel will report the #NUM! error.
When encountering this error, you need to check the numerical values in the formula to ensure that the number is valid for the formula being used. In addition, you should not calculate numbers that are too large or too small outside of Excel’s computing capabilities.
Above are the 6 basic common errors when working on an Excel spreadsheet. When you understand the cause and how to fix it, your work with Excel will become faster and more convenient. Good luck to you.