Have you ever spent hours tweaking a spreadsheet only to forget later how it works? Use this pro tip to add comments inside a formula as a reminder of what it does and how it works.
TLDR? Skip to the 3 min video on YouTube: http://technoju.do/0gDiW
It is not uncommon, especially for entrepreneurs, organizers or people working together in a company to have spreadsheets with super-elaborate formulas and nested arguments that pull data from workbooks in separate documents. But, what good does your spreadsheet wizardry do if you can’t remember how to use it? In this tech tip we use a common feature available in both Google Sheets and every version of Microsoft Excel since Excel 2007 to insert notes within a formula, similar to how software developers comment their code so others know how it works.
The feature we will be using to add comments to our formulas is the N( ) function. The N( ) function evaluates a value or cell reference and returns it as a number. It takes a logical TRUE or FALSE value and converts it to 1 or 0 respectively. It converts a date such as 1/1/2019 to a serial number such as 0.0004952947003 which is useful for calculating things like duration. Any value that is not a date, number or Boolean value (TRUE / FALSE) is returned as 0. And, it is this behavior of the N( ) function that allows us to add comments to our formulas because adding a value of 0 does not change the result of any math operation we might perform in an Excel or Google Sheets formula. For example, the formula =SUM(1+1)+N(“This formula adds 1+1”) would return 2 since =SUM(1+1) returns 2, N(“This formula adds 1+1”) returns 0 and when added together the sum is 2 (2+0=2). We just have to be careful to place the N() function outside of a math operation.
How To Add Comments To Spreadsheet Formulas
To use this nifty trick we simply perform the following steps:
Note: Step-by-step YouTube Video Tutorial also available here: http://technoju.do/0gDiW
1. Open a Google Sheets or Microsoft Excel spreadsheet and select a cell containing a formula.
2. Position the mouse cursor at the end of the formula in the formula bar, click and type +N(
3. Finish adding your comment to the formula by completing the statement in the N( ) function with an open quote “, your text and a close quote “. The finished statement should look like +N(“Your statement here.”)
Bonus Tip: You can include multiple lines for easier reading by pressing and holding the Ctrl key and pressing the Enter key after each line.
To watch the step by step video tutorial on how to add comments to Google Sheets or Microsoft Excel Formulas go to YouTube Video here: http://technoju.do/0gDiW
