Lookup & Reference

SORT Function in Excel

Sorts the contents of a range or array in ascending or descending order.

Syntax

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

Arguments

  • array (required): The range or array to sort
  • sort_index (optional): Column or row number to sort by (default 1)
  • sort_order (optional): 1=ascending (default), -1=descending
  • by_col (optional): FALSE=sort by row (default), TRUE=sort by column

Examples

  • =SORT(A2:B10) - Sort by first column ascending - Result: Sorted array
  • =SORT(A2:C10, 3, -1) - Sort by column 3 descending - Result: Sorted array

SORT best practices

  • Use SORT with dynamic arrays to keep output ordered automatically.
  • Combine SORT with UNIQUE when creating clean lists from raw data.

Excel SORT function documentation

  • SORT returns a sorted copy of an array or range — original data stays unchanged.
  • Syntax: =SORT(array, [sort_index], [sort_order], [by_col]). Dynamic spill output.
  • Combine with [UNIQUE](/functions/unique/) and [FILTER](/functions/filter/) for modern report stacks.
  • Requires Microsoft 365 or Excel 2021+ for dynamic array behavior.

SORT syntax and multi-column sort

  • sort_index: which column to sort by (1 = first column in array).
  • sort_order: 1 ascending (default), -1 descending.
  • by_col: FALSE sort by rows (default); TRUE sort columns horizontally.
  • Multi-column: =SORT(A2:C100, {2,1}, {1,-1}) sorts by col 2 asc then col 1 desc.

Step-by-step: sorted unique product list

  • Step 1 — Product IDs in column A with duplicates.
  • Step 2 — =SORT(UNIQUE(A2:A500)).
  • Step 3 — Clear rows below formula for spill.
  • Step 4 — Wrap FILTER first if list must exclude blanks: =SORT(FILTER(A:A, A:A<>"")).
  • Step 5 — Compare with Sort dialog on copy — SORT formula keeps source intact.

SORT vs SORTBY vs table Sort button

  • SORT — single function; sort_index column number.
  • SORTBY — sort by separate helper columns not in output array.
  • Table Sort — manual UI; changes view unless you copy values elsewhere.
  • Stack: =SORT(FILTER(data, condition), 3, -1) for filtered top-N style lists.

Worked examples to copy

  • A-Z names: =SORT(B2:B200, 1, 1).
  • Highest revenue first: =SORT(A2:C1000, 3, -1).
  • Sort unique: =SORT(UNIQUE(region_column)).
  • Horizontal: =SORT(month_row, 1, 1, TRUE) for row of headers.

People also ask

  • Does SORT change original data? — No. Returns new spilled array.
  • SORT #SPILL!? — Blocked cells in spill range — clear or move formula.
  • SORT with headers? — Exclude header row from array or sort will mix headers into data.
  • SORT vs pivot sort? — SORT formula is live; pivot is interactive analytics.

Common errors

  • sort_index must be within array bounds

Use cases

  • Dynamic sorting
  • Ranked lists
  • Organized reports

Frequently asked questions

  • How do I sort by multiple columns with SORT? SORT only sorts by one column. For multi-column sorting, use SORTBY instead: =SORTBY(A2:D10, B2:B10, 1, C2:C10, -1) sorts by column B ascending first, then by column C descending. SORTBY is more flexible for complex sorting needs.
  • Can I sort horizontally instead of vertically? Yes! Set the by_col argument to TRUE: =SORT(A1:E1, 1, 1, TRUE) sorts a horizontal range. This is useful for sorting column headers or horizontal data layouts.
  • How do I create a dynamic sorted list that updates automatically? Simply reference your data range in SORT: =SORT(Table1[#All], 2, -1). As you add or modify data in Table1, the sorted output updates automatically. Combine with FILTER for filtered and sorted dynamic lists.
  • Why isn't SORT sorting numbers correctly? Numbers stored as text sort alphabetically (1, 10, 2 instead of 1, 2, 10). Convert to numbers first: =SORT(VALUE(A2:A10)*1) or fix the source data. Check for leading spaces or apostrophes that force text format.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use SORT

  • Dynamic sorting — common Lookup & Reference scenario for SORT.
  • Ranked lists — common Lookup & Reference scenario for SORT.
  • Organized reports — common Lookup & Reference scenario for SORT.

SORT in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • SORT syntax: =SORT(array, [sort_index], [sort_order], [by_col])
  • array (required): The range or array to sort
  • sort_index (optional): Column or row number to sort by (default 1)
  • sort_order (optional): 1=ascending (default), -1=descending
  • by_col (optional): FALSE=sort by row (default), TRUE=sort by column
  • Confirm SORT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm SORT arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • SORTBY (/functions/sortby/): Sorts the contents of a range or array based on the values in a corresponding range or array.
  • FILTER (/functions/filter/): Filters a range of data based on criteria you define. Returns an array of values that meet the specified conditions.
  • UNIQUE (/functions/unique/): Returns a list of unique values from a range or array.

Common problems and fixes

  • How to Fix #SPILL! in FILTER (/problems/filter-spill-error/): Clear the spill range, unmerge cells, and make sure the FILTER output has enough empty space to expand.

Errors to watch for

  • sort_index must be within array bounds — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter SORT on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

SORT worked examples to copy

  • =SORT(A2:B10) — Sort by first column ascending. Expected result: Sorted array.
  • =SORT(A2:C10, 3, -1) — Sort by column 3 descending. Expected result: Sorted array.

SORT reference summary for crawlers and offline review

  • SORT belongs to the Lookup & Reference category in Excel. Sorts the contents of a range or array in ascending or descending order.
  • Full syntax: =SORT(array, [sort_index], [sort_order], [by_col]). Open /functions/sort/ for parameters, FAQs, and related pages.
  • Common mistakes: sort_index must be within array bounds
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.