Googlesheets
No Responses June 3, 2025

How to Extract Unique Non-Blank Values in Google Sheets: A Step-by-Step Guide

Google Sheets is a powerful tool, but some simple tasks can be unexpectedly tricky. A common challenge is extracting unique values from a column while ignoring blank cells.

In this blog, I’ll explain why the UNIQUE function alone isn’t enough, and how to fix the issue with a straightforward formula.

The Problem: UNIQUE Includes Blank Cells

Consider the following dataset in column D:

When you use the formula:

=UNIQUE(D2:D)

The result is:

The issue here is that the UNIQUE function treats the blank cell as a unique value, which is often undesirable.

The Solution: UNIQUE + FILTER

To exclude blank cells while extracting unique values, combine the UNIQUE and FILTER functions. Use this formula:

=UNIQUE(FILTER(D2:D, LEN(D2:D)))

Impact

  • The duplicate “Work” and “Personal” entries are removed.
  • The blank cell is excluded.
  • The final result is a clean, unique list of values.

Conclusion

The combination of UNIQUE and FILTER is a powerful way to ensure your data is clean, unique, and ready for use. Whether you’re working on a report, dashboard, or simply organizing information, this formula can save time and frustration.