Getting It Right in Excel.

Photo by J.M. Cameron.¹

Excel is great for crunching numbers and analyzing data. It does exactly what you say and won’t complain if there is a lot of work. All is good so long as you correctly explain what you need. If you don’t, you get what you asked for not what you wanted. Here are some tools and techniques to help avoid and fix mistakes.

Start Small. Problems often arise when people begin by working with large quantities of real data. That makes it difficult to verify that the answers are correct and to troubleshoot if they are not.

Instead test your formulas with easy numbers. Thus for a checkbook, create the formulas and try them with ten “test” checks of $100 each. The total should obviously be $1000. If it is, you know the formulas are correct and you can replace the “test” checks with real data. If it is not, you know you need to troubleshoot.

Click on Cell to Enter It in Formula.

Make Sure Formulas Refer to the Proper Cells. Typos in formulas are fatal — if you type M10 Excel won’t know you meant N10.

A simple solution is to avoid typing. Once you enter “=” to begin a formula, clicking on a cell puts its address into the equation. For example, in the animated example, clicking on $12,000 enters “B2.”

Boxes Show Cells Used by Formula in E2.

And as you build an equation Excel highlights the cells it references. That makes it easy to see that all the proper data are included, but nothing else. To check an existing formula double click the cell it’s in. The colored boxes will be displayed again.

fx Buttons Launch Function Wizard.

Use the Function Wizard. Functions like SUM, IF and VLOOKUP greatly expand Excel’s power, but they only work correctly if everything is in the proper place. The Function Wizard can help by indicating what is needed and where it goes and previewing the results while we work.

Launch the Wizard by clicking the fx button either on the left side of the Formula Ribbon (larger button in image) or the one to the left of the Formula Bar (smaller button in image).

Function Wizard Shows Where Needed Information Should Go.

Initially a dialogue box opens listing all the functions (you can view them by category). Choose the one you want and click OK. A second dialogue box then appears that tells you what information is needed and where it should go. The computed result is previewed as you provide the requested information. An explanation of what is required appears at the bottom alongside a link to the relevant page of Microsoft’s support web site.

Trace Precedents Shows Source Data for Calculation in Active Cell.

Auditing Formulas. Sometimes mistakes occur because a change is made in one cell without realizing how it affects calculations elsewhere. Excel’s formula auditing features help us find and avoid such problems.

To see what cells affect the formula in the active cell, click the Trace Precedents button on the Formula ribbon. The first click displays arrows pointing to all cells included in the current formula. Click again and the arrows extend to show cells that affect calculations in those cells. Each additional click extends the arrows to show the next set of links in the chain of formulas. A chime sounds when you have reached the end.

Trace Dependents Shows Cells Affected by Changes in Active Cell.

To see what cells will be affected by changes in the active cell click Trace Dependents (on the Formula Ribbon). It works like Trace Precedents in reverse — the first click points to cells directly affected by the change and so on. Again a chime sounds when you can go no further. To remove the formula tracing arrows, click the Remove Arrows button.

Sometimes it’s helpful to be able to see and print your equations. Click the Show Formulas button on the Formula Ribbon to do this. And if you have a large worksheet, or formulas that reference several worksheets or linked files, you can add cells to the Watched Window, which remains visible even when the chosen cells are not on screen.

* * * * *

As Ben Franklin famously said: An ounce of prevention is worth a pound of cure.²

-----

1. Photograph taken in 1868 from Wikipedia.
2. The Philadelphia Gazette, February 4, 1736. The quotation appears in a letter Franklin submitted anonymously as an “old citizen” concerning fire prevention. Franklin owned the newspaper and the next year helped found the first volunteer fire department in the country. In 1752 Franklin and his fellow firefighters founded a fire insurance company, which still exists today.

Visit our Excel Productivity Guides page for more helpful articles.

This article originally appeared in our free twice-monthly e-mail newsletter. To receive future issues, please add your name to the subscription list.

Want to learn more? We offer seven different Excel classes: Mastering Excel, Advanced Excel, Excel Charts, Excel Dashboards, Macros and Pivot Tables and Power Pivot. Click any class name for a detailed description.

We can also create a private Personalized Excel Class for your group, or provide individual training or project consulting services.