ThisCalculator

Everyday

Unique Values Ignore Blanks? Explained

Updated · Standard formula, shown below

Yes, in virtually every spreadsheet and data tool, the UNIQUE function and similar distinct-value operations ignore blank cells by default. This means that when you ask for a list of unique values from a range, empty cells are not included in the result, and you don’t need to filter them out manually. The behavior is consistent across Excel, Google Sheets, and most programming languages’ distinct functions, though there are edge cases and options worth understanding.

How UNIQUE Works in Excel and Google Sheets

In Excel, the UNIQUE function (available in Excel 365 and Excel 2021) returns a list of unique values from a range or array. Its syntax is UNIQUE(array, [by_col], [exactly_once]). The function automatically excludes blank cells from the output. For example, if you have a column with values Apple, Banana, (blank), Apple, the formula =UNIQUE(A1:A4) returns Apple and Banana in a spill range. The blank cell is simply ignored.

Google Sheets has an identical UNIQUE function with the same behavior. It also ignores blank cells by default. If you want to include blanks as a distinct value, you would need to use a workaround, such as replacing blanks with a placeholder text before applying UNIQUE.

Why Do Blank Cells Get Ignored?

The rationale is that blank cells represent the absence of data, not a value itself. Including them would clutter the unique list with an empty entry, making it less useful for data analysis and reporting. Most data-cleaning operations assume blanks are noise that should be removed. The UNIQUE function aligns with this principle, treating blanks as non-values.

In programming, functions like numpy.unique() in Python also exclude NaN values by default (though not None in some contexts). Similarly, SQL’s SELECT DISTINCT treats NULLs as a single group, but many database systems have quirks. In spreadsheets, the behavior is consistent: blanks are ignored.

How to Include Blanks If You Really Need Them

There are rare cases where you might want blanks to appear as a value, for example, when tracking missing data. To include blanks in the unique list, you can replace them with a placeholder string before applying UNIQUE. In Excel, you could use =UNIQUE(IF(A1:A10="","[blank]",A1:A10)) as an array formula (entered with Ctrl+Shift+Enter in older versions). In Google Sheets, the same formula works as an array formula without special entry. After getting the list, you can optionally replace the placeholder back with an empty string if needed.

Another approach is to use a helper column that converts blanks to a unique text like “N/A” and then run UNIQUE on that column. This is straightforward and avoids complex array formulas.

Common Misconceptions and Edge Cases

A frequent misconception is that UNIQUE treats blank cells as a single unique value, but it doesn’t. It simply omits them. Another edge case: if your range contains formulas that return empty strings (""), those are NOT considered blank. An empty string is a text value, so UNIQUE will include it as a distinct entry. To ignore empty strings, you need to filter them out first, for example, using FILTER in Excel: =UNIQUE(FILTER(A1:A10,A1:A10<>"")).

Also, note that UNIQUE is case-insensitive in Excel and Google Sheets? Actually, it is case-sensitive in Excel (e.g., “Apple” and “apple” are different), while Google Sheets’ UNIQUE is case-insensitive. This is a key difference when working with text data.

Alternatives to UNIQUE for Distinct Values

If you’re using an older version of Excel that doesn’t have UNIQUE, you can use the Remove Duplicates feature (Data tab > Remove Duplicates) which also ignores blank cells. Or you can use a pivot table, which by default does not show blank items. In Google Sheets, you can use the UNIQUE function, or the QUERY function with SELECT DISTINCT, which also ignores blanks.

In programming, Python’s set() will include None if present, but you can filter it out. Pandas’ unique() excludes NaN by default. In R, unique() includes NA, but you can use na.omit() first.

Frequently Asked Questions

Does UNIQUE ignore blank cells in Excel?

Yes, the UNIQUE function in Excel automatically ignores blank cells. If your range has empty cells, they will not appear in the resulting list of unique values.

How do I include blanks in the UNIQUE output?

You can replace blanks with a placeholder text before applying UNIQUE. For example, use =UNIQUE(IF(A1:A10="","[blank]",A1:A10)) as an array formula. Then you can replace the placeholder with an empty string if needed.

Does UNIQUE ignore empty strings (“”) in Excel?

No, UNIQUE treats empty strings as text values, so it will include them in the output. To ignore them, you need to filter them out first using FILTER or a similar approach.

Is UNIQUE case-sensitive in Excel and Google Sheets?

Yes, Excel’s UNIQUE is case-sensitive, meaning “Apple” and “apple” are considered different values. Google Sheets’ UNIQUE is case-insensitive, treating them as the same value.

What about blanks in a range with formulas that return “”?

If a formula returns an empty string, it is not a blank cell. UNIQUE will include it. To exclude it, use =UNIQUE(FILTER(range, range<>"")) in Excel.

Can I use UNIQUE with multiple columns?

Yes, UNIQUE can work on a multi-column range. It will return unique combinations of values across the columns. Blank cells in any column are ignored in the sense that rows with blanks are still considered, but the blank itself doesn’t become a value.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.