[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.


[Fixed!] Error Messages in Excel: 10 Practical Solutions

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


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


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.


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: [Fixed!] NAME Error in Excel


Solution: Write Formula Name Correctly

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


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.


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

Read More: [Fixed!] Null Error 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.


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


Download Practice Workbook

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


Conclusion

From now on, you can understand some frequent Error Messages in Excel using the tutorial we discussed. 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


<< Go Back To Errors in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo