Calculation Issues

Automatically Sort Excel Rows by Status and Name

Huda QurayshiHuda Qurayshi Nov 18, 2026 869 views

Question details

The user wants rows to appear in a custom status order and then alphabetically whenever a status drop-down changes.

Automatically Sort Excel Rows by Status and Name
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.
Before you start

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.

Solution 1Recommended

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.

1
Create an 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.

2
Add the custom sort order

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.

3
Create the sorted view

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.

4
Test a status change

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 an automatically sorted output view with SORTBY
Unknown and blank statuses: The helper formula places unrecognized statuses after the four listed values and blank statuses last. Change the values in the MATCH formula if you need a different status order.
Sort in WPS Spreadsheets

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. 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. 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. 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. 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.
Keep status drop-down edits separate from the sorted viewSort by a custom status order and then alphabeticallyOpen and save Microsoft Excel XLSX filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.