Enter your email to get the Recalc Formula Guide

What does "good" spreadsheet analysis look like?

We believe in simple, easy-to-follow spreadsheet analysis. No crazy long formulas. No unformatted sheets. Someone else on your team should be able to quickly pick up your work and easily follow what you did and why.

General principle Details
Clearly label and format your work Ensure your work is easy to follow:

  • Put raw data on a backup tab
  • Color-code: Hardcodes=Blue, Links=Green, Black=All else
  • Consider shading input cells as yellow
  • Separate different sections with shaded text across the top
  • Calibri or similar font, all one size
  • Consider getting rid of gridlines
  • Label everything - don't leave free floating numbers/text
  • Don't allow hardcodes in your formulas Separate hardcodes out into "assumption" cells (also called "drivers" or "inputs"). Link to these cells. Then you can easily update.
    Set up your work in parallel enabling easy copying and pasting Don't have the same data arranged vertically in one place and horizontally in another
    Add Checks to your work Catch and correct mistakes as they happen. When creating a summary table, setup a Check line below the total. Compare the total to your back up data. We recommend setting up all checks so they equal zero so that if you see any number as a result, you know you have an issue
    Use preferred formulas Always use sumifs() instead of sumif() - you can easily update if you need to add another criteria. Xlookup is a better way to do a lookup than Vlookup()
    Break apart complex formulas into multiple columns Keep all formulas fairly simple. Don't use crazy amounts of nested formulas that are hard to audit
    Use F2 to visually check your work When copying and pasting, use F2 to check your formula and make sure cell references are locked correctly

    Go-to Formulas

    We believe the majority of spreadsheet analysis can be solved using 8 key formulas. See the guide for in-depth explanation of the functionality of each formula, the relevant inputs, and a screenshot of the implementation.

    • Unique( ): Remove duplicate values from a list
    • If( ): Test if something is true, then do X, if not do Y
    • Count / Counta( ): Count the number of items in a datasheet
    • Countifs( ): Count the number of times certain criteria exist
    • Sumifs( ): Add up values based on certain criteria
    • Xlookup( ): Look up one piece of information
    • Filter( ):Get to a subset of your data based on certain conditions; Look up multiple pieces of information
    • Sort / Sortby ( ): Reorder your data

    Reach out about training today!

    Scroll to Top