[Fixed!] Error Messages in Excel (10 Practical Solutions)

Get FREE Advanced Excel Exercises with Solutions!

Without hesitation, Microsoft Excel is one of the most compelling computer software you can use. Using Excel’s tools and functions, we can do an unlimited number of things to a dataset. However, inaccuracies in the Excel document show up regularly and excel displays an Error message beginning with a Hash (#) symbol whenever an invalid formula or function is entered. This post will teach you how to handle Excel Error messages and explain their meaning. So, to solve Error Messages in Excel, you must go through these suitable ways.

Download Practice Workbook

Following the link below, you can get a free copy of the sample workbook we discussed during the demonstration.

10 Reasons with Solutions to Fix Error Messages in Excel

Let’s use a sample dataset to illustrate this point with an example. For example, the following Dataset contains three columns: Sales Rep, Area, and Total Sales. We will construct Error Messages for the Dataset in all these ten sections and explain why they occur, as well as we will fix the problems. In addition, I have yet to mention that in this article I am writing. I have used Microsoft Excel 365‘s most up-to-date iteration in my work. You can choose any alternative edition that is superior to the others in satisfying your needs.

excel error messages

Reason 1: #DIV/0 Error

Suppose a function tries to divide by 0 or a numerically comparable number to zero, the #DIV/0! error will be generated. The error message is #DIV/0! appears in cell D12 when we try to divide the value in cell D5 by 0.

#DIV/0 Error

Solution: Divide Cell Values with Any Numbers Except 0

If you divide D5 by whatever value is apart from Zero, you will not get an Error message.

Divide Cell Values with Any Numbers Except 0

Read More: On Error Resume Next: Handling Error in Excel VBA

Reason 2: #N/A! Error

The #N/A! error means that something is currently unavailable. This error happens when the formula or a function within the procedure cannot locate the required information. The lookup routines are the most common culprits. We will search for the data in cell D12 between B5 and D10 in the provided Dataset. And still, the #N/A! error appears. By paying close attention, we may observe that the value of the D12 cell is outside of the allowed range.

#N/A! Error

Solution: Input Reference Data Correctly

Accurately enter the reference information. Here, we’ll use cell D12 as an example, where you may enter Liam. Liam will get Area information as a result.

Input Reference Data Correctly

Read More: How to Find Reference Errors in Excel (3 Easy Methods)

Reason 3: #REF! Error

Another standard error is the #REF! error occurs when a row or column referenced in a calculation is accidentally deleted. It can also happen when relative references are copied and pasted to a new position in a computation. In this instance, cells D7 and D9 are added to cell D11.

The #REF! error occurs in cell D11 when the seventh row is deleted.

#REF! Error Message in Excel

Solution: Rewrite Formula After Deleting a Column or Row

To fix this, you’ll need to retype the intended formula in cell D11. This action will result in an accurate numerical value.

Read More: How to Fix #REF! Error in Excel (6 Solutions)

Reason 4: #NULL! Error

This mistake arises when we use a Space in the function parameter rather than a Comma or a Colon. The SUM function is utilized in the given data set to sum the contents of cells D5 and D6. The #NULL! error arises because we failed to use a colon after D5, which separates D5 and D10.

#NULL! Error Messages

Solution: Replace Space with a Comma or Colon in Formula

Put Colon in between D5 and D10 to add up their values. Consequently, we will get the intended outcome in cell D11.

Reason 5: #NAME! Error

The #NAME! Error is another frequent Excel formula error. An Excel error message appears if we misspell the function name. Also, this occurs anytime Excel does not recognize the formula text. Here, we’ll look for the highest possible sales sum, between D5 and D10. Specifically, Excel has an issue with the calculation in cell D12 of the Dataset below.

#NAME? Error Messages

Read More: Reasons and Corrections of NAME Error in Excel (10 Examples)

Solution: Write Formula Name Correctly

If you substitute MAX for MX in the equation, you will receive the correct result.

Read More: Excel Error: The Number in This Cell is Formatted as Text (6 Fixes)

Reason 6: #VALUE! Error

The #VALUE! error appears if the values are of an invalid type or whenever the wrong kind of function parameter is used. This example shows an attempt to combine cell ranges D5 and C5 in cell D12. The Error occurs because the two cells do not have the same data type.

#VALUE! Error Message in Excel

Solution: Input a Valid Formula Argument

If you want to prevent this mistake, use data of the same type throughout the formula. Simply substituting D6 for C5 will get the desired outcome.

Read More: How to Remove Value Error in Excel (4 Quick Methods)

Reason 7: #NUM! Error

This Error happens when the formula attempts to operate using incorrect numerical input. The given Dataset tries to calculate the square root of the negative cell value of D5. However, we all know that we cannot calculate the square root of a negative integer. For this reason, the #NUM! Error appears.

#NUM! Error Message in Excel

Solution: Remove Invalid Numeric Data

If you take the Negative sign out of the equation right before D5, the result is the square root.

Read More: How to Calculate Mean Squared Error in Excel (3 Easy Methods)

Reason 8: ##### Error

The ##### Error is Excel’s most frequent problem. However, this is not a mathematical mistake, and this issue occurs when the column width is too narrow to display all cell values. We may find the ##### Error in column D of this Dataset.

##### Error Message in Excel

Solution: Expand Column Width

By dragging the right edge of the column, you can make the column wider and see the values better.

Reason 9: #CALC! Error

The #CALC! error occurs when a formula with an array makes a calculation mistake. For this demonstration, the FILTER function requests for WAS from the range C5:C10, which doesn’t exist. So, the Error shows up.

#CALC! Error in Excel

Solution: Provide Intended Array and Value as Function Argument

If you change Was to Washington in the formula, it will give you the expected result.

Reason 10: #SPILL! Error

An error occurs when a formula outputs a spill range extending into a cell with a value. Here, we’ll utilize the UNIQUE function to extract the unique areas for the C column. into a spill range beginning at cell E5. Yet, the provided data has already engaged E8‘s attention and the #SPILL! error therefore appears.

#SPILL! Error in Excel

Solution: Delete Preoccupied Cell Value

You may eliminate the occupied number in E8 by erasing it. as a result, the formula will produce the correct results for the individual names.

Solution of #SPILL! Error

Read More: How to Correct a Spill (#SPILL!) Error in Excel (7 Easy Fixes)


From now on, you can understand some frequent Error Messages in Excel using the tutorial we discussed. On the ExcelDemy Website, you can find many articles like this one. Keep using them, and let us know if you have any more ideas or ways to finish the assignment. Please use the space below to send any questions, comments, or ideas.

Related Articles

Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Hi there! I am Lutfor Rahman Shimanto. I have completed my graduation in Information Technology from Jahangirnagar University. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of work, I enjoy Chess a lot. I am a founding member of the Jahangirnagar University Chess Club and an internationally rated chess player.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF