Excel is the go-to tool for business calculations. Simple or complex, weeks of work or merely minutes, you’ll use Excel eventually. As with any tool, knowing a few tricks can transform the output from a clunky mess to a useful, beautiful creation.
- Learn before you hack
- Make it pretty
- Document everything
These three hints apply to lots of life’s endeavors,
but let’s talk about how they apply to Excel.
Learn before you hack
Excel is built so anyone can use it. Work-arounds do exist, which makes it tempting to hack and kludge your way to inelegant solutions. Avoid this!
Learn keyboard shortcuts. My Tao of shortcuts applies here, too. Invest in getting lightning-fast with keyboard shortcuts. Yes, you can point and click, but in the end all that clicking will take hours out of your life! (also on that note… go ahead and stop hiding from Excel 2007!)
Learn pivot tables. Pivot tables are the most valuable tool in Excel. If you can’t use them, you’re missing out. I won’t explain them in this post because many others already have, but learn them one way or another. Pivot tables let you perform cuts, filters, and sums on huge amounts of data with speed and grace. It’s the best way to get familiar with a dataset and do many analyses.
Learn functions. You know it’s time to learn a new function when you find yourself struggling to link lots of “if” statements together or manually re-typing data to get the format right. A function probably exits to solve your exact problem. Use Google, built-in help, or your friends to find the answer, but DO NOT do things the slow, hacky way. The elegant solution will solve the problem better and save you hours down the road when you have to do the same thing again!
Make it pretty
Spending time on the aesthetics has several benefits. Pretty Excel models inspire confidence in clients and coworkers and are easier to use and build.
Understand the ins and outs. Take time to identify key inputs and outputs. Ensure these are easy to find when you lay out the model. Next, list other inputs and outputs and sketch a plan for linking the two before starting. A planned model looks cleaner than an haphazard one and is easier to build!
Organize your tabs. Keep inputs, outputs, and data on separate tabs to help people find what they’re looking for. Also try organizing tabs into groups and color-coding them to reinforce the structure.
Use consistent colors. Settle on a single, non-hideous color scheme. This makes models easy to read and nice to look at! Plan the colors you’ll use in advance and stick to your plan.
An elegant, beautiful model is no good if people can’t use it. Plus, you’ll never know your model as well as the day you build it. For those reasons, document! Your boss will think you’re a superhero, and you’ll significantly reduce the number of emails asking for explanations!
Summarize and give instruction. There should be two tabs at the beginning of any Excel model. One summarizes what it’s for, why it was built, when, and by whom. The other gives instructions on key inputs and outputs, what users can change, and what they should leave alone.
Record your assumptions. Let me say that again… Record your assumptions! It’s a shame this is last on the list because it’s the most important. If you make an assumption, DOCUMENT IT. Every sheet with calculations or inputs should have an “assumptions” column. Total stranger should be able to explain your model… This helps catch mistakes, gets your thinking crystal clear, and gives you bulletproof answers to the age-old question “what’s happening in this cell?”
If you commit to making elegant, beautiful, well-document models, you’ll be ahead of most… Of course, there are countless other best practices I didn’t mention. What are yours?