How to Return Nonblank Values Without Gaps in WPS Office Excel
Question details
The user needs a formula in WPS Office Spreadsheets that takes a column with empty cells and returns only the nonblank values consecutively in another column, optionally sorted.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is creating a compact list from a source column that includes blank cells and wants to avoid gaps in the output; sorting the resulting values numerically is also mentioned.
- Observed behavior
- A typical formula copied down returns blank cells for blank source rows, leaving gaps in the output list.
Open the workbook in WPS Office Spreadsheets and identify the source column and the first output cell. If you plan to sort the result, keep the output in a separate column so the source data remains unchanged.
Use the FILTER Dynamic Array Formula in WPS Office Spreadsheets
Use this method when your WPS Office version supports dynamic arrays and you want a single formula that automatically spills all nonblank values into a compact list.
The FILTER function tests every cell in the source range and returns only the cells that meet the condition, so empty cells are skipped without dragging a formula down.
Click the cell where the compact list should start, such as C1.
Type =FILTER(A:A,A:A<>"") and press Enter. WPS Office will spill all nonblank values from column A into C1 and down.
To avoid processing the entire column, replace A:A with a fixed range such as A1:A100, for example =FILTER(A1:A100,A1:A100<>"").
Use an AGGREGATE Formula for Older WPS Spreadsheets Versions
Use this method if your WPS Office version does not support the FILTER function or you need a formula that can be copied down row by row.
Sort the Resulting Nonblank List by Value
Use this method after extracting the list when the values must appear in numerical order rather than the original source order.
Create a Gap-Free Nonblank List in WPS Office Spreadsheets
WPS Office Spreadsheets makes it easy to extract only the nonblank values from a column and place them in a continuous list. The formulas work with common XLSX files and give you a lightweight, free alternative for handling Excel formula tasks.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the workbook that contains the source column with blank cells.
- 2. Click the first output cell: Select the cell where you want the compact nonblank list to begin, such as C1.
- 3. Enter the nonblank extraction formula: Type =FILTER(A:A,A:A<>"") and press Enter. If your version does not support FILTER, use the AGGREGATE formula shown above.
- 4. Copy or sort the returned values: If you need a static list, copy the returned values and paste them as values. To reorder them, select the output column and click Data > Sort.
Frequently Asked Questions
Does WPS Office support the FILTER function?
Recent versions of WPS Office Spreadsheets support FILTER. If the formula is not available, you can open the workbook and use the AGGREGATE-based formula described in this article to achieve the same nonblank list.
How can I return nonblank values in sorted order without gaps?
Extract the values first with =FILTER(A:A,A:A<>"") or the AGGREGATE formula, then select the output column, go to the Data tab, click Sort, and choose Smallest to Largest.
Why does my formula return 0 or a blank when copied down?
Make sure the IFERROR final argument is "" so no values are displayed for non-matching rows, and check that the source range and output column references are correct. If you use legacy array formulas, press Ctrl+Shift+Enter.
Can I extract nonblank values from a range with multiple columns?
FILTER can return multiple columns, but the compact column list method in this article is designed for a single source column. For multiple columns, use FILTER with a condition such as =FILTER(A1:B100,(A1:A100<>"")*(B1:B100<>"")).
How do I avoid a #SPILL error in WPS Office Spreadsheets?
Clear the cells directly below the formula cell because the spill range must be empty. If you need the list somewhere else, move the formula to a cell with enough blank cells below it.
