Sometimes the situation occurs when Excel fails to appropriately evaluate functions or formula of the worksheet, then you may come across an error value as #DIV/0! #NULL! #REF! – Within cells where the formula is placed.
You might obtain help in recognizing difficulty using button help for error options, that’s showed within cells along with error formulas.
You may also like:
#NULL! Errors – 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).
#NULL! The error arises when:
- Multiple cell references in formula are alienated using space as a substitute of an arithmetic operator like plus sign ( + ) – ex: =A1 A3+A5;
- The end and start of 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
Divide by 0 errors is faced mainly when formula tries to segregate by zero – rows 8 and 9 in the image above. This occurs when:
- the denominator or divisor in division operation equals 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;
- a text or number data, in a surplus of 253 characters, is inserted into cell formatted for numbers dates, times, or accounting;
- a negative figure dwells in a cell that’s formatted for times or dates. 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 cell if probable or prefer a dissimilar format for cell like General;
- Approve the time or date value in affected cell so that end result isn’t negative;
- Make corrections into formula that brings negative date or time value for displaying in 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 fix the error easily.