Errors are usually annoying and when comes to Excel formula errors they are even worse. I am saying this because no matter how hard you work to formulate a piece of code that calculate your data, one misplaced number or click can flush that up.
So today, in this blog I am going to discuss three such Excel formula errors i.e. #VALUE, #REF and #NAME that people face very often and find it really hard to get rid of.
I am going to discuss what each of these errors are, how to find the reason that caused that error and of course, ways to fix #VALUE, #REF and #NAME errors in an easy effective way.
So, let’s get started
The #REF Error
The #REF error occurs when you enter an invalid formula in a cell reference. It can also happen when you delete a series that contains clear cell reference in the formula. Or when you overwrite it by pasting another value in it.
How to find #REF Error
In the Excel sheet Press Ctrl + F which is known as Excel find function.
Type #REF and click on Find All.
By doing this it will take you to every cell which has #REF error automatically.
Method to Fix #REF Error
This is the easiest method to fix #REF error.
- Select the Excel sheet that has a #REF error.
- Press CTRL + F a box will appear that says Find and Replace
- Select the Replace Tab
- Type #REF in Find What field and leave Replace field empty
- Click on Replace All.
By doing this, it will remove the #REF error from the formulas and also fix the issue.
Helpful Article: How to Fix #NULL! #REF! #DIV/0! and ##### Excel Errors
The #VALUE Error
Multiple causes can result in a #VALUE error as it depends on the formula that you have used. A wrong number or invalid value being listed as an argument can cause a cell to display #VALUE error. Take an example: enter =if(“a”, 1,0) this will result in #VALUE error.
Method to fix #VALUE Error
To prevent #VALUE Error and fix your Excel spreadsheet below are a few options you can try:
- Fix all the data in the cell so that the numerical data always contain numeric
- Use workaround formulas to stop #VALUE Error where the data is not value default
- Create a SUM formula using the range of cells the formula will ignore any wrong data types.
The #NAME Error
The reason behind the #NAME error is the mistyped formula name. If you entered a formula which does not exist, you will see a #NAME error. Take a look at the example:
Say =getmecheese() and enter
You will get a #NAME error, check whether the function name is spelled correctly or not and the formula is valid.
Method to Fix #NAME Error
To solve #NAME error always check the spellings of formulas you are running in Excel. If your formula spelling is correct and still your spreadsheet is showing an error, then one of the entry in the formula had made Excel confused.
- Highlight the cell where you want to run formula
- Click on “Formula Tab”
- Select “Insert Function”
- Once you clicked on “Insert Function” a Formula box will appear on the screen of a spreadsheet, in there you can select the needed formula and click OK
After completing the above steps Excel will go through each step of formula in a separate field to make sure that no errors are there and the program can read cell properly.
Automatic Solution: MS Excel Repair Tool
If you are not so technical computer person and want to solve the issue without any hassle then you can use the professional recommended MS Excel Repair Tool to fix the Excel errors. This Tool is best to repair all kinds of issues, errors, and corruption in Excel workbooks. This Repairing tool allows you to restore all corrupt excel file that includes worksheet properties cell comments, charts, and other valuable data. It is a unique tool for repairing multiple excel files at ones and restores the entire data. It is very easy to use and can be used with both Windows as well as Mac operating system. This tool supports every Excel versions.
Steps to Utilize MS Excel Repair Tool:
Hope after reading this article it is clear to you how these errors arise and the fixes given will help you to resolve #VALUE, #REF, and #NAME error.
Make sure to follow the fixes carefully to avoid further errors.
If in case you are having any queries you can tell us in the below comment or visit our Repair MS Excel page Ask Question.