In this article, we will learn about why #NULL! occur And How to Fix #NULL! in Excel.
In excel, at times we get #NULL! error. This error occurs when there is something important missing with range which can't be identified in the formula you have supplied to the cell and is recognized by Excel by showing the error. As per Microsoft official site a #NULL! is Excel's way of saying, the range supplied is not valid in the formula you provided.
If you are using cell reference to array, if it's not in valid format this error suggests you to check some points discussed below. This usually happens when you try to manually type the formula and wrong character is used instead of the required character.
Let’s understand how this error occur and how to fix them with some examples. First of all check the formula in the cell for each and every character carefully.
Wrong character instead of colon ( : )
The most basic reason of #NULL! error is when the formula used in the cell doesn't support the excel format. Generally Excel has predefined format for the formula which it accepts only if that format is followed. Here #NULL! error suggests that array provided as cell reference is not followed by the correct format. See the below example to understand more.
For example, the SUM function adds up all the values given in as input. Here the range (A2:A21) to add up is provided as cell reference.
Look in the above snapshot and realize the mistake just happened. The above formula has space character instead of Colon ( : ) character between two cell reference ( A2 & A21 ).
How to fix it!
You can fix the error for the result. See the below format type you can use in excel. See the following excel correct format shown below.
First Method:
Select and drop down till required cell. Using cell reference correct format for providing range in the formula.
In the above gif, the range is provided. Follow the below explained steps to avoid errors.
Second Method:
Using the named range option in excel. Name the range using the named range attribute of excel. In the gif below shown, is a type of named range.
In the above gif named range has been assigned and used in the formula. Follow the below steps to avoid having #NULL error.
As you can see the #NULL! Error can be handled when you use correct Excel format while using range in the formula.
Wrong character instead of comma ( , )
The most basic reason of #NULL! error is when the formula used in the cell doesn't support the excel format. Generally, Excel has a predefined format for the formula which it accepts only if that format is followed. Here #NULL! error suggests that cells provided as cell references is not followed by the correct format. See the below example to understand more.
For example, the SUM function adds up all the values given in as input. Here all the individual cell values needed to be added.
Look in the above snapshot and realize the mistake just happened. The above formula has space character instead of Colon ( , ) character between two cell references ( B2:B7 & C2:C7 ).
How to fix it!
You can fix the error for the result. See the below type format you can use in excel. See the following excel correct format shown below.
First Method:
Use Comma character ( , ) every time when two cell references or named references are used in the same formula. Comma character ( , ) is understood by excel as two different ranges references.
In the gif shown above, the range is provided with the correct format to the function. Follow the steps explained below to avoid errors.
Second Method:
Using the named range option in excel. Name the range using the named range attribute of excel. In the below gif shown is a type of named range.
In the above gif named range has been assigned and used in the formula. Follow the below steps to avoid having #NULL error.
As you can see the #NULL! Error can be handled when you use the correct Excel format while using range in the formula.
Hope this article about how Why #NULL! occur And How to Fix #NULL! error in Excel is explanatory. Find more articles on Excel error functions here. Please share your query below in the comment box. We will assist you.
Related Articles
#VALUE Error And How to Fix It in Excel
How to Remove Text in Excel Starting From a Position
Create drop down list in excel with colour
Remove leading and trailing spaces from text in Excel
Popular Articles
If with conditional formatting
Join first and last name in excel
Count cells which match either A or B
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
