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.
|Clearly label and format your work||Ensure your work is easy to follow:
|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|
||Catch and correct mistakes as they happen. When creating a summary table, setup a
|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|
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