Script Everything

Ryan Sheehy

Not Null In Google Sheets

by

How can you check if a cell or value is not null in Google Sheets?

There are several ways to check if a cell is not null in Google Sheets with formulas like ISBLANK() or simple operator checks like "<>" with formulas like AVERAGEIF and COUNTIF .

Similar to a previous post I wrote on checking if a cell is empty the check for a cell not being null is a little of the same but also a little bit different.

Creating a New Google Sheet

Welcome to the world of Google Sheets! To get started, head over to sheets.google.com and sign in with your Google account. Click the ‘+’ button to create a new sheet, or you can open a pre-existing file from your Google Drive. Remember to give your sheet a descriptive name so you can easily find it later.

Familiarising Yourself with the Interface

Google Sheets has an intuitive interface that should be familiar if you’ve used any spreadsheet software before. At the top, you’ll find a toolbar with standard formatting and editing options, while columns and rows are labelled with letters and numbers, respectively. The formula bar, where you key in your formulas and functions, is just above the spreadsheet. Lastly, there are multiple tabs at the bottom of the sheet for organising your data into separate sheets within the same file.

What Is A Null Value?

A null value is a ‘blank’ or ’empty’ cell present in your Google Sheet.

It is not the same as a cell containing a zero or an empty string (“”); it signifies a complete absence of data in that particular cell.

Null values can occur when you import data from external sources, as placeholders, or due to data entry errors.

How null values can impact your data and calculations

Null values can cause problems when working with your data, especially in calculations. Some formulas in Google Sheets may not work correctly when they include null values, which could lead to incorrect results or misinformation. Moreover, null values can result in errors while applying filters, organising data, or generating pivot tables and charts. Hence, it’s important to address null values in your data to ensure accuracy in your analysis.

Common reasons for null values in Google Sheets

There are several reasons why you might encounter null values in your Google Sheets, such as:

  • Missing or incomplete data from the original source
  • Data entry errors when inputting information manually
  • Cells purposely left blank to indicate a lack of data or as placeholders
  • Incorrectly imported data or formatting issues when transferring data
  • Formulas that result in errors due to incomplete data or invalid operations

Understanding the root cause of null values in your data can help you make data-driven decisions and improve the overall quality of your Google Sheet.

Identifying Null Values in Your Sheet

Using the ISBLANK() Function

The ISBLANK() function is a powerful tool to identify null values in your Google Sheet. It returns TRUE if a cell is blank, and FALSE if it contains any data (including numbers, text, or other characters).

To use this function, simply type =ISBLANK(CELL) in an empty cell, where CELL refers to the cell you want to check for null values.

Visual Inspection and Sorting Data

Another way to identify null values is by visually inspecting your sheet and sorting the data by columns. To sort your data, simply click on a column header, then select ‘Sort sheet A-Z’ or ‘Sort sheet Z-A’. This will group the null and not null cells together, making it easier to spot which cells are empty.

COUNTIF() Or COUNTA()

It’s important to locate which columns have a high proportion of null values in your dataset. To do this, use the COUNTIF and COUNTA functions. The COUNTIF function calculates the number of cells that meet a specific criterion, while the COUNTA function counts the number of non-empty cells .

You can read more about COUNTIF and null values here .

Filtering Rows With Not Null Values

To have a clean dataset which only contains not null values, you may need to filter out the rows with null values. In Google Sheets, there are a few methods available to achieve this:

Using the FILTER() Function

The FILTER() function is a simple way to extract not null rows from your dataset. This function takes two arguments: the range of data to be filtered, and the condition for filtering.

To filter out rows with not null values, you can combine FILTER with the NOT and ISBLANK functions like so: 

=FILTER(A1:C100, NOT(ISBLANK(A1:A100)))

In this example, the function will filter rows in the range A1 to C100 based on whether the corresponding cell in column A is not blank.

Applying Filters Through the Google Sheets Interface

Another method for filtering not null rows involves using the built-in filter feature of Google Sheets. To accomplish this, follow these steps:

  1. Select the data range you’d like to filter.
  2. Click on the “Data” tab in the menu, and choose “Create a filter”.
  3. Click the filter icon that appears in the header of the column you wish to filter by.
  4. Choose “Filter by condition” and select “Is not empty” from the dropdown list.
  5. Click “OK” to apply the filter and display only the not null rows in your range.

Transforming Null Data

You may want to transform your data in your dataset so that it can help make any calculations performed more meaningful. Here are a couple of examples using different techniques involving null values which may assist.

Use Case: Creating Full Names

In some cases, you might find that you need to combine data across multiple columns. One popular use case I have found is when creating a full name in a list of names where the dataset contains First Name, Middle Name and Surname.

Here’s an example of a dataset:

A B C
1 First Name Middle Name Surname
2 John Alex Smith
3 Jane Doe
4 Alan Jones

As you can see from the dataset table there are two individuals who do not have a middle name, if I were to apply a simple formula to create a full name of: 

=CONCATENATE(A2, " ", B2, " ", C2)

Then I would have extra spacing between the First Name and Surname of the rows where no Middle Name exists: 

John Alex Smith
Jane  Doe
Alan  Jones

(Notice the extra space between “Jane” and “Doe”)

One method could be to use the IF() function to determine if the cell is null, or you use the TRIM() method to automatically remove the white space should there be no middle name.

This would look something like so: 

=CONCATENATE(TRIM(CONCATENATE(A2, " ", B2)), " ", C2)

And the result would be: 

John Alex Smith
Jane Doe
Alan Jones

As you can see because of the null middle name value and the combination of multiple CONCATENATE functions with TRIM it creates the right full name without adding an extra space.

Use Case: Addresses

Another common case I have found when trying to ignore null values is creating full string addresses.

Here’s a small sample of addresses where some contain units in the first address line and others do not:

A B C
1 Address 1 Address 2 Suburb
2 Unit 1 10 Martin Place Sydney
3 20 Drivers Lane Perth
4 200 Central Coast Hwy Erina

The only difference in combining the addresses is that usually when they are concatenated together they are separated by a comma for each address section. The simple formula to achieve this would be to use CONCATENATE like so: 

=CONCATENATE(A2, ", ", B2, ", ", C2)

However, this produces the following output: 

Unit 1, 10 Martin Place, Sydney
20 Drivers Lane, , Perth
200 Central Coast Hwy, , Erina

Combining TRIM as performed previously with the names will not work as easily here. Instead using an IF function could help: 

=CONCATENATE(A2, ", ", IF(ISBLANK(B2), C2, CONCATENATE(B2, ", ", C2)))

This would produce the result: 

Unit 1, 10 Martin Place, Sydney
20 Drivers Lane, Perth
200 Central Coast Hwy, Erina

Excluding Null Values From Calculations

When performing calculations in Google Sheets, it’s essential to remove null values from your dataset to ensure your results are accurate and reliable.

For example, if you wanted to calculate the average of a range of numbers excluding null values, you could use the following formula with the syntax "<>" meaning to exclude null cells: 

=AVERAGEIF(A1:A10, "<>")

In this formula, A1:A10 is the range that you want to calculate the average for, while "<>" in the condition excludes null values from the calculation.

The same logic can be applied to COUNTIF when excluding null values too .