What nine things should everyone know how to do with a spreadsheet?

Businessman holding a laptopMacworld recently ran an article, “Nine things everyone should know how to do with a spreadsheet,” by Rob Griffiths. Here’s the list:

  1. Format numbers
  2. Merge cells
  3. Use functions
  4. Distinguish between relative and absolute references
  5. Name cell references
  6. Extract data from ranges
  7. Perform logical tests
  8. Mix text and formula results
  9. 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.

 

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

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

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

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

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

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

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

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

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

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