7 Tips for Working with Excel Formulas

Several elements can help you be as productive as possible when writing and correcting Excel formulas. You can view all your formulas at once and correct errors one by one. You can use add-in wizards to help write functions.

Master operator precedence

One of the most important factors in writing formulas is getting the operators correct, and I do not mean telephone-company operators. This has to do with mathematical operators — you know, little details such as plus signs, and multiplication signs, and where the parentheses go. Operator precedence — the order in which operations are performed — can make a big difference in the result. You have an easy way to keep your operator precedence in order. All you have to remember is “Please excuse my dear Aunt Sally.”

This phrase is a mnemonic for the following:

• Parentheses

• Exponents

• Multiplication

• Division

• Addition

• Subtraction

Thus, parentheses have the first (highest) precedence, and subtraction has the last precedence. Well, to be honest, multiplication has the same precedence as division and addition has the same precedence as subtraction, but you get the idea!

For example, the formula =1 + 2 × 15 equals 31. If you think it should equal 45, you’d better go visit your aunt! The answer equals 45 if you include parentheses, such as this: =(1 + 2) × 15.

Getting the order of the operators correct is critical to the well-being of your worksheet. Excel generates an error when the numbers of open and closed parentheses do not match, but if you mean to add two numbers before the multiplication, Excel does not know that you simply left the parentheses out!

A few minutes of refreshing your memory on operator order can save you a lot of headaches down the road.

Display formulas

In case you haven’t noticed, it’s kind of hard to view your formulas without accidentally editing them. That’s because any time you are in “edit” mode and the active cell has a formula, the formula may incorporate the address of any other cell you click. This totally messes things up.

Wouldn’t it be easy if you could just look at all your formulas? There is a way! It’s simple. Click File at the top left of the Excel workspace, click Options, click the Advanced tab, and scroll down to the Display options for this worksheet section.

Setting options.

Notice the Show formulas in cells instead of their calculated results check box. This box tells Excel that for any cells that have formulas to display the formula itself instead of the calculated result. The following figure shows a worksheet that displays the formulas. To return to normal view, repeat these steps and deselect the option. This option makes it easy to see what all the formulas are!

Viewing formulas the easy way.

You can accidentally edit functions even when you have selected the View Formulas option. Be careful clicking around the worksheet.

Fix Formulas

Suppose that your worksheet has some errors. Don’t panic! It happens to even the savviest users, and Excel can help you figure out what’s going wrong. On the Formulas tab in the Formula Auditing section is the Error Checking button. Clicking the button displays the Error Checking dialog box, shown here. That is, the dialog box appears if your worksheet has any errors. Otherwise, it just pops up a message that the error check is complete. It’s that smart!

Checking for errors.

When there are errors, the dialog box appears and sticks around while you work on each error. The Next and Previous buttons let you cycle through all the errors before the dialog box closes. For each error it finds, you choose what action to take:

• Help on This Error: This leads to the Help system and displays the topic for the particular type of error.

• Show Calculation Steps: The Evaluate Formula dialog box opens, and you can watch step by step how the formula is calculated. This lets you identify the particular step that caused the error.

• Ignore Error: Maybe Excel is wrong. Ignore the error.

• Edit in Formula Bar: This is a quick way to fix the formula yourself if you don’t need any other help.

The Error Checking dialog box also has an Options button. Clicking the button opens the Formulas tab of the Excel Options dialog box. On the Formulas tab, you can select settings and rules for how errors are recognized and triggered.

Use absolute references

If you are going to use the same formula for a bunch of cells, such as those going down a column, the best method is to write the formula once and then drag it down to the other cells by using the fill handle. The problem is that when you drag the formula to new locations, any relative references change.

Often, this is the intention. When there is one column of data and an adjacent column of formulas, typically, each cell in the formula column refers to its neighbor in the data column. But if the formulas all reference a cell that is not adjacent, the intention usually is for all the formula cells to reference an unchanging cell reference. Get this to work correctly by using an absolute reference to the cell.

To use an absolute reference to a cell, use the dollar sign ($) before the row number, before the column letter, or before both. Do this when you write the first formula, before dragging it to other cells, or you will have to update all the formulas.

For example, don’t write this:

=A4 x (B4 + A2)

Write it this way instead:

=A4 x (B4 + $A$2)

This way, all the formulas reference A2 no matter where you copy them, instead of that reference’s turning into A3, and A4, and so on.

Use formula auditing

There are precedents and dependents. There are external references. There is interaction everywhere. How can you track where the formula references are coming from and going to?

Use the formula auditing tools, that’s how! On the Formulas tab is the Formula Auditing section. In the section are various buttons that control the visibility of auditing trace arrows.

Auditing formulas.

The formula auditing toolbar has several features that let you wade through your formulas. Besides showing tracing arrows, the toolbar also lets you check errors, evaluate formulas, check for invalid data, and add comments to worksheets.

Use conditional formatting

Just as the IF function returns a certain value when the first argument condition is true and another value when it’s false, conditional formatting lets you apply a certain format to a cell when a condition is true. On the Home tab in the Styles section is a drop-down menu with many conditional formatting options.

This figure shows some values that have been treated with conditional formatting. Conditional formatting lets you set the condition and select the format that is applied when the condition is met. For example, you could specify that the cell be displayed in bold italic when the value it contains is greater than 100.

Applying a format when a condition is met.

Conditions are set as rules. The Rule Types are

• Format all cells based on their values.

• Format only cells that contain. . . .

• Format only top or bottom ranked values.

• Format only values that are above or below average.

• Format only unique or duplicate values.

• Use a formula to determine which cells to format.

When the condition is true, formatting can control the following:

• Borders

• Font settings (style, color, bold, italic, and so on)

• Fill (a cell’s background color or pattern)

Cells can also be formatted with color schemes or icon images placed in the cell.

Use data validation

On the Data tab, in the Data Tools section, is Data Validation. Data Validation lets you apply a rule to a cell (or cells) such that entry must adhere to the rule. For example, a cell can be set to accept only an integer entry between 50 and 100.

Setting data validation.

When entry does not pass the rule, a message is shown.

Caught making a bad entry.

The error message can be customized. For example, if someone enters the wrong number, the displayed error message can say Noodlehead — learn how to count! Just don’t let the boss see that.