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