How to Return Unique Nonblank Values From an Excel Range
Question details
The user needs to extract a list of distinct, non-blank text descriptions from a specific range so they can be copied to another worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering out duplicate entries and blank cells from a dropdown-selected list to generate a clean, distinct dataset.
- Observed behavior
- The goal is to generate a dynamic spilled array of unique values starting from a designated cell, automatically excluding any empty rows.
Ensure you are using a version of Excel or WPS Spreadsheet that supports Dynamic Array formulas, such as Microsoft 365, Office 2021, or the latest version of WPS Office.
Use UNIQUE and FILTER Functions to Exclude Blanks
Combine the UNIQUE and FILTER functions to dynamically extract distinct items while explicitly excluding blank cells from the results.
The UNIQUE function automatically extracts distinct values from a range, but it treats blank cells as a value (often returning a 0). By nesting the FILTER function inside UNIQUE, you can remove the blank cells before the unique values are calculated.
Click on the cell where you want the extracted list to begin (for example, A15).
Type the formula: =UNIQUE(FILTER(C12:C23, C12:C23<>""))
Press the Enter key. The distinct, non-blank descriptions will automatically spill down the column.
Use the UNIQUE Function Only (If Blanks are Acceptable)
If having a blank row in your final extracted list is acceptable, you can use the standalone UNIQUE function for a simpler formula.
Extract Only Values Occurring More Than Once
If your goal is to identify which items are duplicates by returning only the values that appear multiple times, combine UNIQUE, FILTER, and COUNTIF.
Extract Unique Values Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE and FILTER, allowing you to instantly clean your data and remove blank entries just like in Microsoft Excel.
- 1. Open your file: Launch WPS Spreadsheet and open your workbook containing the source data.
- 2. Select the target cell: Click on the cell where you want the clean, distinct list to start.
- 3. Input the formula: Type =UNIQUE(FILTER(C12:C23, C12:C23<>"")) and press Enter to instantly spill the results.
- 4. Copy the results: If you need to move the data, select the spilled array, copy it, and use 'Paste as Values' in your destination worksheet.
Frequently Asked Questions
Why am I getting a #SPILL! error when using the UNIQUE function?
A #SPILL! error occurs when the destination range needed for the dynamic array is blocked by existing data. Clear the cells below your formula to provide enough empty space for the results to expand.
What if my version of Excel doesn't support the UNIQUE function?
If you are using an older version like Excel 2016 or 2019, you can use the 'Advanced Filter' feature under the Data tab to copy unique records to a new location. Alternatively, you can upgrade to WPS Office for free to access modern dynamic array formulas.
How can I sort the extracted unique values alphabetically?
You can wrap the SORT function around your existing formula. Simply update your formula to: =SORT(UNIQUE(FILTER(C12:C23, C12:C23<>""))).
