How do I fix errors in Excel?

If you've been using Microsoft Excel for a while, you must have seen those cryptic error messages appearing in your cells. Something like #REF! or #NULL!. They are supposed to help us fix mistakes, but their exact meaning is far from obvious.

In this short tutorial we will cover in detail the 6 most common error messages you can get in Excel, and how to fix them!

The #### Error

This one is easy, it means that there is not enough space in the cell to display the value.

To fix it, simply increase the size of the cell.

The #NAME? Error

This error appears when you are using a function that doesn't exist, which probably means that you made a spelling mistake.

So proofread the function name to find the mistake, and fix it!

The #DIV/0! Error

Do you know that it's impossible to divide a number by zero? And when you try to do just that in Excel, you get the #DIV/0! error.

If that happens you have 2 solutions:

• Do not divide by zero.
• Add an IF function to do the division only if it's not by zero. For example you could do something like this: =IF(E2<>0, D2/E2, "")

The #NULL! Error

This usually happens when you have an error in the syntax of your formula. Maybe you forgot a commas or a plus sign somewhere.

The only way to fix that is to have a close look at your formula to see what's wrong.

The #REF! Error

I don't like seeing this error, it means that a reference (cell or range) you are using is not valid. That can happen when you delete a column, or past stuff on top of data.

There's no easy fix, usually the best way is to start ths formula from scratch to make sure you didn't delete important data.

The #VALUE! Error

This is a classic mistake. This happens when your formula includes cells with different data types, like if you try to multiply a number by a text.

So check all the cells/range in your formula to find which cells should not be there, and remove them.

Bonus

If you really can't fix your error, it's possible to "hide" it with the IFERROR function:

=IFERROR(value, value-if-error)

Put your formula in the first parameter, and if there's an error, the second parameter will be executed.

But try to avoid this solution as much as you can, because it's much better to actually fix the error than to "hide" it like that.

Conclusion

With all this information you can be quicker to debug your spreadsheet because you know exactly what the problem is :-)

MS Excel is indeed a popular application for creating spreadsheets, keeping accounting data, and tracking day-to-day activities in organizations. But, while using Excel, many users often encounter some common errors. And it’s important to learn how to fix these errors. Otherwise you may face a lot of trouble in conducting your day-to-day activities. In this article, we’ll look at eight common errors of MS Excel and how users can resolve them.

1. ##### Error

The first common error that users often encounter is a cell filled with hashtag characters. But, it is not an actual error; it simply means that column isn’t wide enough to display the value you’ve inputted.

Fixing this error is simple, just increase the column width so that the value can be displayed in the spreadsheet.


2. #NUM Error

The NUM error occurs when an invalid number is added to the formula. If you have added the invalid number, you’ll find the NUM error on your spreadsheet. The same error can occur if the numeric value used is too small or large.



To fix this error, check if you’ve entered any formatted data like currency, dates, or special symbols in the formula. Remove any invalid characters from the formula and keep numbers only.

3. #VALUE! Error

The #Value error is displayed in Excel when it finds any spaces, characters, or text in a formula where it expects a number.



The simplest way to fix this error is to check the formulas and assure that only numbers are used in it.

4. #REF! Error

It is considered one of the most complicated error and can be tricky sometimes to figure out. The #REF error occurs in Excel when a formula references a cell that is invalid. For example, if the user removes a cell, column, or row in a spreadsheet, and create a formula with a cell name that has been deleted, Excel with display the #REF error in that cell.



To resolve this error, you must be careful while pasting a set of cells. Make sure there are no formulas that would get affected on deleting a cell. Also, if you’ve accidentally deleted a cell, click the undo button (Ctrl+Z) to recover them.

5. #DIV/0! Error

This error usually appears when users ask Excel to divide a formula by zero or an empty cell. As you all know, division by zero is not possible in the real world, then how would it be possible in Excel?



Resolving this error is pretty simple – you have to change the value of a cell to the value that is not equal to zero or add value if the cell is blank.

6. #Name! Error

The #Name error is a bit complex than others. It appears when Excel is unable to understand the name of the formula you’re trying to run, or if excel cannot compute one or more values entered in the formula itself.



To resolve this error, check the spelling mistakes in the formula. Also, type the Excel functions in lower case because when you enter a formula, Excel will convert it to upper case automatically if it is identified.

7. #NULL! Error

This error occurs when Excel specify an intersection of two areas that don’t actually intersect, or when an incorrect range operator is utilized.



Resolving this error is a bit complicated, as you’ve to verify that you’re using the proper syntax in the formula.

• Use a colon to separate the first cell from the last cell once you refer to a continuous range of cells in a formula.
• Also, you should use a comma when you refer to two cells that don’t intersect.
8. #N/A Error

The #N/A error usually appears when the number you are referring to your formula cannot be found. It can occur if you’ve deleted a number or row that is being utilized in the formula or if it is referring to a sheet that was deleted or not saved.

To fix this error, you’ve to check the entire formula carefully to find which sheets or rows were deleted or reference wrongly. If few formulas are linked together, verify that everything in every formula has value.

These are eight common errors that users often face while creating spreadsheets. However, there are some other errors also which can corrupt an excel file. Fixing these errors manual methods can be tricky, so use Excel Repair tool to automatically fix these errors.

Excel

Excel Repair software is an efficient tool that can repair corrupt/damaged excel files and retrieve data from it.

• Recovers corrupt/damaged Excel files instantly
• Restores Excel data including table, formulas, charts, cells, fields, forms, and reports
• Retains every cell’s properties and formatting along with data
• Offer dual recovery modes, i.e., single file mode and multiple file mode

Using this Excel repair tool, users can quickly repair any excel file and fix every error of an excel file.

What causes Excel errors?

What are the 5 common errors in Microsoft Excel?