Tips For Troubleshooting Formulas In Excel
If you are someone who is genuinely interested in numbers, then MS Excel is the perfect place for you to solving out problems, equations, puzzles and do many other mathematical things. But if you are doing it just for the sake of passing in a subject, then the formulas used in Excel can seem extremely complicated to you. You will feel a big gap in your knowledge base when you cannot seem to solve a certain equation in Excel. If you belong to the second group, then you need to follow the tips given below to troubleshoot any formulas you might use in Excel at any point of time. Read on to find out more.
Turning on error checking to know the signs
If you turn on the ERROR CHECKING feature on MS Excel, then the program will tell you if there is any error in the formula you have entered in any cell of the worksheet you are working on. But you cannot leave this feature on at all times, because in case of large worksheets, there will be a lot of formulas, and until and unless you put in all the data, every formula you enter will be shown as an error. But for single page worksheets, error checking can be used regularly.
You can turn this feature on by clicking on FILE>OPTIONS>FORMULAS, which can be found on the left panel of the dialog box that opens when you click on Options. You need to check the ENABLE BACKGROUND ERROR CHECKING option box. You will also have the option to choose the color scheme that will be used when pointing out an error in any formula. Following these steps, click on OK and confirm the selection. Once this option is turned on, Excel will point out any error that occurs by placing a triangle of the chosen color on the cell that contains the error. You can select the cell marked with the triangle, and choose suitable options to correct the error in the formula.
Ruling out all the basic and common errors
Taking a close look at the formula you entered seems like a good idea to ensure that you have not made any silly mistakes while entering. Mistakes like forgetting to start the formula with an EQUAL sign, or the absence of a colon while setting the range of cells for a certain formula, non closure of the parentheses, and adding unnecessary formats in your formula can quite easily be avoided if you simply take a close look at what you entered. This can save you a lot of trouble, as well as time.
You can quite easily use the formula bar to view the formula and edit it if need by simply clicking on the cell that contains said formula. You can also go through all the formulas you have entered in your entire worksheet by simply clicking FORMULA>SHOW FORMULAS. This will allow you to check all the formulas, and you can make any and every correction needed in any cell of the work sheet.
Take help from the Excel program
When you input a certain formula that has an error in it, MS Excel will automatically notify you of the error and ask you to make a correction. You can either click on the YES tab to accept Excel’s correction suggestion, or you can click CANCEL if you want to make the corrections all by yourself. But since the code of excel is written in such a way that it detects and corrects any and every formula, you would be wise to click on YES and accept what Excel has to offer.