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 columns 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 the 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 you 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, more professional, and more durable overall.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
