ExcelInsider Team
Checking whether a cell is empty or not in Google Sheets is essential for effective data management and analysis. This enables users to apply conditional formatting and formulas with logic, only when the necessary data is present. Google Sheets has many built-in tools that we can use to determine whether a cell is not empty.
Follow the steps below to determine whether a cell is not empty in Google Sheets:
➤ Open the spreadsheet file and select the cell where you want to display the output result.
➤ In the selected cell, type the formula:
=IF(A2<>””, “Not Empty”, “Empty”)
➤ Replace “A2” with the cell reference you want to check. This formula will return “Not Empty” if the cell contains any value, and “Empty” if the cell is blank.
➤ Then, press Enter to finally display the output.
In this article, we will learn seven easy methods of checking if a cell is not empty in Google Sheets.
Check If a Cell is Not Empty Using IF Function
This is the simplest and straightforward way of finding out whether a cell contains any value or not. The IF function in Google Sheets is used to perform logical tests and return results based on whether a condition is true or false.
In the sample dataset, we have a worksheet called “Employee Attendance” containing information on Employee name, Department, Days Present, Days Absent and the City. By using the IF function, we will check if any cells in column E are not empty. The output will be displayed in column F of a separate worksheet named “IF Function”.
Steps:
➤ In the IF Function worksheet, select F2 cell and put the following formula:
=IF(E2<>"", "Not Empty", "Empty")
Note:
In the formula, the IF() function checks whether cell E2 contains any data. (E2<>””, “Not Empty”, “Empty”) returns Empty or Not Empty by checking if the value in cell E2 is not equal to an empty string (“”). If cell E2 contains any data, the formula returns “Not Empty”. Whereas, if cell E2 does not contain any data, it returns “Empty”.
➤ Next, press Enter, and drag F2 Cell to apply the formula to the rest of the cells in column F. Column F should now display whether the corresponding cells in column E are empty or not empty.
Note:
Instead of manually dragging the cells, you use the autofill feature to automatically fill the column.
Using ISBLANK with NOT Function
This is another simple method that you can use to check if a cell is not empty in Google Sheets. The ISBLANK function in Google Sheets is a useful tool that checks whether a cell is empty or not. Just like the first method, we will work with the same dataset and display the result in column F of the new “ISBLANK Function” worksheet.
Steps:
➤ Open the ISBLANK Function worksheet and in cell F2, put the following formula:
=IF(NOT(ISBLANK(E2)), "Not empty", "Empty")
Note:
The (ISBLANK(E2) part of the formula checks whether the cell E2 is empty (TRUE) or if it contains something (FALSE). NOT() reverses the logic, displaying TRUE when the cell is Not Empty and FALSE when it is Empty. Finally, the IF() function evaluates the result and outputs “Not Empty” if the result is TRUE and “EMPTY” when the result is FALSE.
➤ Then, press Enter and drag the F2 cell to fill all cells in column F with the desired output.
Using COUNTA Function to Flag Not Empty Cells
Just like the first two methods, this approach also performs the same task of checking whether a cell is not empty in Google Sheets. The COUNTA function lets users count the number of non-empty cells within a specified range in Google Sheets. We will again work with the same dataset and show the result in column F of the COUNTA Function worksheet.
Steps:
➤ In the COUNTA Function worksheet, select cell F2 and put the formula:
=IF(COUNTA(E2)>0, "Not empty", "Empty")
Note:
COUNTA(E2) part of the formula counts if there is any content in cell E2. If the cell contains any text or numbers, the formula returns 1. And for empty cells, it returns 0. The “>0” condition checks whether the count is greater than 0, meaning the cell is Not Empty. If TRUE (E2 is not empty), the formula outputs “Not Empty”; if FALSE (E2 is empty), it outputs “Empty”.
➤ Next, press Enter and drag the F2 cell to fill all cells in column F. The results should now be displayed.
Using LEN Function to Return TRUE/FALSE For Not Empty Cells
Unlike the first three methods, this method uses the LEN function and shows the result as either TRUE or FALSE. The LEN function in Google Sheets is used to display the number of characters in a cell or text string. Working with the same dataset, we will use the LEN function to check if a cell is not empty. We will display the output in column F of the LEN Function worksheet.
Steps:
➤ Open the LEN Function worksheet and in cell F2, put the formula:
=LEN(E2)>0
Note:
LEN(E2) counts the number of characters present in cell E2. If the number of characters is greater than 0 (meaning the cell is not empty), the formula returns TRUE. On the other hand, if the number of characters is equal to 0 (cell is empty), the formula returns FALSE.
➤ Lastly, press Enter, drag cell F2 and fill all cells in column F to display the desired output.
Using ARRAYFORMULA to Display Empty Cells At Once
You can also use the ARRAYFORMULA function to check whether a cell is not empty in Google Sheets. ARRAYFORMULA in Google Sheets is a powerful function that allows users to apply any formula across a range of cells all at once, eliminating the need to drag cells manually, saving time and improving efficiency. We will again work with the same dataset and display the result in column F of the ARRAYFORMULA Function worksheet.
Steps:
➤ In the ARRAYFORMULA Function worksheet, select Cell F2 and put the formula:
=ARRAYFORMULA(IF(E2:E13<>"", "Not Empty", "Empty"))
Note:
(IF(E2:E13<>””, “Not Empty”, “Empty”) part of the formula checks all cells in the range E2 to E13, returning “Not Empty” if a cell contains data and “EMPTY” if the cell is empty. Finally, ARRAYFORMULA() applies the formula across the entire range of the dataset.
➤ Next, press Enter, and you should have the results properly displayed in column F.
Applying Conditional Formatting to Visually Highlight Non Empty Cells
Conditional formatting in Google Sheets is a useful feature that lets users automatically change the appearance of cells based on specific conditions or rules. Unlike other methods, this method visually displays the result by highlighting non-empty cells in a different color from empty ones, making it easier to identify which cells contain data. We will once again use the same dataset and display the results by changing the background color of the cells in column E of the Conditional Formatting worksheet.
Steps:
➤ In the Conditional Formatting worksheet, select cells E2 to E13.
➤Then, from the main menu, head to Format >> Conditional formatting.
➤ In the Format rules of the Conditional format rules menu, select Custom formula is and paste the following formula:
=NOT(ISBLANK(E2))
Note:
(ISBLANK(E2)) part of the formula returns the value TRUE if the cell is empty, and FALSE if it contains any data. NOT() reverses this, so the formula returns TRUE if the cell contains any value, and FALSE when it is empty.
➤ Next, click on Done. Now, all non-empty cells in column E will be highlighted, while empty cells will not be highlighted.
Running Custom Apps Script To Automate Not Empty Cell Detection
Google’s Apps Script is another powerful tool that lets users automate their tasks and customize workflows using custom scripts. You can check whether a cell is empty or not empty using this powerful tool. Working with the same dataset, we will display the output in column F of Apps Script worksheet.
Steps:
➤ Open the Apps Script worksheet and from the main menu head to Extensions >> Apps Script.
➤ In the script editor, paste the following code:
function checkCityNotEmpty() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange("E2:E13");
var values = range.getValues();
var results = [ ];
for (var i = 0; i < values.length; i++) {
if (values[i][0] !== "") {
results.push(["Not Empty"]);
} else {
results.push(["Empty"]);
}
}
sheet.getRange(2, 6, 12, 1).setValues(results);
}
Note:
In the custom script,
➧ function checkCityNotEmpty(), this part of the script defines the function that will run the script when called.
➧ var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); gets the currently active spreadsheet.
➧ var range = sheet.getRange(“E2:E13”); defines the range of the script and selects cells E2 to E13.
➧ var values = range.getValues(); it retrieves the value inside the specified range.
➧ var results = [ ]; this creates an empty list to store Empty or Not Empty labels.
➧ for (var i = 0; i < values.length; i++) it loops through each cell in the defined range.
➧ if (values[i][0] !== “”) check whether the cell contains any data.
➧ results.push([“Not Empty”]);
} else {
results.push([“Empty”]);
Part of the script adds “Not Empty” to the results array if the cell is empty; otherwise, it adds “Empty.”
➧ sheet.getRange(2, 6, 12, 1).setValues(results); outputs the results into the range F2 to F13, showing “Empty” or “Not Empty” for each row.
➤ Next, click on Run from the script editor menu and wait for the script to execute.
➤ Lastly, head to the Apps Script worksheet, and you should see the results being displayed in column F.
Frequently Asked Questions
Why Does My Formula Return Incorrect Results for Non-Empty Cells?
Your formula might display incorrect results if the cell reference is incorrect or if there are any hidden characters like extra spaces, line breaks, or invisible formatting. To avoid this issue, make sure that the cell reference is accurate and remove any unnecessary spaces or hidden characters from the dataset.
Which Method Should I Choose For Working With Large Datasets?
If you are working with large datasets, the ARRAYFORMULA and the Apps Script method would be most efficient. ARRAYFORMULA can apply the formula across all cells at once, saving time and effort. Apps Script, on the other hand, automates the process for large datasets by using a custom script.
Does Conditional Formatting Affect Spreadsheet Performance?
For small to medium datasets, Conditional Formatting does not have much impact on the spreadsheet’s performance. However, if you are working with large datasets, it is best to avoid this method as it may slow down your spreadsheet and affect responsiveness.
Concluding Words
Knowing how to check if a cell is not empty in Google Sheets is vital for improving data accuracy and ensuring formulas, formatting and logical conditions are applied only when relevant data is present. In this article, we learned seven useful methods for checking if a cell is not empty in Google Sheets, including using the IF function, ISBLANK function, COUNTA function, LEN function, ARRAYFORMULA function, Conditional Formatting and custom Apps Script. Feel free to try out every method and select one that is best suited to your requirements.
