Macworld recently ran an article, “Nine things everyone should know how to do with a spreadsheet,” by Rob Griffiths. Here’s the list:
- Format numbers
- Merge cells
- Use functions
- Distinguish between relative and absolute references
- Name cell references
- Extract data from ranges
- Perform logical tests
- Mix text and formula results
- Use conditional formatting
I thought about my own top nine before I read the article, or even looked at the list. My focus is on visualizing your financial future and exploring your options. Even so, I was surprised how different my list was. I’ve used everything on Griffith’s list and certainly found it all valuable, but here’s my list for those wishing to explore their financial future.
- Play what-if
This is barely a skill. Griffiths is assuming that you know how to do this. It’s just entering a number in a cell. The key is to see what that does to the answer. This hardly seems worth mentioning, except that so many people seem to think that all you do with Excel is to dump a bunch of numbers into it and produce a static chart or slide. Explore! See what drives the outcome. See how to get where you want to be.
- Write formulas
This is also implicit in Griffith’s list. Even if the word “formula” brings up scary images of high school algebra, everyone should be able to get Excel to write a formula for them by typing “=” and clicking on the cells you want to use in the calculation. It’s amazing what you can do without ever entering a cell reference (if you name your inputs). Learn how to build more sophisticated formulas and you’ll be unstoppable.
- Name cells
Griffiths has this one on his list as a means to keep formulas easier to read. That’s nice, but it’s on my list for a very different reason. If you name your inputs they automatically become absolute references and you don’t have to bother with all the dollar signs. This makes writing formulas to AutoFill a cinch. Also, I’m lazy, so I prefer to use Create Names, which uses the input labels. By the way, Griffiths looked at how to do everything on his list in Apple’s Numbers as well as Google’s Sheets. Numbers doesn’t support naming cells. Too bad. I’m a Mac user, but that’s a show-stopper for me.
- AutoFill formulas
The best part of writing formulas is being able to replicate them to fill an entire spreadsheet. Construct simple formulas that build on each other, AutoFill them down the page and get complex results. The secret to doing this is to name your inputs to make them play nice and then click on cells in the same of previous row to create a formula. Grab the corner and pull it down to fill in everything else. This is the technique that makes students’ jaws drop in my classes. Some big Excel reference books don’t even mention it.
- Use Goal Seek
Work the problem backwards. Your projected savings won’t get you to your goal? Tell Goal Seek to find the amount you need to save to get there.
- Use functions
This definitely belongs on any list, and it’s in both of ours. Whatever you want to do—statistics, finance, text handling, and more—Excel has a function for you. Griffiths mentions IF, HLOOKUP, and VLOOKUP. The IF function is especially powerful. It lets you make what happens on the spreadsheet respond to whatever is happening anywhere else on the spreadsheet. If you’re doing loan payments, check out PMT, IPMT, and PPMT.
- Create charts
You don’t need to know all the kooky chart options, but sometimes a simple chart helps you see what’s going on, year-by-year.
- Sort and filter
In most of the problems I deal with, you don’t have a lot of data (eg. How can I save enough for an $80,000 college education in 18 years?). But sometimes you’re keeping track of a lot of information and you need to make sense of it. Make a table of it, and then use the tabs at the top of each column to reorder or focus it all, to figure out what you have and what matters.
- Build data tables
This is a somewhat advanced, somewhat confusing technique, but it’s great for sensitivity analysis. What really matters to the answer? You can also use it to turn any spreadsheet into a Monte Carlo model for risk assessment.
This is clearly a very subjective list. What are your nine favorite Excel techniques?