5 Excel Tips and Tricks for Fixing Your Broken Formulas

By Robert Half May 16, 2014 at 7:00am

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.

If you're grappling with an Excel spreadsheet and need help taming your formulas, these Excel tips and tricks can help you whip them into shape. You’ll be strutting your tech-savvy skills in no time.

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.

You can also review Microsoft Office’s helpful Excel tips and tricks for formulas. And if you’re registered with OfficeTeam, don’t forget to take advantage of our free Excel training.

More Excel tips and tricks

More From the Blog...