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 calculates 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. #REF, #NUM, #NAME, #N/A, #VALUE, #NULL, #DIV/0, ##### that people face very often and find it really hard to get rid of.
I am going to discuss each of these errors and what is the reason behind the occurrence of these errors. Moreover, I will also tell you about the best fixes to resolve #VALUE, #REF and #NAME errors in Excel.
So, let’s get started…!
To repair corrupt Excel workbook, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
How To Fix Formula Errors #REF, #VALUE & #NAME In Excel?
In this section, we will learn about the 3 most commonly rendered formula errors in Excel. So, let’s catch complete information about each of these Excel formula errors in brief.
Excel Formula Error #REF
Why Your Excel Is Showing #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 references in the formula. Or when you overwrite it by pasting another value in it.
Here comes the #REF! Error message, this is quite complicated to figure out. This commonly appears when a formula references a cell that is not valid
This means that you have mistakenly pasted or deleted over a cell that was used in the formula.
For instance, the cell named “Outcome” column includes the formula: =SUM(A2,B2,C2).
And if you accidentally delete the column named “Number 2”, you will get the #REF! Error message.
How To Find #REF Error In Excel?
TRICK 1# This is the easiest method to fix #REF error in Excel.
- 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.
TRICK 2# To fix Excel #REF error you need to be careful before pasting a set of cells there are no formulas that would get affected. Also while deleting the cell’s it is essential to be sure what formulas are included in those cells?
Tip: If you have mistakenly deleted few cells, that click the Undo button on the Quick Access Toolbar or else press Ctrl +Z for Windows PC and Command + Z for Mac to recover them.
Excel Formula Error #NUM!
This Excel error is faced when the formula contains an invalid numeric value, and as a result, you can see #NUM! error message in Excel. Generally, this occurs when you enter a numeric value that is different than the other arguments used in the formula.
For instance, at the time you are entering an Excel formula, assure not to include the currency format $2,000. Instead of that enter 2000 and after calculating the formula, format the cell with currency and commas.
For example, when you’re entering an Excel formula, make sure you don’t include values like $1,000 in currency format. Instead, enter 1000 and then format the cell with currency and commas after the formula is calculated.
How To Fix #NUM Error?
To fix the #NUM! Error verify whether you entered any formatted currency, dates, or special symbols. And if you find them out remove those characters from the formula and keep the numbers only.
To easily format the numbers after removing the commas and currency from your formula, select entire column and then choose the small triangle to the icon right to select an option from the drop-down menu in Accounting Number Format.)
Excel Formula Error #NAME
The reason behind the #NAME error in Excel is the mistyped formula name. If you entered a formula that does not exist, you will see a #NAME error in Excel. 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.
How To Fix #NAME Error In Excel?
TRICK 1# Well, to fix Excel #NAME error you need to carefully check the typing mistakes in the formula.
TRICK 2# Always type Excel Functions in lower case and this way, when you enter the formula Excel will automatically convert it into upper case if it is identified.
TRICK 3# Check Entries In The Assigned Formula
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 entries in the formula had made Excel confused.
- Highlight the cell where you want to run the 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 the formula in a separate field to make sure that no errors are there and the program can read cell properly.
Excel Formula Error #N/A
The #N/A, commonly signifies that the numbers you are referring to your formula cannot be found.
It might happen you have mistakenly deleted number or row that is being utilized in the formula or referring to a sheet that was deleted or not saved earlier.
For the advanced users, the common causes of the #N/A error are that when any cell can’t be found from a formula referenced in a VLOOKUP.
How To Fix Excel #N/A Error?
Here you need to check carefully your entire formulas and make sure to closely look which sheets or rows were deleted or reference wrongly. If few formulas are linked together, verify that everything in every formula has a value.
Excel Formula Error #NULL!
This Excel #NULL error message appears when indicates an intersection of two areas that don’t intersect, or when a wrong range operator is utilized
Check out some additional context:
- Range operator (semicolon): Describes references to a range of cells.
- Union operator (comma): Is used to combine two references in a single reference.
- Intersection operator (space): Return the reference to the intersection of two ranges.
What Does #null Mean In Excel?
Principles for #NULL! Excel error happens when more than two cell suggestions are isolated wrongly or unintentionally using space formula – rows 2 to 5 in the image above.
In Excel formulas, the space character is utilized as intersect operator, which means it’s used when listing more than one overlapping or interconnecting data range- like A1: A5 A3: C3 (the cell reference A3 is a fraction of both ranges, so ranges do overlap).
Why You Are Getting #Null Error In Excel?
- Multiple cell references in the formula are alienated using space as a substitute for an arithmetic operator like plus sign ( + ) – ex: =A1 A3+A5;
- The end and start of the cell are divided by means of space instead of range operator – the colon (: ) – example: =SUM( A1 A5);
- Personage cell orientation in formula are isolated by space rather union operator – the comma ( , ) – example: =SUM( A1 A3,A5);
- Intersect operator – space character – is utilized deliberately, but specifically, ranges don’t crisscross – example: =SUM(A1: A5 B1: B5)
How To Fix #Null Error In Excel?
To fix #NULL error the first thing you need to do is to verify you are using the proper syntax in your formula
- Make use of separator cell references using arithmetic operator – suppose: =A1+A3+A5
- A colon is used for separating the first cell from the last cell once you refer to a continuous range of cells in a formula. ( : ) – example: =SUM(A1:A5)
- Divide individual formula cell reference with comma – example: =SUM(A1,A3,A5)
- Make certain that ranges at odds by space essentially overlap – example: =SUM(A1:A5 A3:C3)
- You must be making use of a comma when you refer to two cells that don’t intersect.
Excel Formula Error #DIV/0!
This #DIV/0 error message appears when you are asking Excel to divide a formula by Zero or an empty cell. Well, you can’t perform this task in you are doing division by hand, or on a calculator, then how it is possible in Excel.
What Does #DIV/0! Mean In Excel?
The #DIV/0! error appears when any formula tries to get divided by zero. Excel #DIV/0! error gives a clear indication that something unexpected or some data is missing in Excel spreadsheet.
Generally this error encounters when data is entered Excel cell but it’s not complete. Suppose, worksheet cell’s is empty because of no data is present in it.
Reasons Behind Getting #DIV/0! Error In Excel
When the formula tries to segregate by zero, then the #DIV/0! error occurs. Other reasons are as follows:
- the denominator or divisor in division operation equal to zero – either unambiguously – like =A5 / 0– or as a result of a subsequent calculation that has zero for the outcome;
- a cell referencing formula that’s blank.
How To Remove #Div/0 In Excel?
Well, this is not a big issue and can be resolved easily. All you need to change the value of the cell to the value that is not equal to 0 or else inserts a value if your cell was blank.
- Verify that you have accurate data in cells referenced within the formula.
- Confirm whether data is into correct cells or not.
- Ensure that the formula uses correct cell reference.
Excel Formula Error #####
Well, this is quite a scary message rather than the other Excel error messages. But no need to PANIC, as this, simply means that column is not wide enough to show the value you have entered.
What Does ##### (hashtag) Error Mean In Excel?
A cell occupied with hashtags rows, pound symbols or number signs, are technically not termed as an error value by Microsoft. Instead of that,##### Error In Excel are considered to be resultant of the length of data put within the formatted cell.
As a result, a row of ##### happens on several occasions, as mentioned below:
- Inscribed value is wider than width of present cell width a cell formatted for dates or times- row 10 in image over;
- a formula pierced into cell formatted for numbers generates an outcome that’s wider than cell;
- Dates and times within Excel should be positive figures- row 11 in the image above.
How To Fix ##### Error In Excel?
- Extend the harmed cell by stretching column (individual cells can’t be widened lacking broadening complete column);
- Abridge length of data in the cell if probable or prefer a dissimilar format for cell-like General;
- Now, Approve the time or date value in an affected cell so that end result isn’t negative;
- Make corrections into a formula that brings a negative date or time value for displaying in the affected cell.
- To fix the Excel error ##### simply click on the right border of the column and drag it to increase the width or else simply click twice on the right border of the header column this will automatically fit the widest cell in that column.
Excel Formula Error #value
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 a #VALUE error. Take an example: enter =if(“a”, 1,0) this will result in #VALUE error.
How To Fix #VALUE Error In Excel?
To fix #VALUE Error in Excel below here are a few options that 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.
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 repair 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 once and restores the entire data. It is very easy to use and can be used with both Windows as well as Mac operating systems. This tool supports every Excel version.
Steps to Utilize MS Excel Repair Tool:
Bonus Tips To Reduce Excel Errors
Here check out few helpful tips that will help you reduce the chances of getting Excel error messages. Make use of these precautionary tips to avoid errors and work easily in Excel.
- Utilize the * symbol for multiplying numbers, instead of X.
- Match entire opening and closing parentheses they should be in pairs.
- Always beings the Excel formula with a (=) equal sign.
- Make use of the quotation marks around text in formulas.
Hope after reading this article it is clear to you how these errors arise and the fixes given will help you to resolve #REF, #NUM, #NAME, #N/A, #VALUE, #NULL, #DIV/0, ##### 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 I assure you to fix it soon.