Excel formulas. No, you don’t need to be a math geek.

Writing formulas is the number one Microsoft Excel skill that decision makers need to know. Formulas give Excel its power and flexibility. They let you answer the questions you want to ask, which usually go beyond adding columns of numbers or making charts of a bunch of data.

Don’t freak out with memories of high school algebra. Excel formulas are even more intuitive than using a calculator. Just start with “=” to let Excel know you’re entering a formula. Then click on numbers in the spreadsheet and put arithmetical operations between them. For example, say you’ve typed in your current salary and the raise percentage you expect in cells B1 and B2. (Type “5%” so Excel knows this is a percentage.) You want to know how many more dollars you will make and your new salary.

Type “=” in call B3 and then do the calculations. The raise amount is the current salary (click on “$104,000”) times (type “*”) the raise percentage (click on “5%”). Then hit Enter. There’s your answer in the cell and the formula in the formula bar above the spreadsheet. Now to get your new salary, you add the current salary and the raise amount. =$104,000 (click) + $5200 (click).

As you go along, you immediately see the results and can do sanity checks. You can click on any calculated cell and see where the answer came from. Double-click on the cell to see the formula highlighting the cells used. Hit Esc to go back.

The real power of formulas is in playing “what-if.” What if you got a raise of 7%? Enter 7% in B2 to get the answers. Now imagine a big future-oriented spreadsheet, Change the inputs–things you control and things you can’t–and visualize the outcome. This is a powerful tool for planning for the future.

How do you use formulas? There’s an art to writing compelling formulas that give you real insights. Most Excel books skim over this topic. I’ll be sharing some of my techniques here. What are yours?

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?

Excel: It’s not just for bean counters anymore

As a decision-maker, you probably see a lot of Excel charts. They may be produced by people who work for you . This often takes the form of a dashboard that summarizes the current status. Because of this, you probably think of Excel as a tool for “number crunchers” or “bean counters.” These are the people who keep track of all those tedious columns of numbers and then summarize them in pretty graphs. Why would you be bothered with that sort of thing?

This common use of Excel fails to tap Excel’s greatest feature—interactivity. The very first spreadsheet program was called VisiCalc, short for visible calculator. It was touting its ability, remarkable in an age when spreadsheets were made out of paper kept in big books, to let you change anything and see how it changes the answer. Now we’ve lost sight of that legacy. We’re back to thinking of a spreadsheet as rows and columns of numbers, but now we can slice and dice them and show them in pretty graphs.

Business is not static. If you’re looking only at this month versus last month, this quarter versus last quarter, you are not looking ahead. Build a spreadsheet to look into the future, then change inputs and see what happens. Play what-if with things you can control to maximize your chance for success. Play what-if with things you can’t control so that you’re ready for whatever comes your way.

Here are some examples of future-focused spreadsheets you can easily build yourself.

  • How much start-up capital do I need?
  • How soon will my business break even?
  • Do I have enough money saved to retire?

Here are some of the what-if questions on things you can control that you might want to explore for that third question.

  • What if I move into a less expensive house?
  • What if I work another 5 years?
  • What if I take a lump sum rather than an annuity?

Here are some of the what-if questions on things you cannot control:

  • What if I live to 90? 95? 100?
  • What if inflation is high?
  • What if the stock market is erratic?
  • What if the capital gains tax rate changes?

Why would you want to build these spreadsheets yourself if there are people working for you who do this sort of thing? Because you’re the only one who knows exactly the questions you want to answer. And answering those questions will raise more questions, questions that you can answer by expanding the spreadsheet. Keep Excel open on your computer to jot down ideas or do quick calculations. Many of these doodles develop into spreadsheets that give real insights.

With just a few simple techniques you can build your own spreadsheets to answer questions like these. Excel is full of features, many of them powerful and complex for sophisticated analysis, many of them to help your bean counters bring you beautiful spreadsheets and charts. In future posts I’ll talk about these key techniques for Excel for decision makers. It turns out that 99% of the stuff you find in those big Excel books you don’t need to know.

How do you use Excel for future-focused decision making?

 

Do Something Even if It’s Wrong

The title is a quote from my late father, who would use it when I was a kid to get us moving. It was only as an adult, recalling this command, that I got to wondering about it. It made no sense. Why would anybody want to do something wrong?

Well, my father was in good company. Theodore Roosevelt said, “In any moment of decision the best thing you can do is the right thing, the next best thing is the wrong thing, and the worst thing you can do is nothing.”

So I ask again, why would anybody want to do something wrong?

If you think about some of the decisions you’ve struggled with, the ones with multiple alternatives, you’ll realize that which is “the right thing” and which is “the wrong thing” is not obvious. Otherwise you wouldn’t be struggling with the decision. If you do nothing, you miss out on the chance of grabbing “the right thing.” Usually, any one of your alternatives is better than what you’re doing now, or you wouldn’t be considering it. If you throw “do nothing” in as an alternative, all of your choices will look better. If you then choose “do nothing,” at least it’s an active and thoughtful choice and not just cowardice.

This common belief that there is always a right choice and a wrong choice has a stifling effect on human and organizational innovation, as I talked about in a previous post. It’s the reason people often do nothing. We’re all afraid of making the “wrong choice.”

Even Yogi Berra, in one of his famously nonsensical quotes, chimed in on this question. “When you come to a fork in the road, take it.” This is actually the answer to the quandary. The important thing is to move forward. Action feeds on action. Maintain your momentum for action and for taking charge of this and all decisions you’ll make in the future.

I’d love to hear about your experience. Have you ever moved ahead and done something even knowing it might be the wrong thing? Have you ever done nothing because you were afraid of doing something wrong?