10 Most Common Excel Formula Errors and Their Tested Solutions

Spreadsheets are very important to keep data and perform calculations on them, especially in the corporate world. Since the workbook tools are mostly complex, many problems can occur. As a result of these issues, users face errors when they try to apply calculations or formulas in them. This becomes a serious problem when managing large amounts of important data.

Therefore, this guide will discuss the common Excel errors that can prevent you from applying formulas to your data. In addition, we will provide useful solutions to resolve them. You will also learn about an advanced tool that solves the damaged workbooks without affecting their data.

Part 1. How to Resolve the 10 Most Common Excel Formula Errors

As said above, any issues with the spreadsheet tool can cause serious hindrances with data management. Thus, the following sections share the extensive Excel errors list that can trouble you and the solutions to resolve them:

Error 1. ##### Problem

This common error appears when a cell's content is too wide to fit within its column. As a result, the cell shows a series of hash symbols instead of displaying the data. Mostly, users face these Excel errors while putting dates or numbers that exceed the column's width. Additionally, while you may think this is a serious problem, it can be simply solved by changing the cell’s formatting.

Solution to Solve the ##### Problem

This error can be easily resolved by adjusting the cell's width to accommodate more data. When the issue happens after inputting a date or time, right - click the cell and choose the appropriate formatting from the "Format Cells” option.

Error 2. #DIV/0!

You may face this error after trying to divide a number by zero or an empty cell, as it is mathematically impossible. Additionally, this usually happens during average calculations or percentage changes where one entry might be zero or missing. In most cases, these Excel calculation errors also suggest something missing from the provided data.

Solution to Solve the #DIV/0! Error

Resolving this error requires you to find the formula which is trying to divide your data by zero. After finding it, change it or check your data to make the corrections. Alternatively, users can use the IFERROR function to get a different result when the formula gives an error.

Error 3. #N/A

When your workbook gives this error, it means that a formula cannot find the value it's looking for. This is common in lookup functions when the desired value isn't present in the data. Simply put, it means the data you are looking for is unavailable. However, this Excel formula error does not mean the formula is incorrect but that the requested data is unavailable.

Solution to Solve the #N/A Error

As a quick solution to this problem, you can select the numeric values and change their format to number. Apart from that, you can use the lookup value for typos by manually finding it from the data. Since extra space between the data can cause this error, you can use the TRIM function to remove them.

Error 4. #NAME?

Many users face this error when the workbook program does not recognize the text in a formula. The error usually occurs due to a misspelled function name or missing quotation marks around the text. In short, it is a result of human errors and does not indicate anything wrong with the data itself. The program only flags it as an error as it does not understand the input.

Solution to Solve the #Name? Error

Since it is related to the mistakes in the entered formula, you can easily review any typing errors to resolve them. In addition, close the text strings in quotation marks if you use them in the formula.

Error 5. #REF!

When users try to apply a formula on a deleted column, they get Excel calculation errors. That's because the program cannot find the targeted data where the formula is applied and displays this error. Most of the time, it happens when copying formulas without adjusting their cell references. Apart from that, it typically appears after modifications to spreadsheets that change the original structure of data.

Solutions to Solve the #REF! Error

Ensure the formula is targeting the data that exists in the sheet. You can try recovering the deleted data by pressing the “CTRL + Z” to undo the deletion process. Upon recovering that data, re-apply the desired formula to see if the problem is resolved.

Error 6. #VALUE!

This error appears when a formula contains an incorrect data type, such as adding numbers to text or performing operations on empty cells. Hence, Excel fails the calculation because it expects different input types. This often happens when data is imported from other sources or when cells contain unexpected characters. Users typically encounter it when mixing numbers, text, and special characters in calculations.

Solution to Solve the #VALUE! Error

To fix this Microsoft Excel error, check if the correct data type is used in the formula and ensure all referenced cells contain numerical values when required. Apart from that, using ISTEXT or ISNUMBER functions can help identify incorrect values.

Error 7. #NULL!

When the spreadsheet program expects two cell references to intersect, but they don't, you get this error. This is a relatively complex error to understand, and it happens when an incorrect range operator is used in a formula, like a comma instead of a colon. Additionally, the problem is common when working with complex formulas involving multiple cell references.

