Sort with user lists

In the current version of the application, user lists can only be used when sorting by values.

By default, sorting by values is performed depending on the type of data contained in the cells and the selected order (ascending or descending) as described at the beginning of the Sort by value section. The user list allows you to set your own sorting order, determined by the order of the items in the list.

Sort columns in the selected range with a user list

To sort the columns of the selected range using a user list, do the following:

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.

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 > 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 User lists from the drop-down list.

5.In the User lists window that opens, select a list from the Available lists panel or add a new one if necessary.

6.At the bottom of the User lists window, click Apply.

7.In the Custom sorting window, open the drop-down list in the Order column again. Now there are two new sorting methods with the previously selected user list. To sort in ascending order, select the item containing the first and last elements of the user list. To sort in descending order, select the last and first elements.

8.At the bottom of the Custom sorting window, click Apply.

Sort rows in the selected range with a user list

To sort the rows of the selected range using a user list, do the following:

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.In the Custom sorting window:

•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.

•In the Row column, select the name of the row 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 User lists from the drop-down list.

6.In the User lists window that opens, select a list from the Available lists panel (see column sorting above) or create a new one if necessary.

7.At the bottom of the User lists window, click Apply.

8.In the Custom sorting window, open the drop-down list in the Order column again. Now there are two new sorting methods with the previously selected user list (see column sorting above). To sort in ascending order, select the item containing the first and last elements of the user list. To sort in descending order, select the last and first elements.

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

Sort the range of filtering with a user list

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

To sort the range of filtering with a user list, do the following:

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 User lists from the drop-down list.

4.In the User lists window, select a list from the Available lists panel (see column sorting above) or create a new one if necessary.

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

6.In the Custom sorting window, open the drop-down list in the Order column again. Now there are two new sorting methods with the previously selected user list (see column sorting above). To sort in ascending order, select the item containing the first and last elements of the user list. To sort in descending order, select the last and first elements.

7.At the bottom of the Custom sorting 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.

Case-sensitive sorting

The procedure and logic for case-sensitive sorting when user lists are applied is similar to standard sorting by values.