This page isn't translated yet, so its text is in English. Function names and formulas are shown in your Excel language.
Excel function · Dynamic arrays
TRIER(SORT)
Returns a range sorted by one of its columns.
What it does
SORT returns a sorted copy of a range and spills it. The original stays as it is, so it suits leaderboards and lists that should always be in order.
Syntax
=TRIER(array; [sort_index]; [sort_order]; [by_col])Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ;
Arguments
- array
- The range to sort.
- sort_index
- Which column of the range to sort by, counting from 1. Left out, the first.
- sort_order
- 1 or left out for smallest first; -1 for largest first.
- by_col
- FALSE or left out sorts rows; TRUE sorts columns.
Example
How do you list the scores from highest to lowest?
| A | B | C | |
|---|---|---|---|
| 1 | Score | Highest first | |
| 2 | 72 | 95 | |
| 3 | 95 | 88 | |
| 4 | 64 | 72 | |
| 5 | 88 | 64 |
The formula in C2:
=TRIER(A2:A5;1;-1)The result spills into the cells below: 95; 88; 72; 64
Sort order -1 puts the largest first: 95, 88, 72, 64. The scores in column A stay where they are.
Common mistakes
- Forgetting -1 when you want the largest first. The default is smallest first.
- Sorting one column of a table on its own, which separates values from their rows. Sort the whole table and choose the column with sort_index.
In other Excel languages
Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.
| Excel language | Function name | Separator |
|---|---|---|
| English | SORT | , |
| Deutsch | SORTIEREN | ; |
| Français | TRIER | ; |
| Español (España) | ORDENAR | ; |
| Español (México) | ORDENAR | , |
| 日本語 | SORT | , |
| 한국어 | SORT | , |
| 繁體中文 | SORT | , |
| ไทย | SORT | , |
| ខ្មែរ | SORT | , |
| Tiếng Việt | SORT | ; |