Quick, clean, and to the point

This video comes from our online video training for Excel.

Don't hard code assumptions into your formulas

One of the biggest mistakes you can make is to hard code assumptions into your formulas. Instead, if a value might change, put it in a cell and refer to it with a reference, as explained in this video.
Video Transcript 

In this video, we'll take a look at a few reasons why you shouldn't hard-code values that may change into your formulas.

Here we have a simple table for a driving trip across the United States. The route begins in San Francisco and ends in New York City. The route is divided into segments, with each segment starting and ending in a larger city.

Let's add formulas in columns E and F to calculate the estimated fuel usage and fuel cost. 

First, in column E, to estimate fuel consumption we need a formula that divides the distance by the estimated miles per gallon.

Let's say we think our car will get 25 miles per gallon, on average. We can write the formula as D7 divided by 25, and then copy it down.

In column F, we need to calculate cost. Let's assume that gas will cost about $2.50 a gallon. In that case, the formula becomes E7 * 2.50.

So, if we total up these colunmns, we can see that the entire trip will take about 130 gallons of fuel which will cost about $300.

So, these formulas work fine. But what if you want to change assumptions? For example, what if you want to change the miles per gallon to 24, and the estimated cost to 2.30?

In that case you'll need to edit all the formulas to reflect these new assumptions.

If you're clever, you can save a few keystrokes if you first select all the formulas, then make the change, and then use control + enter to update all the formulas at once.

But still, you'll need to touch every formula in the worksheet. And if change the assumptions, you'll need to do it all over again.

In a more complicated worksheet, this can be a lot of tedious work. And, because you may need to edit a large number of formulas, it increases the chance of error significantly.

A better approach is to expose the assumptions on the worksheet where they can be easily seen and modified.

To do that, I'll add some input cells at the top, and add labels to make them clear.

Now I can modify the formulas to use these inputs, instead of the hard-coded values. I'll need to make the references to the input cells absolute, so that I can copy the formulas down without problems.

Now I can easily change the assumptions, and all formulas automatically recalculate. There's no need to edit any formulas.

In general, when you build a formula that requires an input that may change, consider exposing the input on the worksheet. This will make your worksheets easier to understand and more durable overall. 

Dave Bruns

Related shortcuts