How to Return Sorted Names Without Blank Rows in Excel
Question details
The user needs to extract a list of employee names from a source table where an adjacent cell contains a specific value, sort the results alphabetically, remove any blank rows, and place the output on a different worksheet.
- Product
- Excel 365
- Device & OS
- not provided
- Scenario
- Creating an automated reporting sheet (like 'Training Needed') that pulls specific, sorted data from a master employee information sheet without leaving empty gaps.
- Observed behavior
- The user requires a formula-based approach to retrieve and organize the data on a separate sheet automatically, rather than modifying or filtering the original table display.
Ensure you are using a version of Excel that supports dynamic array formulas, such as Microsoft 365 or Excel 2021, as the SORT and FILTER functions are required for this method.
Use SORT and FILTER Dynamic Array Functions
Combine the SORT and FILTER functions to dynamically extract, sort, and display names without blank rows on a new sheet.
The most efficient way to achieve this in modern Excel is by nesting the FILTER function inside the SORT function. The FILTER function pulls only the rows that meet your criteria (and ignores blanks), while the SORT function automatically alphabetizes the resulting array.
Click on the worksheet tab where you want the results to appear (e.g., 'Training Needed') and select the top cell where the list should start.
Begin by typing the FILTER function to specify your source data and criteria. For example: FILTER('CHANGE EE INFORMATION'!B5:B47, 'CHANGE EE INFORMATION'!G5:G47="X", "")
Enclose the FILTER function within a SORT function to alphabetize the output. The complete formula should look like: =SORT(FILTER('CHANGE EE INFORMATION'!B5:B47, 'CHANGE EE INFORMATION'!G5:G47="X", ""))
Press Enter. The results will dynamically "spill" down the column, showing all matching employee names sorted alphabetically with no blank rows.
Use Power Query to Filter and Sort Data
If you are using an older version of Excel that does not support dynamic arrays, Power Query is a robust alternative to process and load the data.
Effortlessly Manage and Filter Data with WPS Spreadsheet
WPS Office provides powerful data management tools, including advanced formulas and seamless dynamic array support, to help you organize reports across multiple worksheets easily.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the workbook containing your employee data.
- 2. Apply the Dynamic Formula: Navigate to your target sheet and enter the =SORT(FILTER(...)) formula exactly as you would in Excel.
- 3. Instantly Generate the Report: Press Enter to spill the results automatically, creating a clean, sorted list without blank rows.
- 4. Utilize Built-in Data Tools: Alternatively, explore the 'Data' tab in WPS Office to use advanced filters and sorting tools if you prefer a no-formula approach.
Frequently Asked Questions
Why is my FILTER function returning a #CALC! error?
The #CALC! error typically occurs if the FILTER function evaluates to an empty array (meaning no rows currently match your "X" criteria). You can handle this gracefully by adding a "Not Found" argument at the end of your FILTER formula, like this: =SORT(FILTER(Range, Criteria, "No Training Needed")).
Do these dynamic array formulas work in older versions of Excel?
No, the SORT and FILTER functions are available only in Microsoft 365, Excel 2021, and newer versions. For older versions like Excel 2016 or 2019, you must use Power Query or complex INDEX/MATCH array combinations.
Will the extracted list update automatically if the source data changes?
Yes, dynamic array formulas automatically recalculate. Whenever you add or remove an "X" in the source data sheet, the spilled results on your target worksheet will update and re-sort instantly.
Can I pull multiple columns using the FILTER function?
Yes. If you expand the first argument of your FILTER function to include multiple columns (e.g., A5:C47 instead of just B5:B47), the formula will spill all those columns into your new sheet, while still filtering out the blank rows.
