Exceljet

Quick, clean, and to the point

Don't hard code values that may change

In this video, we'll take a look at a few reasons why you shouldn't hardcode 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, 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 that up, 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.

You can save a few keystrokes if you first select all the formulas, then make the change, and 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 these 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 the add labels to make them clear.

Then I can modify the formulas to use these inputs instead of the hard-coded values. I 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 without any need to edit 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.

Course 
Core Formula

Related shortcuts

Author 
Dave Bruns