Sorting allows you to arrange data in alphabetical or numerical order, either ascending or descending. Microsoft Excel can sort rows as well as columns of worksheet lists. When sorting text in a table, you can sort a single column or the entire table. In addition, it is possible to sort by multiple words or fields in one table column, as well as for a selected range of the list.
To quickly sort by the desired field, simply place the cell pointer in the required column of the list with data, go to the Data tab, and in the Sort & Filter group click either Sort Smallest to Largest or Sort Largest to Smallest. On the other hand, if you select the Sort button in the Sort & Filter group on the Data tab, a dialog box will open, where you can specify sort keys (columns or rows), the sort order, and some additional parameters.
NOTE
On the Home tab, in the Editing group, there is also a Sort & Filter button, whose commands also allow you to sort a list of data.
In MS Excel, the following sort order is used:
- Numbers (from – to +);
- Text and formulas;
- The value FALSE;
- The value TRUE;
- Error values;
- Empty values.
When using sorting, keep in mind:
- The sorting order of data in MS Excel depends on the Windows regional settings.
- If it is necessary to arrange numeric values in alphabetical order, you should either place an apostrophe before the numeric values, format numbers as text, or enter the number as a formula (for example: = »345″).
- When sorting lists containing formulas, remember that relative references in formulas may lead to incorrect results when records are moved. Therefore, it is better to use formulas with absolute references in lists.
- To return to the original list, insert an additional index field before the list, containing an increasing numeric sequence with any step (e.g., 1, 2, 3, …). By selecting a cell in this column and sorting the list in ascending order, you can restore the original order of the list.
- Custom sort order allows you to arrange data in a predefined sequence (for example, days of the week, months, etc.). To do this, use the Custom Lists window , which is called by the Custom List command in the Sort dialog box. Click the order field in the column of this window and choose the Custom List command.
- Dates and times must be entered in the proper format or with the help of date or time functions, since MS Excel uses an internal representation of these values for sorting.
- Sorting by multiple fields is set in the Sort dialog box, starting from the top level. You can change the sort level using the Move Up and Move Down buttons in that dialog box.
- MS Excel can sort not only rows but also columns, as well as a selected range in the list. Furthermore, sorting can be carried out taking into account the case of the entered characters .