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:
- Select the data range you’d like to filter.
- Click on the “Data” tab in the menu, and choose “Create a filter”.
- Click the filter icon that appears in the header of the column you wish to filter by.
- Choose “Filter by condition” and select “Is not empty” from the dropdown list.
- 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, ErinaExcluding 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
.
