Do you have math anxiety? Do thoughts of formulas and spreadsheets keep you up at night? Don’t despair. Here are five Excel tips for fixing faulty formulas.
1. Copy formulas
First, if your formula is working, don’t mess with it. Copying existing formulas saves you time.
At the bottom right corner of the active cell is a box called the “fill handle.” Hover over it and it turns into a black cross. Simply drag the black cross from the cell containing the formula to adjacent cells where you want it copied, then release the fill handle.
2. Turn on error checking
It’s generally a good idea to enable error checking because Excel will alert you when a cell has a formula error. To turn this on, go to File > Options. In the left panel of the dialog box, choose Formulas. Make sure the “Enable Background Error Checking” box is selected. Excel will show you where any errors exist in your worksheet by displaying a small colored triangle in the upper left corner of the problem cell. Click the information icon to see a context menu of options you can use to correct the error. (Mac users: Go to Excel > Preferences > Error Checking.)
3. Rule out common errors
If your formula isn’t working, first make sure you haven’t made a simple error. For example, did you forget to start the equation with an equal sign? Did you forget to close your parentheses? You can view and edit the formula in the Formula Bar by clicking the cell that contains it.
4. Check spreadsheet formulas at a glance
The Show Formulas function makes it possible to view all the formulas and functions in your spreadsheet. You'll be able to see which cells contain formulas, read through all formulas to check errors and print the spreadsheet while showing all formulas. To access Show Formulas, press Ctrl + `. Repeat the key combination to hide formulas.
5. Let Excel help
If you've entered a formula with an obvious error, Excel will let you know and suggest a correction. You can click “Yes” to accept Excel’s suggestion or “cancel” to return to the formula and correct it yourself. Use the options in the context menu that appears when you click the information icon to get help on the issue, ignore the error, step through the calculations in the formula or change the formula options.