Sorting only one column is one of the fastest ways to damage a spreadsheet. Names become separated from departments, dates no longer match sales figures, and a report that looked tidy becomes unreliable.

An Excel sort formula avoids that problem because it creates a separate, live sorted view instead of rearranging the original data. The key is choosing the right function: SORT when the sort key is inside the data you return, and SORTBY when the key is separate, calculated, custom, or part of a multi-level sort.

Choose SORT when the sort key is inside the data; choose SORTBY when it is separate

Use SORT when the column or row you want to sort by is included in the array you are returning. For example, if you return columns A through D and want to sort by column B of that same selection, SORT is the straightforward option.

Use SORTBY when your sort key sits in another range, comes from a calculation, follows a custom order, or requires more than one criterion. Both functions return a dynamic spilled result: Excel fills adjacent cells with the sorted output while leaving the source data untouched.

That distinction matters because preserving record integrity should be the priority. When a Name, Department, Start Date, and Sales amount belong to one employee record, sort the entire record range rather than sorting names or sales on their own.

Check dynamic-array support and prepare a clean source range

SORT and SORTBY require a version of Excel that supports dynamic arrays. They are available in Microsoft 365, Excel for the web, and newer perpetual Excel releases. If Excel returns #NAME?, first check whether your version recognizes these functions.

Start with a contiguous source range, such as A2:D20. If you place the formula beside the data, keep the header row outside the formula range so the output contains records only. You can also use an Excel Table, which is usually the better choice for data that grows regularly.

Before entering a formula, make sure the destination area is clear. A spilled result needs enough empty cells below and to the right of the formula cell to display every returned record.

Avoid #SPILL!, blank rows, and mismatched range sizes

A #SPILL! error means Excel cannot place the output where it needs to go. Clear values from the intended spill range and remove merged cells, which can also block dynamic arrays.

For SORTBY, every by_array must match the returned array's height for vertical sorting or width for horizontal sorting. For example, if you return A2:D20, a valid vertical sort key could be B2:B20. Using B2:B19 creates incompatible dimensions.

Blank rows can also produce results that look odd, especially if blanks appear in the middle of the source. If you do not want blank records in the output, filter them out before sorting rather than expecting SORT to clean the data automatically.

Sort one column alphabetically or numerically with SORT

The basic SORT syntax is:

=SORT(array, [sort_index], [sort_order], [by_col])

To arrange a list of names in A2:A20 alphabetically from A to Z, enter:

=SORT(A2:A20)

To arrange numbers in B2:B20 from low to high, use:

=SORT(B2:B20,1,1)

The sort_index tells Excel which column or row within the selected array to use. In a single-column list, that value is 1. The sort_order is 1 for ascending order and -1 for descending order. Ordinary numeric values do not need to be converted before sorting; Excel sorts genuine numbers correctly.

Sort a list from Z to A or largest to smallest

For reverse alphabetical order, use:

=SORT(A2:A20,1,-1)

For numbers from largest to smallest, use:

=SORT(B2:B20,1,-1)

Unlike using Data > Sort, these formulas create an automatically refreshed copy. If someone changes a name or number in the source range, the sorted results update without running the sort command again.

Sort a complete table without separating names, dates, and values

When columns describe the same records, return the complete table. Suppose A2:D20 contains Name, Department, Start Date, and Sales. To sort the table by Department, which is the second column within that selected range, use:

=SORT(A2:D20,2,1)

Every row moves as one unit. A sales value remains matched with the same employee, department, and start date.

A common mistake is treating sort_index as a worksheet column number. It is not. If your selected array is C2:F20 and you want to sort by the data in worksheet column E, the sort index is 3 because E is the third column inside C:F.

To sort the same Name, Department, Start Date, and Sales table by Name, use:

=SORT(A2:D20,1,1)

Sort dates correctly, including mm/dd/yyyy displays

Excel stores real dates as serial numbers, so a true date in mm/dd/yyyy format sorts correctly with a normal formula. For more help creating and fixing date values, see this Excel Date Formula guide. To sort the table by Start Date in the third selected column, use:

=SORT(A2:D20,3,1)

The display format does not determine the sort order. The real problem is imported dates stored as text. If dates appear left-aligned, resist date formatting changes, or sort incorrectly, convert them to actual Excel dates before sorting. If you need to control how dates or numbers display as text, see the Excel TEXT formula guide.

Use SORTBY when the sort order comes from another range

SORTBY uses a separate array to determine order. Its syntax is:

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

For example, suppose A2:B10 contains a visible product list and C2:C10 contains a companion score. To return only columns A and B, sorted by score from highest to lowest, use:

=SORTBY(A2:B10,C2:C10,-1)

