I’m going to start a series called Function Friday where I will share common Excel functions and formulas marketers should learn. I’ll kick this series off with a tip that every marketer needs to know before entering the icy waters of function madness. Think of it as an appetizer to tide you over until your meal is served next Friday. 🙂
Have you ever been in the middle of a really complicated Excel formula, just to lose everything because you can’t finish it? It’s happened to me more times than I care to admit. I’ll be knee deep in colored parentheses just to find out I’m missing a key element or I’m stuck and have to look something up. If you just press Enter (Mac: Return), Excel will throw a tantrum and kill off your hard work with a dull blade. If you press the Esc key, you’ll lose everything. And Excel has no built-in functionality that allows you to save your work for later.
To avoid becoming a statistic, this hack will allow you to freeze your formula wherever you are so that you can return to it later when you’re ready: While the cell is still active, just put a single apostrophe in front of the formula. This stores your formula as text. When you’re ready to proceed, simply remove the apostrophe, and you’re good to go. If you don’t remove the apostrophe, although you can still add to the formula, you won’t get the helpful tool tips.
As you can see in the screenshot above, the apostrophe isn’t apparent in the cell after you press Enter, but you can see it in the formula bar.
In related news, if you have numbers that are left-aligned (which is an indicator that Excel thinks your numbers are text), look for an apostrophe in the formula bar!
Pro Tip: If Google Spreadsheets is more your cuppa, no worries. This hack works there too.
I usually use the point “.” to do this. I didn’t know you can also use an apostrophe.
Yep. There are many paths to the same goal. 🙂