Sort Excel Rows by a Calculated Key: Helper Columns vs. SORTBY

Excel's Sort dialog cannot take a formula as a sort key—it reads only the values (or colors and icons) physically stored in cells. The practical fix is to stop copying the helper formula by hand: convert your range to a Table so Excel fills the Month Cleared column automatically, or skip the column entirely and build a sorted view with SORTBY.

Why the Sort dialog ignores formulas

When you open Data > Sort, each sort level points at a column in your data. Excel evaluates nothing at that point; it simply reads what each cell contains—text, numbers, dates, cell colors, or conditional-format icons. There is no field where you can type =DATE(YEAR(Cleared), MONTH(Cleared), 1) and have the dialog use the result as a key. A formula works as a sort key only because it lives in a cell and the cell holds its result, which is exactly why the helper-column approach functions at all. The limitation is not a bug; it is how the dialog was designed.

Option 1: Keep the helper column, but let a Table fill it for you

If you reconcile your checkbook in place, this is the lowest-friction route:

1. Click anywhere in your data and choose Insert > Table (or press Ctrl+T).
2. With any cell in the table active, type a header such as Month Cleared in the first empty column.
3. In the first data row of that column, enter:

=DATE(YEAR([@Cleared]), MONTH([@Cleared]), 1)

Excel fills that formula down the entire column instantly, and every new row you add at the bottom inherits it automatically. The column maintains itself from then on—no copying, no fill handle. Because each cell holds a real date (the first of the month the transaction cleared), the Sort dialog treats it like any other key: sort by Month Cleared, then Check no., Cleared, Date, and Description. Your transactions group by month, then by transaction type, with checks ordered by number—mirroring how bank statements are organized. If the column clutters the view, right-click its header and choose Hide; sorting still works on hidden columns.

Option 2: Sort by formula with SORTBY

Excel 365 and Excel 2021 include SORTBY, which does literally what the dialog will not: sort a range by computed keys with no helper column. Place this in an empty area—ideally on a second sheet—and it spills a fully sorted copy of the data:

=SORTBY(Ledger,
   DATE(YEAR(Ledger[Cleared]), MONTH(Ledger[Cleared]), 1), 1,
   Ledger[Check no.], 1,
   Ledger[Cleared], 1,
   Ledger[Date], 1,
   Ledger[Description], 1)

Each 1 means ascending for that key; use -1 for descending. The month expression is calculated on the fly—nothing is written back into your ledger—and the spilled result re-sorts itself whenever you add or edit rows. If your data is not a Table, replace the structured references with plain ranges such as B2:B500. Two caveats apply: the spill is a read-only view, so you read from it but type into the original table, and the formula needs clear space below and to the right or it returns #SPILL!.

Which approach suits a checkbook?

• Reconciling in place: use the Table plus auto-filled column. You keep one editable sheet, sorting stays in the familiar dialog, and the helper column costs nothing once the Table maintains it.
• Producing a statement-style report: use SORTBY on a separate sheet. It reproduces the bank's grouping—everything for one month, transactions grouped by type, checks by number—without touching the ledger.

Nothing stops you from combining both: the Table keeps the ledger tidy, while the SORTBY sheet becomes your printable monthly view.

Preventing the copy-down chore next time

Whenever a sort key is derived from another column—a month, a fiscal quarter, an UPPER() version of a name—resist filling a formula row by row. Put the data in a Table first so calculated columns propagate themselves, or reach for a dynamic-array function such as SORTBY (or plain SORT for simple cases) that computes keys inside the formula itself. Both patterns keep the derived logic in exactly one place instead of scattered across hundreds of cells.

Frequently Asked Questions

Can I type a formula into the Sort dialog as a sort key?

No. Sort levels accept only column references, plus cell color and icon criteria. A formula can serve as a sort key only through its cell result, which is what the helper column provides.

Does SORTBY change my original data?

No. It spills a sorted copy into empty cells and leaves the source range untouched. Edit the original table and the copy updates itself automatically.

What if my Excel version doesn't have SORTBY?

SORTBY requires Microsoft 365 or Excel 2021 or later. On older versions, use the Table with an auto-filled helper column and sort through the dialog as before.

Why doesn't my formula fill down when I add rows?

Automatic filling is a Table feature. If your data is still a plain range, Excel will not propagate the formula. Convert the range with Insert > Table, or extend an existing calculated column by dragging the small handle at its bottom-right corner.

Can I hide the helper column and still sort by it?

Yes. Right-click the column header and choose Hide. The Sort dialog lists hidden columns too, and sorting by them works normally.

Why does SORTBY show #SPILL!?

The spilled result needs an empty cell for every row and column of the output. Anything blocking—text, another formula, a table edge—triggers the error. Move the formula or clear the obstruction.