HomeGoogle DocsSpreadsheetTwo Ways to Specify Blank Cells in Google Sheets Formulas

Two Ways to Specify Blank Cells in Google Sheets Formulas

Published on

You may already know how to specify blank cells in Google Sheets formulas.

But still, you are here because you may only know one way to do that, and it seems to make you feel the topic is quite interesting.

First of all, let me shed some light on the topic.

We may often want to specify blank cells in Google Sheets formulas mainly for two different purposes. Do you know what they are?

  1. To return an empty cell(s) as a formula output.
  2. As a condition in a formula to include or exclude blank rows.

Note:- Both may or may not come in a single formula.

When it comes to specifying blank or empty cells in Google Sheets, you can follow, mainly, two ways and let me call it;

  1. Blank character (double quotes) approach.
  2. Logical (IF test) approach.

I know most of you are well conversed with the blank character approach that by using double-quotes.

How many of you know it won’t work in all the cases, and we should follow the Logical (IF test) approach?

OK, we will discuss that at a later stage.

How to Specify Blank Cells in Google Sheets Formulas?

1. Blank Character (Double Quotes) Approach

It’s the usual approach and in which we can specify blank cells as follows.

Single:-

""

Multiple (Vertically):-

We can create an array of blank cells using the Curly Braces as below.

{"";"";""}

Multiple (Horizontally):-

Depending on your Locale settings, you can use either of the below formulas.

{"","",""}

If you are in EU countries, this may work.

={""\""\""}

2. Logical (IF Test) Approach

Single:-

if(,,)

You May Like: How to Use IF Function in Google Sheets – Advanced Tips.

Multiple (Vertically):-

Here also we can create an array of blank cells using the Curly Braces.

{if(,,);if(,,);if(,,)}

Multiple (Horizontally):-

Here also, the Local settings may affect the output. Try either of the below formulas.

{if(,,),if(,,),if(,,)}

If you are in EU countries, this may work.

={if(,,)\if(,,)\if(,,)}

Formula Examples

At the beginning of this tutorial, I have mentioned the two purposes of specifying blank cells in Google Sheets formulas.

Here are examples of both of them using the above two approaches.

Return an Empty Cell as a Formula Output (Purpose 1)

Sample Data:-

The value in cell B2=”Name” and C2=”Stuart.”

The following formula in cell E2 tests whether the text string in cell B2 is “Name.”

=if(B2="Name",C2,"")

If it evaluates to TRUE, it returns the name (text string) in cell C2.

If it evaluates to FALSE, it returns blank.

Above, I have followed the Blank Character (Double Quotes) Approach to return a blank cell.

In cell E3, I have the following formula, which is similar to the E2 one. The only difference is, here I have followed the Logical (IF Test) Approach to return a blank cell.

=if(B2="Name",C2,if(,,))
Specifying Blank Cells in Google Sheets Formulas - Example

Specifying Blank Cell as a Condition in a Formula (Purpose 2)

Sample Data:-

Cell Range B2:B10 contains a few text strings and empty cells.

I want to extract only the text strings. With the help of the FILTER function, we can do that.

Here is how to specify blank cells as a condition in a Filter formula in Google Sheets.

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 the functions, except Database functions.

In Database Formulas, it seems only the Logical (IF Test) Approach works. You can find an example below.

Specifying Blank Criteria Cells in Database Formulas in Google Sheets

When it comes to getting expanding array results in row or column-wise, Database functions such as DMIN, DMAX, DSUM, DCOUNT are my choice.

While coding such formulas, we may require to specify empty criteria.

Here I am not going to detail how to use Database functions to return expanding results.

Because that you can find under the tag row-wise array.

Here I will show you how to specify blank/empty criteria in Database functions.

For the example purpose, I am taking the function DSUM.

=dsum(B2:C9,2,E2:E3)

It returns the total ‘Quantity’ based on the condition in E2:E3.

How to Specify Empty Cells in Criteria in Database Functions

Here to avoid using criteria/conditions, you may remove the values in E2:E3.

But I don’t want to refer to cell E2:E3.

Then how to specify two blank cells within the DSUM function in Google Sheets?

=dsum(B2:C9,2,{"";""})

Nope! It won’t work.

But the following formula will!

=dsum(B2:C9,2,{if(,,);if(,,)})

In short, to specify blank/empty cells within the 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 will equally work well.

That’s all. Thanks for the stay. Enjoy!

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

LEAVE A REPLY

Please enter your comment!
Please enter your name here