This is cleaner than expanding the visible output to include the score column just so SORT can access it. The helper data can remain in the source sheet without appearing in the report.

Sort by a calculated key without adding a helper column

SORTBY can sort by a calculation that produces one result for every source row. If columns C and D contain Budget and Actual values, this formula ranks records by the largest positive variance:

=SORTBY(A2:D20,D2:D20-C2:C20,-1)

The returned table remains A through D, but Excel uses Actual minus Budget to set the order. If the formula behaves unexpectedly, test the calculation in an empty column first. Any error in the calculated sort key can prevent SORTBY from producing a reliable result.

Sort by multiple criteria with SORTBY

Multi-criteria sorting is where SORTBY becomes the better default. To sort a Name, Department, Start Date, and Sales table first by Department from A to Z, then by Sales from highest to lowest within each department, use:

=SORTBY(A2:D20,B2:B20,1,D2:D20,-1)

Excel applies the criteria in sequence. Department creates the main groups, and Sales resolves the order inside each group. It does not globally sort Sales first.

SORT can sort by one indexed column or row at a time. While there are workarounds, SORTBY is clearer, easier to audit, and less error-prone for an Excel sort formula with multiple criteria.

Create a custom sort order with SORTBY

Alphabetical order is often wrong for operational labels. A priority field of High, Medium, and Low should usually appear in that order, not High, Low, Medium. Fiscal periods, workflow stages, and preferred department sequences have the same issue.

XMATCH can translate labels into positions for SORTBY:

=SORTBY(A2:C20,XMATCH(B2:B20,{"High","Medium","Low"}),1)

Here, the values in B2:B20 are matched against the custom order. High becomes position 1, Medium becomes 2, and Low becomes 3.

For a short fixed list, hard-coding the order is practical. For longer or frequently changed lists, place the desired order in worksheet cells and use that range in XMATCH instead. That lets someone update the priority sequence without editing a long formula.

Sort horizontally with SORT

SORT can also arrange values left to right. Set the fourth argument, by_col, to TRUE:

=SORT(B2:G2,1,1,TRUE)

This sorts the values across row 2 from left to right. The sort index now refers to a row within the selected horizontal array rather than a column.

If labels in row 2 need to move with values in row 3, select both rows and sort by the second row:

=SORT(B2:G3,2,-1,TRUE)

Do not select only the values row when the headers or related rows must stay aligned. The same record-integrity rule applies horizontally: sort the full block that must move together.

Make an automatically sorted view that stays useful as data grows

A spilled SORT or SORTBY formula updates when source values change. However, a fixed range such as A2:D20 does not automatically include a new row added at row 21.

For growing datasets, convert the source into an Excel Table and use its table name in the formula. If your table is named Table1, a live view sorted by its second column can use:

=SORT(Table1,2,1)

Replace Table1 with your own table name. When you add a new table row, the formula includes it automatically.

A formula-generated sorted view is different from physically reordering your source. Use the formula when you need a live report, dashboard section, or alternate view. Use Data > Sort when the original dataset itself must be permanently reordered for the workflow.

Combine FILTER and SORT for a live filtered list

FILTER and SORT work well together when you need a focused report. This formula returns only records marked Open in column C, then sorts the result by the fourth selected column in descending order:

=SORT(FILTER(A2:D100,C2:C100="Open"),4,-1)

FILTER runs first, so the sort applies only to matching records. This pattern is useful for live lists by status, owner, region, or any other field you need to isolate before ordering.

Fix common SORT and SORTBY formula problems

Most formula errors come from a small set of setup problems:

• #NAME?: Your Excel version may not support dynamic-array functions, or the function name is misspelled.
• #SPILL!: A value, formula, or merged cell is blocking the output area.
• #VALUE!: The sort index is invalid, or a SORTBY sort key has a different height or width from the returned array.
• Unexpected order: Numbers or dates may be stored as text rather than as true numeric or date values.

When troubleshooting, first confirm the source range. Next, test the sort key by itself, verify whether you need 1 or -1 for the intended direction, and inspect the area where the result needs to spill.

Also remember that SORT and SORTBY do not treat headers specially. If you include a header row in the array, Excel sorts it like any other row. Keep headers outside the source array for most report layouts, then place the formula below a separate output header row.

Build a reliable live sorted view with the right Excel formula

Use SORT when your sort key is a column or row inside the returned range. Use SORTBY when the key is separate, calculated, based on a custom order, or needs multiple criteria. In either case, return the whole record range whenever related values belong together.

That one habit prevents the most damaging spreadsheet sorting mistake: separating data that should remain on the same row. For help turning a plain-English sorting requirement into an Excel or Google Sheets formula, try FormulaBerry.