You may already be familiar with how to specify blank cells in Google Sheets formulas. But here’s the catch—there’s more than one way to do it, and it’s quite interesting! Let me shed some light on this topic.
We often need to specify blank cells in Google Sheets formulas for two different purposes. Can you guess what they are? Let me tell you:
- To return an empty cell(s) as a formula output.
- As a condition in a formula to include or exclude blank rows.
Note: These purposes may or may not come together in a single formula.
When it comes to specifying blank or empty cells in Google Sheets, there are mainly two approaches. Let’s dive into each of them:
1. Blank Character (Double Quotes) Approach
This is the usual approach where we specify blank cells using double quotes. You probably already know this one:
- Single:
"" - Multiple (Vertically): We can create an array of blank cells using curly braces like this:
{"";"";""} - Multiple (Horizontally): Depending on your locale settings, you can use either of these formulas:
{"","",""}or={""""""}(for EU countries)
2. Logical (IF Test) Approach
Now, here’s where things get interesting. Did you know that the blank character approach won’t work in all cases? That’s where the logical (IF test) approach comes in. Let’s explore it:
- Single:
if(,,) - Multiple (Vertically): We can create an array of blank cells using curly braces:
{if(,,);if(,,);if(,,)} - Multiple (Horizontally): Again, depending on your locale settings, try either of these formulas:
{if(,,),if(,,),if(,,)}or={if(,,)if(,,)if(,,)}(for EU countries)
Now that you’re familiar with the two approaches, let’s see some examples using these formulas.
Formula Examples
At the beginning of this tutorial, I mentioned the two purposes of specifying blank cells in Google Sheets formulas. Let’s see examples of both purposes using the two approaches.
Return an Empty Cell as a Formula Output (Purpose 1)
Here’s some sample data:
- The value in cell B2 is “Name” and C2 is “Stuart.”
Now, let’s look at the following formula in cell E2:
=if(B2="Name",C2,"")This formula tests whether the text string in cell B2 is “Name.” If it evaluates to TRUE, it returns the name (text string) in cell C2. If it evaluates to FALSE, it returns a blank cell. In this case, we used the blank character (double quotes) approach.
In cell E3, we have a similar formula:
=if(B2="Name",C2,if(,,))The only difference is that we used the logical (IF test) approach to return a blank cell.
Specifying Blank Cell as a Condition in a Formula (Purpose 2)
Now, let’s consider a scenario where we have a cell range (B2:B10) containing text strings and empty cells. We want to extract only the text strings using the FILTER function. Here’s how to specify blank cells as a condition in a FILTER formula:
Formula Using Blank Character (Double Quotes) Approach:
=filter(B2:B10,B2:B10<>"")Formula Using Logical (IF Test) Approach:
=filter(B2:B10,B2:B10<>if(,,))The above applies to almost all functions, except for database functions. In database formulas, only the logical (IF test) approach seems to work. Here’s an example:
Specifying Blank Criteria Cells in Database Formulas in Google Sheets
When using database functions such as DMIN, DMAX, DSUM, and DCOUNT to get expanding array results row or column-wise, we may need to specify empty criteria. Here’s how to do it:
For example, let’s consider the DSUM function:
=dsum(B2:C9,2,E2:E3)This formula returns the total “Quantity” based on the condition in cells E2:E3. But what if we want to specify two blank cells within the DSUM function? This won’t work:
=dsum(B2:C9,2,{"";""})Instead, use this formula:
=dsum(B2:C9,2,{if(,,);if(,,)})To specify blank/empty cells within database functions, follow the logical (IF test) approach, not the blank character (double quotes) approach.
As far as I know, in other category functions, both approaches work equally well.
That’s all for now. I hope you enjoyed this tutorial on how to specify blank cells in Google Sheets formulas. If you want to learn more about Google Sheets and enhance your productivity, visit Crawlan.com. Happy sheeting!
