Formula for tracking spreadsheet

Question

Hello,

I am looking for a formula that will move a row of a tracking spreadsheet to the bottom of the document as items are completed so that the items currently being worked stay at the top. Any and all assistance is welcome. Thank you!

Answer 1

Hi HEATHER HECKARD,

Another option is to use Power Query if you want a refreshed view where completed items automatically appear at the bottom.

• Select your tracking data, then go to Data > From Table/Range.
• In Power Query, sort the Status column so completed items are last.
• Choose Close & Load.
• Use Data > Refresh All whenever the tracking data changes.

This does not rearrange the original rows, but it provides a clean sorted output without needing VBA.

If your main goal is simply to keep completed work out of the way, rather than physically moving rows, you could use a filter to show only active items.

• Enable Data > Filter on the header row.
• Open the Status filter.
• Exclude Completed.

You can re-enable Completed whenever you need to review finished work. This keeps the original tracking sheet intact and avoids additional formulas or automation.

Hope this provides a simpler way to keep your active items easy to see.

Answer 2

A formula cannot physically relocate rows in the original tracking list, but SORTBY can create a live view that automatically places rows marked “Completed” at the bottom.

Create an automatically sorted view

1. Add a Status column to the tracker, using consistent values such as In Progress and Completed.
2. Convert the source data to an Excel table:
   • Select the tracker.
   • Press Ctrl+T.
   • Select My table has headers, then select OK.
3. On a separate worksheet, enter this formula in cell A2, replacing Table1 and Status with your table and column names:

=SORTBY(Table1,--(Table1[Status]="Completed"),1)

The comparison returns 0 for rows that are not completed and 1 for completed rows. Sorting ascending therefore displays active items first and completed items last.

SORTBY returns a dynamic array, so the results “spill” into adjacent cells and automatically resize when structured table references are used.^1^ Keep the cells below and to the right of the formula empty; otherwise, Excel cannot display the complete result.

This approach is available in Microsoft 365 and Excel 2021 or later.^2^ It creates a display view rather than moving the editable source rows, so continue entering and updating information in the original table.

Keep active items in their existing order

If the table has an ID, Created Date, or priority column, add it as a second sorting criterion. For example:

=SORTBY(
    Table1,
    --(Table1[Status]="Completed"),1,
    Table1[Created Date],1
)

This groups completed rows at the bottom, then sorts each group from oldest to newest. Change the final 1 to -1 for newest to oldest. SORTBY supports additional sort arrays, with 1 meaning ascending and -1 meaning descending.^1^

If the original rows must actually move

Use Excel’s built-in sort instead of a formula:

1. Select any cell in the tracker.
2. Select Data > Sort.
3. Under Sort by, select Status.
4. Under Sort On, select Cell Values.
5. Select an order that puts the active status before Completed.
6. Select OK.

Excel can sort an entire range or table by one or more columns, preserving each row as a unit.^3^ This is not automatic: repeat the sort after changing statuses. Automatically rearranging the original rows immediately after editing a status would require automation such as VBA rather than a worksheet formula.

---

References

1. SORTBY function | Microsoft Support
2. What's new in Excel 2021 for Windows
3. Quick start: Sort data in an Excel worksheet | Microsoft Support