6 Google Sheets Formula Tricks

Six of my favorite Sheets formula tricks. Hopefully these will save you some time… and some headaches

Let’s jump right into it, shall we? Each of the six is linked to a interactive example.

1: Avoid COUNTA False Positive with IFERROR

I use the COUNTA (“A” = “anything”) function in combination with FILTER in almost every model I build. But I found early on I was getting false positives. Why? Because for some ungodly reason when FILTER returns “N/A - No results found” the COUNTA function treats that error message as a value, and therefore returns “1”. Avoid that issue by wrapping your FILTER function with an IFERROR with nothing (not even “”) as the “[value_if_error]”.

2: Create a Dynamic Dropdown List

This little workaround is particularly helpful when you’re creating a tool to be used by others (other groups within your org or even clients). By creating a hidden, filtered list with options defined by a selection made prior, you avoid the possibility of an error, where the two selections can’t co-exist. Sound like gibberish? Felt like gibberish when I wrote it. Click the link above, it’ll make more sense in context (here, after selecting a “STATE” from dropdown #1, the “CITY” options in dropdown #2 are limited to those within the selected “STATE”)

3: FILTER with an “OR” Clause

Sometimes there are multiple criteria you want to allow for when FILTERING. In the linked example above, I filter a list to show all account “leads”, that is, accounts with status “mql” (marketing-qualified lead), “sql” (sales-qualified lead) or “website” (leads via the website funnel). Using an “OR” clause allows for a simple method to do this boolean search. The alternative approach requires creating an excess data point by row (“In Scope?”).

Note: if the list of valid criteria is long, consider instead using an “IN” clause, like I described in THIS ARTICLE.

4: Add a Month with EDATE

How often do you create a table with rows representing a month each? I know you’ve been there. You type the first month at top (“11/1/2018”) and then you either manually type out the next 30 rows (yuck) or use some convoluted formula where you extract the month from the above date, add 1, account for the reset to 1 after December. It sucks. This formula gets rid of that entire headache. Shouldn’t the formula be called like “ADDMONTH”? Whatever.

5: Trim Unwanted Text with SUBSTITUTE

Often, when I extract data from an outside source, I’ll find that the raw data is formatted in some unusable structure, where either a unit is built directly into the values (e.g., duration: “6 days”) or the value is wrapped in quotations or apostrophes… Don’t fix the problem manually. And don’t stew over why TRIM only allows for the removal of leading/lagging spaces. Instead, use this formula.

Now, if you want to build a SUBSTITUTE clause into a FILTER formula, that is a little more complicated. Good news: I have a solution for that. I’ll link the article here once I create it. Until then, feel free to reach out and I’ll send it over.

6: Format Titles with TEXT

This is essentially the opposite of the previous trick. The TEXT function lets you format an value exactly as you want, even if it’s attached to a string or paired with another value. In the linked example, I use it to format row titles (for example, a row entitled “8/1 - 8/16”) and to format the conclusion figure: “311.99 / period”.

Hope it was helpful. Thanks for reading!