There are many cases when Excel cannot properly evaluate worksheet formulas and functions and as a result, you start getting Error #DIV/0! #NULL! #REF! in the cell.
And in this case you might obtain help in recognizing difficulty using help button for error options, that’s showed within cells along with error formulas.
You may also like:
Here follow the complete information about the as #REF!, #NULL!, #DIV/0! errors in Excel file:
#Null Error in Excel – Improperly Divided Cell References
Principles for #NULL! the 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).
Reasons Behind 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)
Solutions: Separate cell references correctly.
- Make use of separator cell references using arithmetic operator – suppose: =A1+A3+A5;
- Use colon to separate start and end points of range ( : ) – 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).
#REF! Errors – Invalid Cell References
The invalid cell reference is caused when formula comprises of wrong cell location – rows 6 and 7 in the example above. This happens frequently when:
- Individual or complete rows and columns holding data referenced in the formula are inadvertently removed;
- data from a single cell is transferred into a cell that’s referenced by the formula;
- A formula comprising link – using OLE (Object Linking and Embedding) – to program that’s currently not operating.
- Practice Excel’s undoing attribute to recover lost data within eliminated columns, rows or cells;
- If data can’t be restored, re-enter data and regulate cell references if required;
- Open programs including OLE links and modernize worksheet involving #REF! error.
#DIV/O! – Divide by Zero Error
When the formula tries to segregate by zero, then the Divide by 0 error occurs – rows 8 and 9. This occurs when:
- 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.
- 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.
##### Error – Cell Formatting
A cell occupied with hashtags rows, pound symbols or number signs, are also called, are not termed as error value by Microsoft, but 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.
- 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;
Fully Automatic Resolution:
The above mentioned manual solution will most probably sort out the issues and mentioned errors from Excel file. But for any other corruption or file damage most suitable option would be to make use of MS Excel Repair Tool. This is is highly competent in restoring corrupt Excel files and also retrieves data from worksheet like cell comments, charts, other data and worksheet properties. This is a professionally designed program that can easily repair.xls and .xlsx files.
Steps to Utilize MS Excel Repair Tool:
Make use of the given manual solutions to fix the errors #NULL! #REF! #DIV/0! Errors in the Excel workbook. But if you are not able to fix it with the given manual solutions then go for the fully automatic solution to fix the error easily.