Solution to Solve the #Null! Error

Ensure to review the formula and use correct range operators, like colons and commas, to solve this Excel error. Furthermore, avoid using spaces between cell references unless you want to show an intersection of ranges.

Error 8. #SPILL!

In the Dynamic Array Excel environment, #SPILL! Errors occur when a function is supposed to generate multiple outputs from a single input. It is due to a shortage of space that it becomes unable to display the data as the output area is blocked by existing data. When one or more cells in the destination range already contain a value, the function cannot display the results.

Solution to Solve the #SPILL! Error

Check the output range for any existing data that might block the results and clear it if necessary. Spilled arrays can't fill merged cells, so ensure none are merged cells within the range.

Error 9. #CALC!

The #CALC! error in Excel shows up when there’s a problem with calculations related to array formulas. This can happen when using dynamic functions or formulas with array syntax. For example, if the FILTER function doesn't work with empty arrays and does not find results, you'll see a #CALC! Error.

Solution to Solve #CALC! Error

Begin with including an “if_empty” argument so Excel knows what to do if no matching records are found. In this case, you must also ensure you're using the correct syntax for the function. Add a sample of the formula inside parentheses if you work with a LAMBDA function.

Error 10. #NUM!

Though this error does not occur very often, everyone has experienced this response at least once. The situation for this error to occur is when the calculations are not possible, or the number is too large or small to display. Additionally, this error appears when you are using invalid numeric values, such as dividing by zero.

Solution to Solve #NUM! Error

To bring the value within the range of valid Excel numbers, you should try changing the formula. Another possible solution is enabling iteration calculation, which allows multiple attempts to find a solution.

Pro Tip. How to Fix a Corrupted Excel File [Any Corruption Reason]

While the above errors can be solved by applying the given solutions, there will be cases when your spreadsheets fail to open altogether. In such situations, these general solutions won’t work, and you will have to use advanced tools that can remove the Excel errors and restore the sheet’s functionality. For that, we recommend using Repairit, as its advanced features enable users to fix their damaged spreadsheets quickly.

You can use this tool to remove all types of corruption from your spreadsheets without changing their original information. In this way, your files get repaired without affecting any formulas or tables within the file. While repairing files, it also allows you to import them in bulk to ensure work efficiency. Besides, you can preview the repaired Excel files within the tool to see if the repair process was successful.

Key Features

Wondershare Repairit - File Repair

Try It Free Try It Free

1,010,994 people have downloaded it.

• Repair damaged Excel files with all levels of corruption, including blank files, files not opening, unrecognizable format, unreadable content, files layout changed, etc.

• Repairs all kinds of data stored in damaged Excel files such as tables, charts, formulas, comments, images, etc.

• Support all formats of Excel files: XLSX/XLSM/XLTX/XLTM/XLS

• Perfectly repair corrupted files with a very high success rate, without modifying the original file.

• No limit to the number and size of the repairable files.

• Support Windows 11/10/8/7/Vista, Windows Server 2003/2008/2012/2016/2019/2022, and macOS 10.10~macOS 13.

• Except for Excel, Repairit also supports all formats of PDF, Word, Excel, PowerPoint, ZIP, and Adobe files.

Straightforward Manual to Using Repairit to Fix Corrupted Excel Files

With the discussed features, Repairit becomes a natural choice to solve the Microsoft Excel error. So, use it to fix your damaged spreadsheets by following the given steps:

Step 1. Open the file repair tool on your computer and go to the "More Types Repair” tab to access the file repair feature of Repairit

Step 2. Click the “Add” button and select the problematic workbooks from your device’s storage.

Step 3. After adding the Excel files, press the “Repair” button to fix their damaged sections. Moving forward, click any file to “Preview” it and press “Save” to export it.

Conclusion

To conclude, errors in spreadsheets can affect data accuracy and efficiency, especially when working with large datasets. These errors typically arise due to missing values or formatting issues in the cells. Once you know what these Excel errors mean, you can apply relevant solutions and solve them immediately.

In addition to formula errors, file corruption can prevent users from accessing their important workbooks. For these problems, using tools like Repairit is highly recommended, as it securely fixes the files without changing their data.