Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Formula errors in excel are those errors which occur when excel cannot calculate the formula correctly or there is any mismatch in the arguments provided by the user, there are various types of formula errors in excel, to get help on which kind of formula error we are dealing with we can click on the yellow button left side of the error being displayed and it will show what is wrong with our formula.
Formula Errors in Excel (Table of Contents)
Most Common Excel Formula Errors
If you are frustrated about excel formula errors, then this article is the ultimate flat form for you. If you follow this article you will see the types of excel formula errors and the solutions to overcome those errors. We often say excel formula errors are hard to deal with. However, the truth is excel is not returning values rather because of our own mistakes excel returns errors.
Excel works on pre-determined parameters. If any of the parameters is not satisfying excel returns the result as an error.
I am sure you must have already thought about resolving those errors. But that does not serve the purpose. You need to understand why we are getting those errors. In common it is by mistake.
Top 8 Types of Formula Errors in Excel
- #NAME?: This Excel error occurs usually because of the non-existed function we are using.
- #DIV/0!: This Excel error because if you try to divide the number by zero or vice-versa.
- #REF!: This error arises due to reference missing.
- #NULL!: This error comes due to unnecessary spaces inside the function.
- #N/A: This comes because the function cannot find the requires data. Maybe due to the wrong reference is given.
- ######: This is not a true Excel formula error but because of a formatting issue. Probably the value in the cell is more than the column width.
- #VALUE!: This is one of the common Excel formula error we get to see in excel. This occurs due to the wrong data type of the parameter given to the function.
- #NUM!: This Excel formula error because of the number we have supplied to the formula is not proper.
Ok, now I will discuss one by one in detail.
Excel Formula Error Type #1 – #NAME?
#NAME? an excel formula error is when the excel cannot find the supplied function or the parameters we supplied are not matching with the standards of the function.
Just because of the display value of #NAME? it does not mean excel asking your name rather it is due to the wrong data type to the parameter.
If you notice in the formula bar instead of SUM formula we have misspelt the formula as us. The result of this function is #NAME?.
How to Fix the #Name? ERROR Issue?
In order to fix the excel formula error, look for the formula bar and check if the formula that you have entered is valid or not. If the formula spelling is not a valid one change that to the correct wording.
Type #2 – #DIV/0!
#DIV/0! Is due to wrong calculation method or one of the operators is missing. This error we are in calculations. For example: If you have Budgeted number in the cell A1 and the Actual number in the cell B1, to calculate the efficiency we need to divide the B1 cell by A1 cell. If any of the cells are empty or zero we get this error.
How to Fix the #DIV/o! ERROR Issue?
To fix this excel formula error, we need to use one formula to calculate the efficiency level. Use IFERROR function and do the calculation inside the function.
Type #3 – #REF!
#REF! the error is due to supply the reference is missing all of a sudden. Look at the below example in the cell B2 I have applied the formula as A2 + C2.
Now I am going to delete the column C2 and see the result in the cell B2.
Now I will demonstrate one more example. Look at the below image I have applied the VLOOKUP formula to fetch the data from the main data.
In the VLOOKUP range I have mentioned the data range as A:B but in the column index number I have mentioned the number as 3 instead of 2. Formula encountered an error because there is no column range 3 in the data range.
How to Fix the #REF! ERROR Issue?
Before you add or delete anything to the existing data set make sure all the formulas are pasted as values. If the deleting cell is referred to any of the cells, then we will encounter this error.
Just in case if you have deleted any rows or column or cells by accident always undo your action.
Type #4 – #NULL!
#NULL! Error is due to the wrong supply of the value to the required parameters. For example, after the wrong supply of range operator, wrong mention of parameter separator.
Look at the below image I have applied SUM formula to calculate the sum of the values in cell A2 and B2. The mistake I did here is after the first argument I should give comma (,) to separate the two arguments instead I have given space.
How to Fix the #NULL! ERROR Issue?
In these cases, we need to mention the exact argument separators. In the above image, I should use the comma (,) after the first argument.
In the case of a range, we need to use Colon (:)
Type #5 – #VALUE!
#VALUE! Error occurs if the formula is not able to find the specified result. This is due to non-numerical values or wrong data types to the argument.
Look at the image below I have calculated the commission amount based on the sales value and commissions.
If you notice the cell D6 and D8 we got an error as #VALUE!. The simple reason we got an error as #VALUE! Is because there is no commission percentage in the cells C6 and C8.
We cannot multiply the text value with numerical values.
How to Fix the #VALUE! ERROR Issue?
In these cases just replace all the text values with zero until you get further information.
Type #6 – ######
###### to be very frank it is not an error rather it is just a formatting issue. Look at the below image in the cell A1 have entered the date values.
This is due to the length issue of the characters. The values in the cell are more than the column width. In simple terms “Column width is not wide enough”.
How to Fix the ###### ERROR Issue?
Double click on the column to adjust the column width to get the full values to visible.
Type #7 – #N/A!
#N/A! is due to formula is not able to find the value in the data. This usually occurs in the VLOOKUP formula (I know you have already encountered this error).
We go some errors as #N/A in the cells E6, E8, and E9. If you look at the ranges for these cells it does not include the values against those ids.
ID 156 is not there in the range A6 to B13 that is why we got an error. Similarly, for the remaining two cells we have the same issue.
How to Fix the #N/A! ERROR Issue?
When we are referencing a table range we need to make this as an absolute reference.
Press F4 key to make it as an absolute reference.
Type #8 – #NUM!
#NUM! is due to formatting of the numerical values. If the numerical values are not formatted, we will get these errors.
How to Fix the #NUM! ERROR Issue?
We just need to fix the formatting issue of the specific numbers.
Things to Remember about Excel Formula Error
- Use IFERROR to encounter any kind of errors in Excel
- To find the type error we can use ERROR.TYPE formula.
- #REF! the error is a dangerous thing because we may not know which cell we actually referred to.
This has been a guide to Errors in Excel. Here we discuss the top 8 types of formula errors in excel and how to fix these errors along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- How to Find Null Cells in Excel?
- VLookup Function in Excel VBA
- How to Remove Space in Excel Using Delimited in text to Column?
- Excel Formula for Division
- How to Multiply in Excel?
- Excel VLOOKUP Error
- How to use VLOOKUP with Multiple Criteria?
- How to use IFERROR with VLOOKUP in Excel?
- ISBLANK Excel
- ISERROR Function
- ISNA in Excel