What Is Troubleshooting Excel Formulas?
The Excel Troubleshooting refers to identifying the errors like #N/A,” “#VALUE!”, “#NAME?” etc., It is common to get errors when we apply a formula that does not calculate the results throughout the sheet cells. To overcome that condition, we troubleshoot those Excel formulas.
The following image shows values and performs the calculation. Using the IFERROR function, we will try to Troubleshoot the Error Value of the “#DIV/0!” error in the following example.
Enter the formula in cell C2.
The formula entered is: =A2/B2. The error “#DIV/0!” is found because the number is divided by 0 in cell C3.
Replace the “#DIV/0!” error with the “Error in Calculation” text string. The following formula is entered in cell C3.The complete formula is: =IFERROR(A2/B2, “Error in Calculation”). The IFERROR function returns the “Error in Calculation” text string.
Table of contents
Key Takeaways
- Excel troubleshooting helps users identify the errors obtained while using Excel formulas. To resolve any issue, it is imperative to find its root cause.
- If the problem is in the formula or file format, the appropriate manual fix to resolve the issue.
- Resolving Excel troubles in the shortest possible time is best.
- The program in Excel grows and faces many issues we can’t solve. Big data management tasks signify many challenges.
- The structure of a database can go wrong if two people work on the same sheet.
How To Troubleshoot Excel Formula?
#1 – Troubleshooting The Error Value #N/A
The following image shows the salary details of every employee of the company. Using the IFERROR function with the VLOOKUP function, we will try to Troubleshoot the Error Value of the “#N/A” error in the following example.
In the table,
- Column A: Contains the ID of employees
- Column B: Contains the name of employees
- Column C: Contains the salary of employees
The steps to evaluate the Troubleshooting in Excel are as follows:
- Step 1: Select an empty cell for the output. We have selected cell F3 in this case.
- Step 2: Performing the VLOOKUP function in cell F3. Searching the salary of the employee whose ID is 20.
VLOOKUP function in Excel lookup the value in the first column.
- Step 3: The complete formula is: =VLOOKUP(F2,A2:C6,3,FALSE)
- Step 4: Press Enter key. The error #N/A is found because no employee has ID 20 in the table array.
- Step 5: Replace the #N/A error with the Value Not Found text string.
- Step 6: The complete formula =IFERROR(VLOOKUP(G2,A2:C6,3,FALSE),”Value Not Found”) is entered in cell G3.
- Step 7: The VLOOKUP function does not evaluate errors in the formula. Therefore, the IFERROR function returns the Value Not Found text string.
Hence, the text string replaces the #N/A error with the Value Not Found.
#2 – Troubleshooting Logical Formulas
The following image shows the item, and we will try to Troubleshoot the Logical Formula using IF Function in Excel in the following example.
In the table,
- Column A: Contains the Items
- Column B: Contains the Sales
The steps to evaluate the Troubleshooting in Excel are as follows:
- Step 1: Select an empty cell for the output. We have selected cell B4 in this case.
- Step 2: Next, start by entering the formula in cell B4.
- Step 3: Enter the Logical test value, i.e., B2>B3.
- Step 4: Enter the value if true, i.e., Vegetables Sales is More.
- Step 5: Enter the value if false, i.e., Fruits Sales is More.
- Step 6: The complete formula is: =IF(B2>B3,”Vegetables Sales is More”, “Fruits Sales is More”)
- Step 7: Press Enter key. The image below shows the results in cell B4 as Fruits Sales is More.
Note: As we can see, the result shown in the above image is wrong. The logical test is if the sales in cell B2>B3, the output should be that whose sales are more. Due to an error in entering the true and false values, the result comes as wrong. For Troubleshooting this formula, we should check the calculation process step by step. Therefore, in the below calculation, we met the correct result.
- Step 8: Start by entering the correct formula in cell B4.
- Step 9: Enter the Logical test value, i.e., B2>B3.
- Step 10: Enter the value if true, i.e., Fruits Sales is More.
- Step 11: Enter the value if false, i.e., Vegetables Sales is More.
- Step 12: The complete formula is: =IF(B2>B3, “Fruits Sales is More”, “Vegetables Sales is More”)
- Step 13: Press Enter. The image below shows the results in cell B4 as Vegetables Sales is More.
#3 – Is It A Number Or Date Or Not Both?
The following image shows the dates, and we will try to troubleshoot whether the entered value is a Number or Date or both in the example below.
In the table,
- Column A: Contains the S. No.
- Column B: Contains the Date
The steps to evaluate the Troubleshooting in Excel are as follows:
- Step 1: The values are entered in the following example. As we enter in the form of date format, it reflects as the date.
- Step 2: This happens because of the cell format set by Excel.
- Step 3: If we want to change the format of the cell. We need to go to the Home tab, the Number group, and then change the format from the drop-down list. As we have selected the General format from the drop-down list.
- Step 4: The value is now shown in General format.
- Step 5: If we want to change the format of the cell again. We need to go to the Home tab, the Number group, and then change the format from the drop-down list. As we have now selected the Number format from the drop-down list.
- Step 6: The value is now shown in Number format.
#4 – What If The Formula Doesn’t Recalculate
The following image shows two projects of the students, and we are calculating the percentage of the values. We will try to Troubleshoot the problem if the Formula used in the calculation doesn’t Recalculate in the following example.
In the table,
- Column A: Contains the Student
- Column B: Contains the Value 1
- Column C: Contains the Value 2
- Column D: Contains the Percentage
The steps to evaluate the Troubleshooting in Excel are as follows:
- Step 1: Select an empty cell for the output. We have selected cell D2 in this case.
- Step 2: Next, start by entering the formula in cell D2.
- Step 3: The complete formula is: =B2/C2.
- Step 4: Next, press Enter key. The result is returned in cell D2 as 117%.
- Step 5: Press Enter key. Drag the formula downwards till cell D4.
For checking formula used in the calculation that it matches the same result or doesn’t recalculate.
- Step 6: Select the Manual option from the drop-down list under the Calculation Options in the Calculations group of the Formulas tab.
- Step 7: After selecting the Manual option, select the first output cell, i.e., D2 and drag the formula downwards till cell D4.
- Step 8: The output in the following cells changes, as shown in the below image. That is the way we can troubleshoot the Excel formulas and some of their predefined functions.
Important Things To Note
- The #N/A! error is not a nuisance error because it gives us information about the formula that is gone wrong.
- Solving the Troubleshooting in Excel is a tiring and time-consuming process. The issues might take a full day or more to resolve.
- There is no predefined process for troubleshooting the issues in Excel.
- Errors like “#DIV/0!”, “#NAME?”, “#NUM!”, “#NULL!”, “#REF!”, “#VALUE!”, etc. can be troubleshoot in Excel.
Frequently Asked Questions (FAQs)
To Troubleshoot to Fix the Excel Files, follow the below steps;
Open the file in another format or with another application.
Move or Copy the Excel data into the other Worksheet.
Set the calculation format to Manual.
MS Excel Open and Repair utility.
Resolve Excel File errors with Excel repair software.
To Troubleshoot the error showing the message Excel not Responding, follow the below steps;
Open the Excel file in Safe mode.
Install and update the latest version of Excel.
Disable all the Add-ins in Excel.
Fix the MS Office.
Reinstall Microsoft Excel.
Some basic problems resolve in Excel Troubleshooting are as follows;
1) Excel Multi User Editing
2) Excel Shares Workbooks
3) Excel Linked Workbooks
4) Excel Data Validation
5) Excel Navigation
6) Excel Security
7) Excel Speed Problems
Download Template
This article must help understand the Excel Troubleshooting formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Excel Troubleshooting. Here we learn how to troubleshoot excel formulas & errors, with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply