Filter Exclude Blank Values? How It Works

Yes, when you apply a filter in Excel, Google Sheets, or most database tools, blank cells are excluded from the results by default—unless you explicitly choose to include them. This article explains exactly how blank values behave in filters, how to control them, and why they sometimes appear when you don’t expect them.

What Does “Exclude Blank Values” Mean in a Filter?

When you filter a column of data, the filter engine evaluates each cell against your criteria. A blank cell—one that contains nothing or only spaces—does not match any specific value like “Yes,” “42,” or “contains ‘abc’.” Therefore, unless you specifically select “(Blanks)” in the filter dropdown, blank rows are hidden from the filtered view. This is true in Microsoft Excel, Google Sheets, Apple Numbers, and SQL WHERE clauses (where NULL is treated separately).

How Blank Values Are Treated in Different Tools

The behavior is consistent but the interface differs slightly:

• Excel: In the filter dropdown, you’ll see a checkbox labeled “(Blanks)” at the bottom of the list. Unchecking it hides blank rows; checking it shows only blank rows.
• Google Sheets: The filter dropdown has a “(Blanks)” option at the bottom. You can also use the FILTER function with ISBLANK or <>"" to exclude blanks.
• SQL: Use WHERE column IS NOT NULL to exclude NULLs, and WHERE column <> '' to exclude empty strings. Note that a NULL and an empty string are different—NULL means “no value,” while an empty string is a value that happens to be zero characters long.
• Pivot tables: By default, pivot tables ignore blank cells in the data source, but if the source contains blank rows, they may appear as “(blank)” in row labels.

How to Explicitly Exclude Blank Values

Here are step-by-step methods for common tools:

1. Excel / Google Sheets (manual filter): Click the filter arrow on the column header, uncheck “(Select All)”, then scroll to the bottom and uncheck “(Blanks)”. Click OK. This hides rows where that column is blank.
2. Excel / Google Sheets (formula): Use =FILTER(A2:C100, B2:B100<>"") in Google Sheets, or in Excel 365 use =FILTER(A2:C100, B2:B100<>""). This returns all rows where column B is not empty.
3. SQL: Write SELECT * FROM table WHERE column IS NOT NULL AND column <> '' to exclude both NULLs and empty strings.
4. Power Query (Excel): After loading data, select the column, then in the Home tab click “Remove Rows” → “Remove Blank Rows.” This removes entire rows that have no values in any column.

Common Mistakes and Misconceptions

One frequent misconception is that “blank” and “empty string” are the same. They are not. In Excel, a cell with ="" looks blank but is not. In databases, NULL is a special marker meaning “unknown,” and empty string is a known value. Another mistake: using ISBLANK() in Excel to filter—this function only returns TRUE for truly empty cells, not for cells with formulas that return "". To catch those, use COUNTIF or SUMPRODUCT with "".

Also, when copying data from external sources, trailing spaces are common. Use TRIM() to remove them before filtering. A quick way to find non-printing characters is to use CLEAN() in Excel.

Filtering Blank Values in Data Analysis

In data analysis, excluding blanks is often essential for accurate calculations. For example, if you compute an average with AVERAGE(), Excel ignores blank cells but includes zeros. If you want to exclude both blanks and zeros, you need AVERAGEIF(range, "<>0"). In pivot tables, blanks in the data area are automatically excluded from sums and averages, but a blank row label can appear if the source has a blank in the row field—you can right-click and choose “Filter” → “Hide (blank)”.

Alternatives to Filtering Out Blanks

Sometimes you don’t want to hide blanks but rather replace them with a default value. Options:

• Use IF formulas: =IF(A2="", "N/A", A2) replaces blanks with “N/A”.
• Use COALESCE in SQL: SELECT COALESCE(column, 'N/A') FROM table replaces NULLs.
• Use Find & Replace: In Excel, select the range, press Ctrl+H, leave “Find what” blank, enter a replacement, and click “Replace All.” This fills all blank cells with the replacement value.
• Use Power Query’s “Replace Values”: Replace null with a chosen value before loading.

Frequently Asked Questions

Why does my filter show blank rows even though I didn’t select “(Blanks)”?

Check for spaces or non-breaking characters in the cells. Use =LEN(TRIM(A2)) to see if the length is zero after trimming. If it’s not zero, the cell has invisible characters. Also, if the filter is applied to a column that contains formulas returning "", those will appear. Use a helper column with =ISBLANK(A2) to see which cells are truly empty.

How do I exclude blank values in Google Sheets using a formula?

Use the FILTER function: =FILTER(A2:C100, B2:B100<>""). This returns rows where column B is not empty. For truly blank cells (no formula), you can use ISBLANK: =FILTER(A2:C100, NOT(ISBLANK(B2:B100))). Note that ISBLANK returns FALSE for cells with "".

What is the difference between NULL and empty string in SQL?

NULL means the value is unknown or missing. Empty string ('') is a known value that is zero characters long. In filtering, IS NULL checks for NULL, while = '' checks for empty string. To exclude both, use WHERE column IS NOT NULL AND column <> ''.

Can I filter out blank rows across multiple columns at once?

Yes. In Excel, you can use the “Advanced Filter” with a criteria range that has <>"" in each column. In Google Sheets, use FILTER with multiple conditions: =FILTER(A2:C100, (A2:A100<>"")*(B2:B100<>"")). In Power Query, select all columns, then “Remove Rows” → “Remove Blank Rows” removes rows that are entirely blank, but you can also filter each column individually.

Why does my pivot table show “(blank)” in row labels?

This happens when the source data has blank cells in the field you’re using as a row label. To hide it, right-click the “(blank)” item and select “Filter” → “Hide (blank)”. Alternatively, replace blanks in your source data with a placeholder like “N/A” before creating the pivot table.

How do I count non-blank cells in Excel?

Use COUNTA(range) to count cells that are not empty, including cells with formulas that return "". If you want to exclude those, use COUNTIF(range, "<>""") or SUMPRODUCT((range<>"")*1). For truly blank cells, use COUNTBLANK(range).

Related

• Garmin Express Not Finding Devices? Fix It Now
• Best 3D Printer Brands for Budget, Workflow & Detail
• Python Operators & Conditionals: Make Decisions