Excel essentials for every decision maker

As a decision maker, you need much more than pretty graphs of static data. You need to think about the future. You need to compare alternatives. You need to customize spreadsheets to help you think about your specific questions. You don’t need WordArt or conical column charts. You need quick answers to ever-changing questions. You need to be able to dig deeper as the answers raise additional questions.

Yes, you need to be able to enter data, take sums and make charts. But to use Excel’s full power for decision-making, there are a few key skills you need. I’ll tell you more about each of these in future posts.

1. Formulas

Formulas give Excel its power. This is how you can ask complex questions. Formulas can use or check anything in the spreadsheet to give you the answers you need.

2. Functions

Excel has pre-packaged many common and useful formulas for you to use. These range from simple arithmetic, like sum or maximum, to complex financial, statistical and engineering calculations.

3. AutoFill

Grab a corner of a cell and drag down to fill in the rest of a sequence. You may have used this as a quick way to enter a sequence of numbers. Its real power lies in its use with formulas. This is, in my opinion, the most powerful of all the techniques. (In many fat Excel books it’s given maybe a paragraph.) It lets you create an entire predictive spreadsheet from a single row of formulas.

4. “Bucket brigade” formulas

You won’t find this amazing technique anywhere else. Write a simple formula that builds on the result from the previous row, then AutoFill. The result is a cascade of formulas that each do a little of the work and pass it on, so that at the bottom of the column is the answer to a complex question. Simple examples are compound interest and running totals.

5. Descriptive formulas

Excel, unfortunately, does not recognize descriptive formulas, but they are a great tool for thinking through, entering and documenting formulas.

6. Naming cells

I recommend naming all your inputs. That makes your formulas easier to read and keeps references straight when you copy or AutoFill formulas. Not a power technique, but it sure makes things cleaner.

7. Goal Seek

Goal Seek lets you work the problem backwards. When you finish a spreadsheet to look out into the future and the result isn’t what you want, find out how much you need to change to get there.

8. Data tables

Get a table that shows how the answer changes as one of the variables changes. This is great for what-if explorations or sensitivity analysis. A data table can even do Monte Carlo analysis for risk assessment.

Anything to add? What are your favorite Excel techniques for decision-making?

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