Naming names: Making spreadsheet inputs play nice

One big difference between the spreadsheets that decision makers use and those that bean counters use is the desire and ability to play “what-if.” That means you’ll have inputs that you’ll want to change to see how that affects things. These are unknowns either because they represent a choice you have to make or some factor outside your control.

Take an example. A couple of posts back, the one about bucket brigade formulas, I showed a spreadsheet to figure how much money you’d have each year if you started with $10,000 and it grew at 4% each year. You might ask, “What if I invested $20,000? What if it grew only 3% per year?” Make these kinds of numbers inputs so you can change them and see what happens. You can control the amount you invest, so try different amounts and see what you’ll get. You can’t control what your investments pay, so try different amounts and visualize possible future outcomes depending on luck.

Here you see a spreadsheet to do that. The inputs are on the top. Naming your inputs makes them play nice, as you’ll see. Here’s how to do it. Select the numbers and their descriptions (cells A1:B2 in this example). Then from the Insert menu, select Name > Create… You’ll get a dialog box asking if the names are on the left, which they are, so click OK. Do this before you write any formulas.

Now when you write your formulas by clicking, you’ll automatically get the names instead of the cell numbers. This makes your formula easier to read and interpret. Even better, when you AutoFill the formula down the column, your formulas won’t get confused. They’ll always use the right input. (If you want to get technical, Excel makes all the named inputs absolute references so you don’t have to. See http://theexcelphile.wordpress.com/2012/10/14/absolute-relative-referencing/) Notice that the AutoFilled formula in B7 does exactly what you want it to do.

Now you’re ready to play what-if. Change the input numbers in cells B2 or B3 and watch the results.

What kinds of inputs have you used for your what-if spreadsheets?

Leave a reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s