How to Use UNIQUE While Excluding Blank Cells in WPS Office Spreadsheets
Question details
The user needs a formula that returns a distinct list from a range while excluding empty cells in WPS Office Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages a list that contains blank cells and wants to create a dynamic unique list containing only populated values.
- Observed behavior
- UNIQUE returns a blank result when empty cells exist in the source range. The intended state is a distinct list with no blanks.
Open WPS Office Spreadsheets and verify that the UNIQUE and TOCOL functions are available in your version. Identify the source range that contains blank cells and prepare an output cell where the dynamic list can spill.
Use =UNIQUE(TOCOL(range,1)) to Exclude Blank Cells
Use this formula when you need one dynamic distinct list that automatically skips empty cells in WPS Office Spreadsheets.
The TOCOL function converts the source range to a single column. Its second argument, 1, tells TOCOL to ignore blank cells. UNIQUE then removes duplicate values from that filtered column.
Select the cell where you want the unique list to begin, type =UNIQUE(TOCOL(A1:A50,1)), and press Enter.
Replace A1:A50 with your data range and extend it to include future entries, for example =UNIQUE(TOCOL(A1:A100,1)).
Verify that the spilled list contains only populated distinct values and no blank cells.
Create Blank-Free Unique Lists in WPS Office Spreadsheets
Use WPS Office Spreadsheets to build a dynamic distinct list from a range that contains empty cells. Enter the UNIQUE(TOCOL()) formula and WPS returns only populated unique values automatically.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the workbook that contains the range with blank cells.
- 2. Select the output cell: Click the first cell where you want the unique list to appear.
- 3. Enter the formula: Type =UNIQUE(TOCOL(A1:A50,1)) and press Enter. Replace A1:A50 with your actual data range.
- 4. Adjust for future entries: Extend the range if needed, for example =UNIQUE(TOCOL(A1:A200,1)), so new values are included automatically.
Frequently Asked Questions
Why does UNIQUE return a blank cell in WPS Office Spreadsheets?
UNIQUE treats empty cells in the source range as a valid value, so a blank appears in the distinct list. Wrap the range with TOCOL using =UNIQUE(TOCOL(A1:A50,1)) to ignore blanks.
Can I use UNIQUE with TOCOL on multiple columns?
Yes. Use a formula like =UNIQUE(TOCOL(A1:C50,1)) to stack the range into one column, ignore blanks, and remove duplicates.
Which WPS Office versions support UNIQUE and TOCOL?
These dynamic array functions are available in current WPS Office Spreadsheets versions that support modern Excel functions. If the formula returns #NAME?, update WPS Office to the latest version.
How do I make the unique list update automatically when new data is added?
Extend the range inside the formula, for example =UNIQUE(TOCOL(A1:A200,1)). New entries within that range are included in the unique list automatically.
Can this formula be used as a source for a drop-down list?
Yes, you can reference the spill range created by the formula when defining a data validation list, so the drop-down list also ignores blank entries.
