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.
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.
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.
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?