How to Use FILTER with SORTBY to Exclude Blank Excel Formula Results
Question details
The user needs to sort a range of data using SORTBY while excluding blank strings generated by formulas.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Sorting dynamic data ranges where formulas return empty strings. The user wants to ignore these blank rows so they do not incorrectly appear at the top of the sorted list.
- Observed behavior
- When using the SORTBY function alone, blank formula results are pushed to the top. When attempting to nest the FILTER function to remove these blanks, the user receives an array size mismatch error because the source array and sort array end up with different dimensions.
Verify the exact ranges of your source data and your sorting criteria column, and ensure you understand exactly which column contains the blank string results you wish to exclude.
Apply Matching FILTER Conditions inside SORTBY
Filter both the data array and the sort-by array using the exact same criteria to prevent dimension mismatch errors.
The SORTBY function requires all provided arrays (the data array and any sorting arrays) to have the exact same number of rows. If you use the FILTER function on the main data array to remove blanks but fail to apply the exact same filter to the sort-by array, Excel will return an error because the arrays are no longer the same size.
Determine your main data array (e.g., B2:F113), the specific array you are sorting by (e.g., F2:F113), and the column containing the blank values you want to filter out (e.g., B2:B113).
Write the FILTER function for your main data to exclude blanks. Use the not-equal-to operator (<>""). For example: FILTER(B2:F113, B2:B113<>"").
Write an identical FILTER function for the column you want to sort by. Use the same logical condition: FILTER(F2:F113, B2:B113<>"").
Wrap both filtered arrays in the SORTBY function, adding the sort order at the end (-1 for descending, 1 for ascending). The final formula will look like: =SORTBY(FILTER(B2:F113,B2:B113<>""),FILTER(F2:F113,B2:B113<>""),-1).
Easily Manage Dynamic Arrays with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like SORTBY and FILTER, allowing you to easily organize your data and eliminate blank rows without formatting errors.
- 1. Open your workbook: Launch WPS Office and open your Spreadsheet document containing the data with blank formula results.
- 2. Select the destination cell: Click on the empty cell where you want the sorted, non-blank data to begin spilling.
- 3. Enter the nested formula: Type your combined formula, ensuring both the data array and the sort array are wrapped in identical FILTER functions.
- 4. Execute the function: Press Enter to dynamically spill the properly sorted results into the worksheet.
Frequently Asked Questions
Why does SORTBY return a VALUE or mismatch error when used with FILTER?
This happens when the arrays inside the SORTBY function have different dimensions. If you apply a FILTER to your source data to remove rows, you must apply the exact same FILTER condition to your sort-by array so they maintain the same row count.
Can I sort by multiple columns while excluding blanks?
Yes. You can add additional sort arrays to the SORTBY function. However, you must remember to wrap every additional sort array in its own FILTER function using the exact same criteria as the original data array.
What does the <>"" operator mean in the FILTER function?
The <>"" operator stands for "is not equal to empty text". It tells the FILTER function to evaluate the specified range and exclude any rows that are completely blank or contain formulas that return an empty string.
