If the ERROR.TYPE function in Excel is not working on your Windows 11/10 PC, this post will help you fix the issue. The ERROR.TYPE() function is one of the most useful functions for diagnosing errors in Excel. It helps identify the error by returning a specific number that corresponds to its type. For example, for the ‘#NULL!’ error, the function returns 1, for the ‘#DIV/0!‘ error, it returns 2, and so on.
Using the ERROR.TYPE() with IFERROR(), lookup functions, VBA, or custom rules makes error detection easier. However, sometimes, the function itself may not work as expected, making it difficult to troubleshoot errors.
How to use the Excel ERROR.TYPE function
The ERROR.TYPE() function has the following syntax:
=ERROR.TYPE(error_val)
where error_val corresponds to the cell or expression you want to check for an error.
To use the ERROR.TYPE() function, input =ERROR.TYPE(error_val) in a cell, replacing error_val with the cell you want to check for errors. Press Enter to display the error code.
If the referenced cell contains an error, ERROR.TYPE() returns a number as follows:
| If error_val is | ERROR.TYPE() returns |
|---|---|
| #NULL! | 1 |
| #DIV/0! | 2 |
| #VALUE! | 3 |
| #REF! | 4 |
| #NAME? | 5 |
| #NUM! | 6 |
| #N/A | 7 |
| #GETTING_DATA | 8 |
| Anything else | #N/A |
One common use case of the ERROR.TYPE() function is managing errors in formulas. For instance, if a formula in cell A1 results in an error, you can use the IF() function with ERROR.TYPE() to display a custom message, as follows:
=IF(ERROR.TYPE(A1)=2, "Divide by Zero Error", "Other Error")
The above formula checks if the error in A1 is a #DIV/0! error (which occurs when a number is divided by zero). If it is, the formula returns “Divide by Zero Error”; otherwise, it displays “Other Error”.
Fix ERROR.TYPE function in Excel not working
If the ERROR.TYPE function in Excel not working on your system, use these fixes:
- Ensure the formula is correct
- Check for errors in the referenced cell
- Check cell formatting
- Check for conflicts
- Repair Excel
Let us see this in detail.
1. Ensure the formula is correct
In Excel formulas, arguments are always enclosed within parentheses. If your function is part of a complex formula, ensure that each set of parentheses is correctly placed. Excel may highlight the issue and display an error if a closing or opening parenthesis is missing.
Also, ensure you’ve used mathematical and logical operators (+, -, *, /, =, <, >), commas (,), and semicolons (;) correctly.
2. Check for errors in the referenced cell
Ensure all cell references are valid and do not point to deleted or incorrect ranges. ERROR.TYPE() only works if the referenced cell contains an error. If the cell does not have an error, the function returns #N/A, which might seem like it’s not working.
For example, suppose cell A1 contains the integer value 10, cell B2 contains the integer value 2, and cell C1 contains the formula =ERROR.TYPE(A1/B1).
Now in C1, the formula returns #N/A because A1/B1 (10/2) is a valid calculation with no error. This is not an error in the function itself but simply an indication that no error exists in the referenced cell.
3. Check cell formatting
Certain data types like Date, Time, or custom formats may cause unexpected results when using the ERROR.TYPE() function.
For example, if cell C1 contains the formula =ERROR.TYPE(A1/B1), but it is formatted as ‘Time’, it will return 00:00:03 instead of 3, making it look incorrect.
To fix this, select the cell containing the formula and go to the Home tab. In the Number Format dropdown, check if the format is Date, Time, or a custom format. If it is, change it to General or Number to ensure the correct output.
4. Check for conflicts
Sometimes, add-ins can interfere with Excel functions. Try disabling them and see if the function works.
Click the File menu and select More > Options. Select the Add-ins tab in the Excel Options window, then select COM Add-ins from the dropdown at the bottom and click Go.
Select any unnecessary Add-ins and click Remove.
5. Repair Excel
If the issue persists, try repairing Excel.
Search for Control Panel using the Windows Search bar and open it. Go to Programs > Programs and Features.
Right-click on Microsoft 365 or Office (the name will vary depending on your version) and select Change. You will see two options. Either select Quick Repair or Online Repair and then click Repair. Complete the on-screen instructions to repair Excel.
I hope this helps.
Read: How to fix Excel not responding without losing data?
How to fix error in Excel formula?
Fixing errors in an Excel formula depends on the type of error you encounter. Ensure that the formula cell is formatted correctly and there are no typing mistakes in the formula. You may use the ERROR.TYPE() function to determine the type of error and wrap your formula with IFERROR() to display a custom message instead of an error.
How to apply iferror formula in Excel?
If you have a dataset where A1 contains the number 10, B1 contains 0, and you use the formula =A1/B1 in C2, Excel will return the #DIV/0! error. To handle this, you may use the IFERROR() function as =IFERROR(A1/B1, "Cannot divide by zero"). This ensures that instead of returning an error, Excel will display a user-friendly message.
Read Next: Fix COULDN’T REFRESH DATA TYPES errors in Excel.
