Next cohorts · Spreadsheet Fundamentals: Part 1 · Jan 11
Back to blog
GuidesKatie Fifer

The Recalc Formula Guide

A practical guide to eight core formulas and the habits that keep spreadsheet analysis simple and easy to follow.

A comprehensive guide to spreadsheet navigation and data manipulation -- for both Google Sheets and Microsoft Excel. For each formula, we summarize its purpose. We also provide an overview of what defines "good" spreadsheet analysis (hint: not long, over-complicated formulas) and our 8 go-to formulas necessary to run the majority of spreadsheet analysis.

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 principleDetails
Clearly label and format your workEnsure 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 formulasSeparate 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 pastingDon't have the same data arranged vertically in one place and horizontally in another
Add Checks to your workCatch 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 formulasAlways 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 columnsKeep all formulas fairly simple. Don't use crazy amounts of nested formulas that are hard to audit
Use F2 to visually check your workWhen 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. The list below summarizes the role of each formula.

  • 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

Browse more articles.

Explore more tips, guides, and insights to sharpen your skills.