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?