Sort by value

Logic of sorting depending on data type

Standard sorting in ascending or descending order is performed depending on the type of data contained in the cells:

–Text data is sorted in ascending order in alphabetical order, and in descending order in reverse order.

–Numerical data is sorted in ascending order from the minimum to the maximum value, and in descending order in reverse order.

–The date and time are sorted in ascending order from earlier values to newer ones, and in descending order in reverse order.

–Logical data is sorted in ascending order from FALSE to TRUE, in descending order in reverse order.

–Errors are sorted in ascending order by error priority: #EMPTY! → #DIV/0! → #VALUE! → #REF! → #NAME? → #NUMBER! → #N/A → #NULL, and in descending order in reverse order.

–Mixed data is sorted in ascending order as follows: numeric data (from minimum to maximum) → text data (from A to Z) → logical data (from FALSE to TRUE) → errors (by error priority, see above) → date and time (from earlier dates to later ones) → cells without values. In descending order in reverse order.

When sorting with user lists, the sort order is determined by the order of the items in the list.

Sort columns of the selected range by value

To sort the columns of the selected range by value, follow these steps:

1.Select a range of cells containing more than one row. When selecting a range from multiple columns, sorting is performed on the column of the range with the active cell.

2.Run the sort command in one of the following ways:

•On the Home tab, in the Data group, click the arrow to the right of the  Sort button and select the Sort ascending or Sort descending command from the drop-down list.

•On the Data tab, click the arrow to the right of the  Sort button and select the Sort ascending or Sort descending command from the drop-down list.

The appearance of the  Sort button depends on the last command invoked using it and may differ from the one shown here.

•Right-click any cell in the selected range and select Sort > Sort ascending / Sort descending.

3.If the Sort cells window appears with a suggestion to add adjacent cells to the range:

•Select Add the data and sort if you want to add adjacent cells to the current range, and click OK.

•Select Ignore the data if you want to apply sorting only to the selected range, and click OK.

Using the Sort ascending or Sort descending commands on the toolbar will reset the applied row sorting.

You can also sort the columns of the selected range by value using custom sorting. To do this, proceed as follows:

1.Select a range of cells containing more than one row.

2.Open the Custom sorting window in one of the following ways:

•On the Home tab, in the Data group, click the arrow to the right of the  Sort button. In the drop-down list, select Custom sorting.

•On the Data tab, click the arrow to the right of the  Sort button. In the drop-down list, select Custom sorting.

•Right-click any cell in the selected range and select the Sort > Custom sorting command.

•Press Ctrl+Shift+R (Windows, Linux) or ⌘Cmd+⇧Shift+R (macOS).

3.If the Sort cells window appears with a suggestion to add adjacent cells to the range:

•Select Ignore the data if you want to apply sorting only to the selected range, and click OK.

•Select Add the data and sort if you want to add adjacent cells to the current range, and click OK.

4.In the Custom sorting window:

•Select the Columns have titles checkbox if you want to use the values of the upper cells as column titles. Titles will not be part of the sorting.

•In the Column column, select the name of the column you want to sort by from the drop-down list.

•In the Sort column, select By value from the drop-down list.

•In the Order column, select the sorting order: Ascending / Descending or From A to Z / From Z to A.

5.At the bottom of the window, click Apply.

Sort rows of the selected range by value

You can sort the rows of the selected range by value only using custom sorting. To do this, proceed as follows:

1.Select a range of cells containing more than one column.

2.Open the Custom sorting window in one of the following ways:

•On the Home tab, in the Data group, click the arrow to the right of the  Sort button. In the drop-down list, select Custom sorting.

•On the Data tab, click the arrow to the right of the  Sort button. In the drop-down list, select Custom sorting.

The appearance of the  Sort button depends on the last command invoked using it and may differ from the one shown here.

•Right-click any cell in the selected range and select the Sort > Custom sorting command.

•Press Ctrl+Shift+R (Windows, Linux) or ⌘Cmd+⇧Shift+R (macOS).

3.If the Sort cells window appears with a suggestion to add adjacent cells to the range:

•Select Add the data and sort if you want to add adjacent cells to the current range, and click OK.

•Select Ignore the data if you want to apply sorting only to the selected range, and click OK.

4.In the Custom sorting window, click  Additional settings on the top panel of the window and select the Sort by rows checkbox.

5.Select the Rows have titles checkbox if you want to use the values in the first column of the selected range as row titles. Titles will not be part of the sorting.

6.In the Row column, select the name of the row you want to sort by from the drop-down list.

7.In the Sort column, select By value from the drop-down list.

8.In the Order column, select the sorting order: Ascending / Descending or From A to Z / From Z to A.

9.At the bottom of the window, click Apply.

Sort the range of filtering by value

The range of filtering can only be sorted by columns; sorting by rows is not available.

To sort the range of filtering by value, follow these steps:

1.Click  Sort and filter in the upper cell of the column you want to sort by.

2.In the Sort and filter window, in the Sort type drop-down list, select By value.

3.Select the sorting order using the  Ascending /  From A to Z or  Descending /  From Z to A buttons.

4.At the bottom of the window, click Apply.

You can also sort the range of filtering by value using custom sorting. To do this, proceed as follows:

1.Click Sort and filter in one of the top cells of the range.

2.In the Sort and filter window, in the Sort type drop-down list, select Custom sorting.

3.In the Custom sorting window:

•In the Column column, select the name of the column you want to sort by from the drop-down list.

•In the Sort column, select By value from the drop-down list.

•In the Order column, select the sorting order: Ascending / Descending or From A to Z / From Z to A (for text data).

4.At the bottom of the window, click Apply.

The appearance of the sort and filter button may differ from the one shown here because it depends on whether sorting has been applied before, the sort order (ascending or descending), and whether a filter has been applied:

–: No sorting or filtering applied.

–: Filter applied, no sorting applied.

–: Sorted in ascending order with no filter applied.

–: Sorted in descending order with no filter applied.

–: Sorted in ascending order with filter applied.

–: Sorted in descending order with filter applied.

Sort by value with case sensitivity

When sorting using the methods described above, the character case for text data is ignored by default. When sorting with case sensitivity, the size of the character as defined in the Unicode standard is taken into account. The comparison is performed character by character from left to right. The case of letters affects the size of the character.

To perform case-sensitive sorting when configuring sorting parameters in the Custom sorting window, click  Advanced settings on the top panel of the window and select the Match case checkbox.