If you’re a financial planning and analysis professional who is not a skilled spreadsheet user, you can’t do your job effectively. In fact, a 2012 AFP survey found that spreadsheet knowledge was “extremely important” to financial planning and analysis.
Here are 19 tips that will make you a faster and better spreadsheet user. They’re broken down into the “Instant Payoff Tips” and “Long-Term Payoff Tips.”
Instant Payoff Tips:
Set up templates and styles. There’s no reason to reinvent your Page Setup or your preferred formats every time you launch Excel.
Customize your toolbar. Add your most-used functions to the Quick Access Toolbar, and locate it just above the formula bar, where it’s easier to get at (rather than at the top of the Excel window). Arrange the icons in an order that’s intuitive to you.
Learn some shortcut keys. Most of us know Ctrl-C, Ctrl-X, and Ctrl-V for Copy, Cut, and Paste, but many others are useful as well.
Use consistent formats. Doing the same thing the same way in every spreadsheet means you won’t have to agonize over small design and layout decisions. Templates and style sheets really help here.
Learn to use Excel as a database. Don’t depend on your IT and accounting colleagues to produce reports from the accounting and other ERP systems. Dump the data into a spreadsheet and generate your own reports and extracts. Pivot tables, SUMIF, database formulas, and array formulas are wonderful tools for getting the most out of your data.
Learn to use Lookup & Reference functions. Functions like VLOOKUP, MATCH, OFFSET, and INDEX are immensely powerful and useful.
Organize data and cells for easy computation. Lay out your spreadsheet to minimize the time you spend entering formulas. For example, avoid inserting blank rows and columns just for visual spacing.
Learn to use automated help. Don’t be proud or stubborn—the automated help capabilities of most of today’s software products are truly amazing.
Don’t learn too many ways to do the same thing. Excel offers lots of different ways to do the same thing, but you only need to know one of them. Don’t clutter your brain with the rest of them.
Long-Term Payoff Tips:
Use sensible, intelligent filenames and folder organization. Know where to find your documents, and get a sense of what’s in a document without having to open it.
Use consistent formats. I know, I know, it’s mentioned above, but it’s also helpful in the long run, because you’ll know where to find things and why they’re there.
Design intelligent, intelligible formulas. We all take a little pride in our most complex formulas, but it’s no fun modifying or debugging them six months later. For example, storing intermediate values in out-of-the-way cells isn’t always elegant, but it certainly makes it easier to figure out what you did.
Use named ranges. Your formulas will be much easier to read and understand. And they might be a lot shorter as well, especially if you’re referencing cells in other worksheets or workbooks.
Use named formulas or macros. Learning to code macros takes some effort, but spreadsheets driven by macros can be much easier, faster, and error-free to work with later. Named formulas are “baby macros” that make your formulas and your spreadsheet logic much easier to follow.
Links: A force that can be used for good or evil. It’s tempting to link spreadsheets and other files together so everything gets automatically updated at the same time, but make sure it’s worth the time and effort to do this, and be on the lookout for unintended consequences.
Make different types of cells visually distinguishable and physically separate. This is especially important for models and other complex spreadsheets, and for spreadsheets that others will have to use. All users should be able to distinguish cells that are raw data, underlying assumptions, user inputs, and calculated results.
Document your work! It sure beats trying to remember what you did. You have lots of choices, including Comments attached to cells, typing user instructions into specific cells, and even handwritten notes.
Check your work! Do it regularly, using checksums, inputting unusual values, tying to other reports and systems, looking for results that just don’t make intuitive sense, etc. Your credibility depends on this.
Avoid cool new features. At least, not until you know you’ll be using this feature frequently and consistently.
Reach Randall Bolten at RBolten@painting-with-numbers.com. Follow him on Twitter: @RandallBolten
For more FP&A advice, visit AFPonline.org/fpa.