You can sort the list alphabetically, numerically, or chronologically by up to three fields. For each column, you can specify ascending and descending sort order.
Ascending sort order:
The numbers will be sorted in order from smallest negative to largest positive number.
Date and time values will be sorted from earliest to latest value.
The text will be sorted alphabetically. In this case, the numeric values specified as text will be located first.
When sorting Boolean values in ascending order, the value FALSE will be displayed first, followed by the value TRUE.
The error values will be sorted in the order in which they were encountered (from a sorting perspective, they are all equal).
Empty cells will appear at the end of the sorted list.
Descending sort order:
In the case of a descending sort order, Excel sorts the records in the reverse order described above. Only empty cells will still be shown at the end of the list.
Custom sort order:
Instead of ascending and descending sort order, you can also use a custom sort order determined by a specified custom list.
Sorting a list
To sort a list, place the cell pointer inside the list and execute the Data – Sort command.
Excel automatically selects the list and displays the Sort Range dialog box, where you can specify sorting options.
You can set sorting by up to three list fields and specify a different sort order for each field.
Excel automatically recognizes field names if the format of the cells containing the names is different from the format of the cells containing the data.
Sort Range dialog box.
If the range selection performed by the program is not entirely correct, set the switch at the bottom of the dialog box to the desired position (Identify fields by “labels (first row of the range)” or “sheet column designations”).
The range and sort options you specify in the Sort dialog box will be saved and displayed in the dialog box the next time you open it.
You can also choose to sort range columns instead of rows. To do this, click the “Options” button in the “Sorting Range” dialog box and in the “Sorting Options” dialog box, in the “Sort” group, set the switch to the “Range Columns” position.
26. Filtering data in Excel.
Filtering data in the list allows you to display only those records that meet specified conditions.
Autofilter
When applying an autofilter, the field names will be supplemented with drop-down lists from which you can select the desired field value or set a custom criterion.
Inserting an AutoFilter
1. Place the cell pointer inside the list.
2. In the Data – Filter submenu, select the “Autofilter” command. Arrow buttons will appear next to the field names, which you can click to open the list.
3. Open the list for the field whose value you want to use as a filter (selection criterion). The list will show the values of the cells of the selected field.
4. Select the desired item from the list. Only those records that match the specified filter will be displayed on the screen.
5. If necessary, select the required element from the list of another field. Only those records that match all specified filtering conditions will be displayed on the screen (the conditions of individual fields are combined using the logical operation “AND”).
Buttons for opening autofilters for fields for which filtering conditions are specified are colored blue.
If you selected one or more columns before executing the AutoFilter command, drop-down lists will be added only to the corresponding fields.
To display all list entries on the screen again, execute the “Display all” command from the Data – Filter submenu.
You can remove the filtering criterion for an individual field by selecting the “All” item in the autofilter list for this field.
To deactivate the autofilter function (remove drop-down lists), select the command Data – Filter – Autofilter again.
Applying a custom autofilter
Using a custom autofilter, you can connect individual record selection conditions using logical operators.
Insert an autofilter into the list by selecting the command Data – Filter – Autofilter.
Open the autofilter list for the desired field and select the element (Condition) in it.
In the “Custom AutoFilter” dialog box that opens (Fig. 6.3.27.), specify the first criterion.
Select a logical operator that combines the first and second criteria.
“Custom AutoFilter” dialog box.
You can set one or two criteria for an individual field in a custom autofilter. In the latter case, they can be combined with the logical operator “AND” or “OR”.
Set the second criterion.
Click the “OK” button. Excel will filter the records based on the criteria you specify.
Advanced filter
To set complex conditions for filtering list data, Excel provides a so-called advanced filter to help the user.
Range of criteria
The criteria can be set in any free space on the worksheet. In the criteria range you can enter and combine two types of criteria:
Simple criteria: the program will compare the contents of the fields with the specified criterion (similar to using an autofilter).
Calculated criteria: In this case, you can also specify the values returned by the formula that are not listed.
When specifying simple criteria, you must first specify field names in the criteria range, and you can enter only those field names for which you are specifying criteria.
The line(s) immediately below the field names line is where you specify the criteria. Excel is not case sensitive when specifying criteria. Can you use wildcards when specifying criteria? And *.
All criteria specified on one line must be executed simultaneously (corresponds to the logical operator “AND”). To specify the connection of criteria using the “OR” operator, specify the criteria on different lines.
Applying an advanced filter
Once you have created a criteria range, you can run the advanced filter and filter the list data.
1. Place the cell pointer inside the list. In this case, Excel will automatically recognize the list range and present a link to the range in the dialog box.
2. Execute the command Data – Filter – Advanced filter. Place the input cursor in the “Condition Range” field and highlight the corresponding range in the worksheet.
3. Close the dialog box by clicking the “OK” button. The screen will now display records that meet the specified criteria.
You can only apply one advanced filter per worksheet.
If you do not want duplicate records to be displayed as a result of applying the advanced filter, select the Unique records only check box in the Advanced Filter dialog box.
To set the display of all list entries in the worksheet after filtering, run the command Data – Filter – Display All.
Working with tables in many cases requires analyzing the data they contain. Excel provides powerful tools for processing information, the ability to build statistical reports and select data by condition. But sometimes it’s enough just to sort the elements to solve the problem.
Ordering numbers
Sorting numbers in ascending or descending order is a common task. It helps to build numerical parameters - the number of goods and customers, the amount of sales and income, and present information in a more visual form. Let's sort this data in Excel.
For example, a travel company file table stores information about the cost of staying at a hotel. The price depends on the type of room and meals offered. The tourist is interested in finding the most economical accommodation, so he needs to sort the data by ascending price.
To do this, select the table column “Cost, rub.” and go to the “Home” tab. In the “Editing” block there is a group of “Sorting and Filter” tools. Select the “Sort in ascending order” item from the menu.
The program, having detected the presence of related columns, clarifies whether it is necessary to sort in Excel only by the selected field, or to expand the range.
We select “Expand”, since we are interested in ordering the complete records of the table, and click “Sort”. Entries are arranged in ascending order of cost of living.
This operation is even easier if the table is formatted by style. In this case, you need to call the context menu of the column by clicking on the drop-down list in the column header. The menu contains similar commands that you can use to sort in Excel.
To ensure that the numbers are sorted correctly, pay attention to the correct format of the data in the column. If the values are entered into the table as text rather than numbers, the alignment will not occur in the expected order. The table shows an example of sorting the numbers 10, 11, 100, 15, 110, 132 depending on the data format.
Arranging text
In Excel, sorting columns that contain text is the same as sorting numbers. Let's execute this command in the column of the table of prices for rooms in the hotel “Food” that we already know. Select a column by clicking on its header and open the “Editing” toolbar of the “Home” tab. Now in the drop-down menu, instead of sorting commands in ascending and descending order, there are sort commands from A to Z and vice versa.
We construct cells from A to Z, again indicating the expansion of the operation range.
When ordering string data, make sure that the values do not contain leading spaces, as this will disrupt the desired order of the cells. This issue occurs when information is uploaded to a file from another source: a database, a third-party application, or another file.
For formatted tables, sorting in Excel is even easier. When you go to the context menu of the column header, you will also see commands for building cells in order from A to Z and vice versa.
Ordering dates
All of the above operations that apply to numbers and string information are also used to construct dates and times. The only difference is in the name of the command; for this type of data it is called “Sort from old to new” and vice versa. An example is shown below.
The same rule applies for dates as for numbers: to ensure the ordering is correct, make sure that the information you are sorting is presented in date format, and not in text format.
Custom Lists
But it is not always possible to build the required list using standard tools; there may be a need to sort in Excel by fields that are not suitable for any of the methods described above. For example, in a weekly to-do list, we want to organize tasks by day of the week.
To perform this sorting, the program provides the ability to use custom lists. MS Excel has a number of preset lists, one of which is a list of days of the week. To view them, go to the Data tab and find the Excel sort and filter toolbar. The “Sort” button opens a window for performing operations on several columns.
In the “Order” field, select the “Custom List” element. Select the days of the week from the list and click OK.
Organize by color and icons
Another convenient option for arranging cell elements in order is the ability to sort them by color. In this case, both the fill color and the text font color can be used. In the to-do table, we denote work tasks in orange, family matters in blue, and holidays in beige-yellow. And let's arrange them by type.
To do this, go to custom sorting. In addition to the method described above, you can use the menu of the “Editing” panel of the “Home” tab. Select the “Custom Sorting” element from the list of “Sort and Filter” commands.
Select the “Task” field, the “Cell Color” type and set the top color to orange. To ensure that household chores are at the bottom of the list, we add another level and for the same parameters we set the blue color at the bottom.
This method allows you to organize table elements by icons. For example, we can mark individual tasks with an exclamation mark as important and display them at the top of the list.
Above we described how to sort data of different types in Excel using the color of cells and text font and marks in rows. We hope this information will help you in working with the program.
Good afternoon
Now I want to talk about one of the frequently used features of Excel, which is sorting data in Excel. Every user has repeatedly faced the need to put their data in proper order; all information should be clearly structured, understandable and convenient. The chaos of data is very difficult to navigate, which can lead to errors and inaccuracies, and this is often fraught with bad consequences.
To avoid these problems, Excel has a very cool feature called “Sorting” and this procedure can be performed in 4 ways:
- Sorting from A to Z;
- Sorting from Z to A;
- Sorting by color;
- Sorting is case sensitive.
It is possible to organize text and numeric data according to these criteria, both in ascending and descending order of value. 
But if you want to get correct data, you need to know several important points about correct sorting:
- When sorting data, be sure to make sure that the filter is applied to all columns of the table. In this case, dynamic sorting will occur, which will sort all the values in the table by criterion and display them correctly. If sorting is applied to only one column, the table will be torn and the data will be lost;
- Worth it, very good to remember! It is impossible to cancel sorting! If you produced during sorting, then you have 2 options. First, close the file without saving, but there is a high risk of losing other changes made or data entered. Secondly, immediately execute the “Undo last action” command on the Quick Access Toolbar or use CTRL+Z. I RECOMMEND! If you just need to return the value to the original values before sorting, create an additional column and indicate in it (you can return the old sorting option by sorting by this column again);
- Rows that were hidden will not be moved during sorting. Therefore, before performing proper sorting, you need to ;
You can sort data in Excel using the control panel by clicking on the “Data” tab, the “Sorting” button, a dialog box will become available in which you can configure the sorting. 
Although this window duplicates almost all the functionality of the built-in sorting in, there is a slight difference; you will have another opportunity to sort your data in a case-sensitive manner. 
In cases where changes occurred in the table, data was deleted, new ones were added, or replaced with others, that is, it is possible to re-sort or apply a filter to your data. On the “Data” tab in the “Sorting and Filter” block, you need to click the “Repeat” button and all your new data will be sorted correctly. 
That’s all I have, I told and showed how to sort correctly and what to pay attention to for sorting accuracy. If you have anything to add, I look forward to your comments or remarks.
Please like if the article was useful to you and interesting!
See you again!
A woman can make any billionaire man a millionaire.
Charlie Chaplin
Sorting in Excel is a built-in function with which the user can arrange data in columns on a sheet in a convenient order for subsequent analysis.
You can sort information alphabetically, by ascending or descending values, by date or by icon, by text or cell color. This is exactly what this article will discuss.
Numbers
Everything is quite simple here. Let's take the following table as an example. Let's sort the data by column C. To do this, select it and on the “Home” tab click on the button "Sorting and Filter". In the next menu, select or "...from minimum to maximum", or "...from maximum to minimum". Let's choose the second option.
Now we have the data in C arranged in descending order.
I have column C located between two others, which are filled with data. In this case, Excel considers the selected column to be part of the table (and counts correctly). As a result, the following message appeared. Since I need to sort specifically for the Class, I highlight the item with a marker "...within the specified allocation" and click "Sort".
Alphabetically
It is done according to the same principle as described above. Select the desired range and press the button "Sorting and Filter". The items in the drop-down menu have changed. Choose either from “A to Z” or from “Z to A”.
The list of names in the example is sorted alphabetically.
By date
To sort dates in Excel, first pay attention to what format is set for the cells in which they are written. Select them and on the “Home” tab look at the “Number” group. The best format is “Date”, short or long, or “(all formats)” - the date can be written in various ways: DD.MM.YYYY, DD.MMM, MMM.YY.
This point is very important, because otherwise the dates may be sorted simply by ascending first two numbers, or by month in alphabetical order.
After that, select the desired range of cells and click on the button "Sorting and Filter". From the menu you can select or "from old to new", or "from new to old".
By cell or text color
This method can be used when there is text in cells in an Excel table or the cells themselves are painted in different colors. For example, let's take a column of numbers painted in different colors. Let's sort it so that the numbers colored in red come first, then green and black.
Select the entire range and click on the button "Sorting and Filter" and select from the menu "Customizable...".
In the next window, uncheck the box if you selected them without the top line, which is the table header. Then we select the column by which we will sort, in the example it is “I”. In the “Sorting” section, select “Font Color” from the drop-down list. In the order section, select “red color” - “Top”. We sorted the numbers in red.
Now you need the column to contain green numbers. Click on the button "Add level". All settings are the same, just select “green color”. Click OK.
Our column is sorted as follows.
As you can see, the numbers are not in order. Let's sort the numbers in ascending order. Select the column and click "Sorting and Filter" – "Customizable...". In the window that opens, click on the button "Add level". The column remains “I”, in the next field we select by “Value”, order "Ascending". Click OK.
Now our column is sorted both by text color and in ascending order of data.
Data is sorted in the same way by cell color, only in the “Sorting” section, select “Cell Color” from the list.
Tables
If you have a table in which you need to sort by several columns at once, do the following. Select the entire range of table cells along with the header. Click on the button "Sorting and Filter" and choose "Customizable...".
Let's sort the class in ascending order, and in the same way the average score.
In the sorting window, check the box "My data contains headers". In the “Column” section, select “Class” from the list, sort by “Value”, and order "Ascending".
To do the same for the average score, click on the button "Add level". In the “Column” section, select “Average score”. Click OK.
The data in the table is sorted.
Now in the “Name” column, color the cells with boys blue and the cells with girls pink. To avoid doing this for each cell separately, read the article on how to select cells in Excel - it tells you how to select non-adjacent cells.
Let's sort this column by cell color: first there will be girls, then boys. Select the entire table again, click “Sort” - "Customizable...".
In the window that opens there are already two levels that we made earlier. These levels have priority - the first is the largest, the second is smaller, and so on. That is, if we want the data in the girls/boys table to be sorted first, then by class, and then by average score, we need to arrange the levels in that order.
Click on the button "Add level". In the “Column” section, select “Name”, sort – “Cell Color”, order – “pink”, “Top”.
Now, using the arrows, move this line to the top of the list. Click OK.
The table with sorted data looks like this.
Before you organize (sort) numbers in Excel, you need to make sure that they are all written in the required format. Otherwise, the result may be either incorrect, or the command that allows you to perform the ordering will be unavailable.
Formats that allow you to sort in ascending and descending order: general, numeric, financial, monetary.
You can check the cell format like this: right-click on the desired range and select the “Format Cells” command in the context menu that appears.
The first way to arrange numbers in ascending order in Excel
The source table contains: the employee’s full name, his position and length of service.
It is required to arrange the data according to length of service - from least to most.
To do this, you need to select a range of numbers that need to be sorted. In our case, this will be the range D3:D8.
A warning may be issued if there is data near the specified range. Among the proposed actions, you need to select “Sort within the specified selection” and click on the “Sort” button.
As a result, the data will be ordered, and the employee with the shortest work experience will be displayed in first place.
Second way to arrange numbers in ascending order in Excel
The first step will be the same as in the first method - you need to select the range of numbers that you want to sort.
Then on the toolbar in the "Home" section, click on the "Sort and Filter" button. A submenu will appear in which you need to select the “Sort from minimum to maximum” command.
This command will allow you to sort numbers in ascending order.
