Automatically Sort Excel Rows by Status and Name
Question details
The user wants rows to appear in a custom status order and then alphabetically whenever a status drop-down changes.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses data-validation drop-downs to assign YES, WAITING, NO, or ENDED to each row.
- Observed behavior
- The desired view updates automatically by status and then by name when an entry changes.
Check that your Excel version supports SORTBY, such as Microsoft 365 or Excel 2021 or later. Identify the Name and Status columns and confirm that the drop-down values match YES, WAITING, NO, and ENDED.
Create an automatically sorted output view with SORTBY
Use a helper column and a separate output sheet to update the order automatically without moving rows in the data-entry table.
The output is a formula-generated view, not a physical rearrangement of the input rows. Make all status and name changes in the input table.
Select the data on the input sheet and choose Insert > Table. Include headers named Name and Status, then use the Table Design tab to name the table InputTable.
Add a StatusOrder column to InputTable. In its first data cell, enter =IF([@Status]="",999,IFERROR(MATCH([@Status],{"YES","WAITING","NO","ENDED"},0),99)). Excel should fill the formula down the table.
On an output sheet, copy the input headers into row 1. In cell A2, enter =SORTBY(InputTable,InputTable[StatusOrder],1,InputTable[Name],1). Leave enough empty cells below and to the right for the results to spill.
Return to InputTable and select a different value from a Status drop-down. Check the output sheet: rows should appear in YES, WAITING, NO, ENDED order, with names sorted alphabetically within each status.

Create a sorted status view in WPS Spreadsheets
Use a status-order helper column and SORTBY in WPS Spreadsheets to display rows by status and name. Check that your installed version supports SORTBY before using this approach.
- 1. Open the workbook: Open the XLSX file in WPS Spreadsheets. For this example, use an Input sheet with names in column A, statuses in column B, and other data in column C.
- 2. Add a helper column: In Input!D1, enter StatusOrder. In D2, enter =IF(B2="",999,IFERROR(MATCH(B2,{"YES","WAITING","NO","ENDED"},0),99)) and fill the formula down through D100.
- 3. Build the output view: On another sheet, copy the headers into row 1. In A2, enter =SORTBY(Input!A2:D100,Input!D2:D100,1,Input!A2:A100,1), leaving the output area empty.
- 4. Change a status: On the Input sheet, choose a new value from a status drop-down and check the output sheet. Expand the formula ranges and fill the helper column farther down if your data extends beyond row 100.

Frequently Asked Questions
Will the SORTBY formula physically move my input rows?
No. SORTBY creates a separate output view. Edit names and status drop-downs in the input table; the view will update from those changes.
How do I change the status order?
Edit the list inside the helper column's MATCH formula. For example, put "WAITING" before "YES" in the array to display WAITING rows first.
Why does Excel show a #SPILL! error?
The SORTBY results need an empty area below and to the right of the formula cell. Clear or move any content blocking that area, then check the formula again.
What if my version of Excel does not support SORTBY?
Use Data > Sort to sort the input table manually by StatusOrder and then Name. A physical automatic sort requires another approach, such as VBA with a macro-enabled workbook and carefully handled change events.




