12 COMMON EXCEL ERRORS AND FIXES

Using excel files and encountering excel error messages is somewhat disturbing as that may impede the flow of your work. This article is all about different excel errors and their possible fixes.

Dec 03,2019 • Filed to: Recover & Repair Files • Proven solutions

0
I have encountered a data entry problem when I try to enter a formula for a spread sheet. I keep getting the #Name error. I tried numerous times and in slightly different ways to enter the formula the way excel does, but keep getting this error. How to fix it?

Working on excel sheets? One or other day, you face these excel errors as they are going to pop up as the excel error messages on the computer screen. Following is the list of all those excel errors along with their effective solutions to maintain your work flow on spread sheets error free.

Let’s check out each of the excel error and their solutions.

Insights about Excel errors:

Here comes the point to ponder upon, these formulas when applied to columns and rows packed with data, sometimes causes certain excel errors that impede the excel spreadsheet functioning and alter the output.

For every formula error occurring the excel spreadsheet, it carries a certain reason behind it. You cannot skip these errors but you can for sure take precautionary steps to rule the chances out to get these excel errors.

Following is the list of excel errors that are usually confronted while working on spread sheet along with the handy solutions to rule them out efficiently.

Here we go!

Part 1. 8 Common Excel Formula Errors and Fixes:

Here comes the list for 8 most common excel errors occurring in the spreadsheet. Have a look! Associated to them, there is a prompt solution to fix them effectively and to keep things going.

Let’s get started!

1- #Value!

This error will pop up principally in light of the fact that the formula contains various kind of values. One can confront this error when one or more cells in spreadsheet contains text instead of numbers for mathematical manipulation. Other causes may involve the OFFSET function in the cell. For example:

The image beneath shows such a model. Here Cell A2 carries a text value, while cell B2 contains a number. In this manner, you will see the "#VALUE!" error in cell.

value-excel-error-1

Method to fix #Value error:

Easiest of the solution to fix this excel error is to use numbers instead of text in the spread sheet for applying mathematical operation. If you still face the excel error message, then check out for the blank cells, missing formulas or use or special characters2 in cells. As this may also cause this excel error to pop up.

2- #Name:

This is the excel error which appears when you have incorporated the wrong name for the operation to be applied. Incorrectly typed range name, and reference to deleted range name or no quotation marks applied in text string along cause the #Name excel error in spreadsheet.

For example:

If you have to use the AVERAGE formula but you typed ‘average’ in text box, this for sure is going to cause this error.

name-excel-error-1

Method to fix #Name error:

You just have to spell the function correctly and it will rule out the #Name excel error. If still your spreadsheet is showing this error, Excel is presumably getting tricked because of more than one of your entrances inside the formula A simple method to fix this is to have Excel to add the function for you. You just have to highlight the cell wherein you need to run the formula, at that point click the "Formulas" tab in the top ribbon. Select "Insert Function" (If you are using Microsoft Excel 2017, this option is present on the left of top ribbon).

3- #DIV/0:

This is the most common form of excel error. If the number present in the cell is divided by zero or in case there is any empty value in cell, you are going to see this error on screen.

For example:

Here you can see the function of DIV applied in the formula bar. But in B3, there exist no value. Hence popping up the #DIV/0 error.

0-excel-error-1

Method to fix #DIV/0 error:

All you can do to fix this excel is to replace the zero value in cell with any non-zero number. Else you can also add a value if the error exist due to the empty cell.

4- #N/A:

This excel error refers to i.e. No value available. When applying function to any cell and there is exist no value cell, this error appears. Although on the technical front, this is not the actual formula error rather it is something manually placed in the cell for showing that there is no specific value to be placed in cell.

For example:

When a VLOOUP function is applied in range and there is exist no certain value in cell to which this function is applied.

A-excel-error-1

Method to fix #N/A error:

All you can do to fix the error is to cross check each column and cell that data is placed correctly and there exist no cell without any data.

5- #####:

Difficult to understand? This excel error is accordance to the width of cell in spreadsheet. If the data to be placed in cells is exceeding the width range of the cell, excel is probably going to display this error. It will not show all the characters rather the number sign is going to appear in the cell.

For example:

As the number in the cell A1 is 1231068, and the width of cell is not larger enough to show up all the numbers, hence, excel will show this error.

#####-excel-error-1

Method to fix ##### error:

For fixing this excel error, all you have to do is to double tap the column’s header and this will automatically increase the width of cell to accommodate all the number of data.

