Create a formula that moves a row to the bottom of a spreadsheet depending on status

Question

Create a formula that moves a row to the bottom of a spreadsheet depending on status

Heather Heckard 0

How do I create a formula that moves a row of data to the bottom of the spreadsheet according to status created as a data validation drop down?

Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-08-20T22:51:23.8333333+00:00

Hi,

Please share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
Heather Heckard 0 Reputation points

2026-08-21T14:25:00.76+00:00

Here is a sample of my spreadsheet. The last column, L has the status identifier. Thank you for taking a look.

3 answers

Your answer

Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-08-20T22:51:23.8333333+00:00

Hi,

Please share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
Heather Heckard 0 Reputation points

2026-08-21T14:25:00.76+00:00

Here is a sample of my spreadsheet. The last column, L has the status identifier. Thank you for taking a look.

Answer 1

Marcin Policht 106.8K MVP Volunteer Moderator

If you're using Excel, a formula by itself cannot physically move a row to the bottom of the same table when the Status drop-down changes. Formulas return values - they do not move or reorder the original cells. However, you can create a second, automatically sorted view of the data where rows with a particular Status, such as "Completed", appear at the bottom.

For example, suppose your data is in A2:F100 and the Status column is column F. You could use the following formula in another area of the worksheet:

=SORTBY(A2:F100,F2:F100,"Completed")

However, SORTBY needs a sorting value, not a text value like that. A better approach is to assign a numeric sort order based on the Status:

=SORTBY(A2:F100,--(F2:F100="Completed"),1)

This keeps all rows where Status is not "Completed" at the top and moves rows with Status = "Completed" to the bottom. The original data remains unchanged; the formula creates a dynamically sorted copy.

If you have several statuses and want a specific order, such as New, In Progress, Waiting, then Completed, you can use:

=SORTBY(A2:F100,XMATCH(F2:F100,{"New","In Progress","Waiting","Completed"}),1)

This is generally the cleanest formula-based solution because changing the Status drop-down automatically changes the displayed order.

If your actual goal is for the original row itself to physically move to the bottom of the same Excel table immediately when someone selects "Completed", you would need VBA/Office Scripts (depending on whether you're using desktop Excel or Excel for the web)

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Heather Heckard 0 Reputation points

2026-08-20T21:14:37.29+00:00

While I follow the concept of what you are saying, I am not excel knowledgeable enough to apply it to my spreadsheet. The scenario you described is exactly what I am looking to do though. I guess I am not following what column to use as the data column, as I have several, including the status.

Marcin Policht 106.8K MVP Volunteer Moderator

You do not use just one column as the "data column." You use the entire range of columns that make up each row, while telling Excel which one of those columns contains the Status.

For example, suppose your spreadsheet looks like this:

A	B	C	D	E
Name	Date	Description	Owner	Status
--------	--------	--------	--------	--------
Name	Date	Description	Owner	Status
John	8/1/26	Task 1	Bob	New
Mary	8/2/26	Task 2	Sue	Completed
Tom	8/3/26	Task 3	Bob	In Progress

Here, A:E is your data range, because those five columns belong to each row. E is the Status column.

If you want rows marked Completed to automatically appear at the bottom, you would put the following formula somewhere else in the worksheet, such as a new sheet:

=SORTBY(A2:E100,--(E2:E100="Completed"),1)

The first part, A2:E100, tells Excel what entire rows of information you want to display. The second part, E2:E100, tells Excel which column to look at to determine whether the row is Completed.

So if your spreadsheet actually has columns A through J, with Status in column H, your formula would instead be:

=SORTBY(A2:J100,--(H2:H100="Completed"),1)

In other words, the first range should include all of your columns, while the second range should contain only the Status column.

If you have more than two statuses and want them arranged in a particular order, for example New first, In Progress second, Waiting third, and Completed last, use:

=SORTBY(A2:J100,XMATCH(H2:H100,{"New","In Progress","Waiting","Completed"}),1)

That will automatically reorganize the displayed rows whenever you change the Status drop-down.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Heather Heckard 0

This did not work for me, and I don't know what I did wrong.

0 comments

Answer 3

Heather Heckard 0

I've tried this a couple of different ways, and it did not work. Not sure what I did wrong.

0 comments