6- #Null:

Here is error pops up when you have specified intersection between two cell ranges but originally there exist no actual intersection at all. The error occurs when you insert a space that refers to intersection instead of comma which explains about union operator between ranges of cells.

For example:

When we forget to place comma between different arguments or cell ranges.

null-excel-error-1

Method to fix #Null error:

You need to check the following details to fix this excel error.

7- #NUM:

The NUM error shows the problem is associated to the number used in the formula. Any invalid argument in function or the number be too large or too small to be placed in formula are the reasons behind this error.

For example:

Here is this formula =2^12345. The result of this is infinity and the Excel is show the #NUM error.

num-excel-error-1

Method to fix #NUM error:

Avoid using the special characters in the formula s they are not going to be manipulated by the Excel function working.

8- #REF:

As the name implies, this excel error is about the invalid cell reference. In case you have deleted the cell that is linked to specific formula working or you have pasted another cell over it that have no link to the formula, the spreadsheet is going to show you the error.

For example:

At first hand row three contains data representing the month of February, if this cell is deleted, the #REF error is the result.

ref-excel-error-1

Method to fix #REF error:

If accidentally the cells are deleted which are linked to formula, all you can do is to click Undo on the Quick Access Toolbar (or press CTRL+Z for PC / Command + Z for Mac) to restore them.


Part 2. 4 Excel File Error Messages You Hate to See

After the common excel errors you happen to confront, there comes certain excel error messages that appears on screens and show you that your work is no longer going to proceed smoothly. Here are some for those excel error messages along with their reasons and efficient solutions to fix them at first hand.

1. Excel cannot open the file ‘(filename)’.xlsx error

This error message appears when the file you are trying to open is not compatible with excel as if it is corrupted or damaged. Other reason may include the invalid extension to be matched with the format of file. So let’s just have a quick fix for it.

Step 1: Start with opening the Excel in your device and heads to the File tab in it.

Step 2: Select the option of Export and then click Change the File type.

Step 3: Here you can change the file format and save the new file.

excel-cannot-open-file-error-message-1

2. Microsoft Excel Error – “The file is corrupt and cannot be opened”

Most of the times, when you upgrade your excel, one can see this error message as a result. You just need to make some changes in settings and you will be set back to work again.

Step 1: In Excel, Click the File menu and select the Option.

Step 2: Next select, Trusted Centre in Options menu

Step 3: Click on Trusted Centre Settings

file-is-corrupt-cannot-open-error-message-1

Step 4: Open a new window with Protected View

Step 5: Unselect all the options and press Ok.

file-is-corrupt-cannot-open-error-message-2

You are all done!

3. The document caused a serious error the last time it was opened

When last time you opened the file, it causes serious errors. This probably because the file is included in the list of disabled files. But this is not a big deal. You can fix this excel error message with these steps:

Step 1: In Excel, just click File, click Options, and then click Add-Ins.

Step 2: Next in the Manage list, click COM Add-Ins, and then click Go.

Step 3: In this COM Add-Ins dialog box, clear the check box for any one of the add-ins in the list that's selected, and then click OK.

Step 4: Restart the application and you are all done with fixing the error.

document-cause-serious-error-message-1

4. There was a problem sending the command to the program

This error message occurs when the process running in the excel file is not letting the Excel to close. Let’s fix this one with these steps:

Step 1: Start with selecting File and then click Options.

Step 2: Select Advanced, and then scroll down to the General section, where you can clear the Ignore other applications that use Dynamic Data Exchange (DDE) check box in the General area.

problem-sending-command-to-program-error-message-1

Step 3: Select OK and you are done.


Part 3. How to Fix Excel File with Error Messages through Stellar File Repair

Stellar Excel File Repair is always a good option to go for. Being a potential excel file repair third party application, Stellar excel file repair helps to fix the error messages easily.

Here is the step wise procedure:

Step 1: Download and run the Stellar excel file repair on your device version.

Step 2: Now select the damaged Excel file to be fixed

Step 3: Click the Scan button and start the scan

Step 4: The software runs the scan and repairs the damaged file showing excel errors

Step 5: Once done with the repair, you can have the preview of the recovered file and save it on preferred location

stellar-phoenix-excel-repair-1

Amy Dennis

staff Editor

0 Comment(s)
Home | Resources | Recover & Repair Files | 12 COMMON EXCEL ERRORS AND FIXES