Sorting data in excel is possible by value. Sorting in Excel. Advanced filters: three important benefits
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.
Sorting data is an integral part of data analysis. You may need to alphabetize names in a list, list inventory and sort it in descending order, or organize rows by color or icon. Sorting data helps you quickly visualize data and better understand it, organize and find the information you need, and ultimately make better decisions.
You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest or newest to oldest) in one or more columns. You can also sort by custom lists that you create (for example, a list consisting of Large, Medium, and Small items), or by format, including cell color and font color, and by icons.
Notes:
You can use AutoFilter or conditional formatting to find the highest and lowest values in a range of cells or table, such as the 10 highest ratings or the 5 lowest sales amounts.
For more information, see Filter data in an Excel table or range and Apply conditional formatting in Excel.
Sorting text values
Notes: Possible problems
Make sure that all data is saved in text form. If the column that you want to sort contains numbers stored in number format and numbers stored in text format, you must format all cells as either numbers or text. Otherwise, numbers saved in number format will be higher than numbers saved in text format after sorting. To convert all selected data to text format, click CTRL+1 to open the dialog box Cell Format, open the tab Number and in the section Category select an option General, Number or Text.
Remove all leading spaces. Sometimes data imported from other applications may be padded with leading spaces. Remove them from your data before sorting. This can be done manually or using the SPACEBAR function.
Sorting numbers
Notes:
Possible problems
Make sure all numbers are saved in numeric format. If the results are different than expected, the column may contain numbers stored in text format. For example, negative numbers imported from some accounting systems, or numbers containing a leading apostrophe character ( " ), are saved in text form. For more information, see Convert numbers from text to numeric format.
Sort date and time values
Notes: Possible problems
Make sure that all date and time values are saved in date and time format. If the results are not what you expected, the column may contain date and time values stored in text format. For date and time values to be sorted correctly in Excel, they must be stored as ordinal numbers with date or time information. If Excel cannot recognize the value as a date or time, it is saved as text. For more information, see Convert dates from text format to date format.
To sort by day of the week, change the format of the cells so that they display the days of the week. To sort by days of the week, regardless of date, convert them to text using the TEXT function. However, the TEXT function returns a text value, so the sort will be done by alphanumeric data. For more information, see Display dates as days of the week.
Sort by multiple columns or rows
You might want to sort on two or more columns or rows to group data that has the same values in one column or row, and then sort those groups that have the same values in another column or row. For example, if you have Department and Employee columns, you can sort first by Department (to group all employees by department) and then by Name (to alphabetize the names of employees in each department). You can sort by 64 columns simultaneously.
Note: For best results, you should include column headers in the range you're sorting.
Sort by cell color, font color, or icon
If you formatted a cell range or table column manually or using conditional formatting using cell color or font color, you can also sort by color. You can also sort by a set of icons created using conditional formatting.
Sorting by custom lists
You can use custom lists to sort in a user-specified order. For example, a column might contain values that you want to sort by, such as "High", "Medium", and "Low". How can I set the sorting to show "High" first, then "Medium", and finally "Low"? If you sort them alphabetically (A to Z), the "High" values will appear at the top, but the "Low" values will appear behind them, not the "Medium" ones. And when sorting from Z to A, the “Average” values will be at the very top. In reality, "Medium" values should always appear in the middle, regardless of the sort order. You can solve this problem by creating a custom list.
Case sensitive sorting
Sorting from left to right
Typically sorting is done from top to bottom, but values can be sorted from left to right.
Note: Tables do not support left-to-right sorting. First, convert the table into a range: select any cell in it and select the items Working with tables > Convert to range.
Note: When sorting rows that are part of a worksheet structure, Excel sorts the highest-level groupings (level 1) so that the order of drill-down rows or columns does not change, even if they are hidden.
Sort by part of the value in a column
To sort by part of the value in a column, such as part of the code (789- W.D.G.-34), last name (Regina Pokrovskaya) or first name (Pokrovskaya Regina), you first need to split the column into two or more parts so that the value you want to sort by is in its own column. To break down the values in a cell, you can use text functions or the Text Wizard. For more information and examples, see Split text into different cells and Split text into different columns using functions.
Sort a smaller range within a larger one
Warning: You can sort values in a range that is part of another range, but this is not recommended because it will break the connection between the sorted range and the original data. If you sort the data as shown below, the selected employees will be associated with other departments.
Fortunately, Excel issues a warning if it detects such an attempt:
If you did not intend to sort the data this way, select the option automatically expand the allocated range, otherwise - sort within specified selection.
If the result is not what you want, click the button Cancel .
Note: You cannot sort the values in a table in this way.
Learn more about common sorting issues
If the results of sorting your data are not what you expected, do the following:
Check if the values returned by formulas have changed If the data you're sorting contains one or more formulas, the values they return may change when the worksheet is recalculated. In this case, reapply the sort to get the latest results.
Show hidden rows and columns before sorting Sort by column does not move hidden rows, and sort by row does not move hidden columns. Before you sort your data, it's a good idea to show hidden rows and columns.
Check the current locale setting The sort order depends on the selected language. Make sure that the control panels In chapter Regional settings or language and regional standards The correct locale is set. For information about how to change the locale setting, see Microsoft Windows Help.
Enter column headings on only one line If you need to use multiple line headings, set word wrapping in the cell.
Enable or disable the title bar It is generally recommended to display a header row when sorting by columns because it makes the data easier to understand. By default, the value in the header is not included in the sort. But in some cases, you may want to enable or disable a header so that the value in the header is or is not included in the sort. Perform one of the following actions.
To exclude the first row of data (column header) from sorting, on the tab home in Group Editing click the button Sorting and Filter, select a command Custom sorting and check the box.
To include the first row of data in the sort (since it is not a column header), on the tab home in Group Editing click the button Sorting and Filter, select a command Custom sorting and uncheck My data contains headers.
Sorting data in Excel is a very useful tool that improves the perception of the information presented. Before moving on to analyzing the interface, let's look at the sorting order of the application:
- The numbers go first from smallest to largest;
- Then there are special characters ($, %, *, etc.);
- They are followed by punctuation marks;
- Next the alphabets begin, first English, then Russian. It is also not case sensitive, i.e. uppercase and lowercase letters are equal to each other (A=a).
To organize, go to the “Data” tab and select the “Sorting and Filter” section. There are 3 icons for sorting:
Sort by ascending (A to Z), descending (Z to A) and sort icon to set conditions for multiple columns at the same time.
Let's consider all the points in more detail.
In order to sort a range of cells in Excel, first select it, then click on the required icon. Please note that the topmost cell in the selected range will be perceived as the table header, i.e. will not be sorted and will remain in the same place. Also, if there are other filled cells next to the selected range, the program will display the following window:
If you choose the first option, the table rows will retain their integrity. If the second, then data outside the sorting range will remain untouched. The figures show the results before and after sorting for two options.
The initial state:
Setting the order of the first table column when choosing the first option:
As you can see, the lines have retained their integrity, and the names still correspond to their surnames.
The same steps, but when choosing the second option:
The sorting is also done by the first column, except that the second column has not shifted relative to the sorted cells.
Immediately select the required range for sorting so that Excel does not automatically offer you to expand it. The column where you start selecting the range will be the column for the sort conditions.
If there is a need to set the order for several columns, then the “Sorting” icon, which has already been mentioned, will help with this. Select the range you want to sort. In this case, it doesn’t matter which column it starts to highlight from, because conditions and columns will be set manually. For example, we will use the following table:
We'll specifically use this data to visualize how Excel performs sorting.
Select the range of our table and click on the "Sorting" icon. The following window will appear:
Initially, the first sorting level has already been added. Select the desired column from the drop-down list. Its name is the top (“cap”).
In the following drop-down list you can select by which parameters to sort the range. For example, if you used a fill for cells or changed the font color, then it is possible to set the order according to these parameters.
The following drop-down list specifies the order: ascending, descending, etc.
To add a second and subsequent columns for sorting, use the “Add Level” button and set parameters for it.
For our example, we use three-level sorting. For the first level, we will set the sorting by “Column1”, the order “From Z to A”. For the second level, we will set the sorting by “Column2”, the order “Ascending”. For the third level by “Column3”, the order is “Descending”. Sorting options are “values” everywhere.
Let's look at the resulting result:
First, the sorting was carried out by the first column (first level), highlighting the data in certain subranges (in the red frame). Next, the second level sorting occurs, but the data is sorted only within the subranges defined by the first level. Having sorted the second level, the subranges are again determined (in the blue frame). Then sorting occurs at the third level using the same algorithm.
Thus, you can set Excel to sort by a significant number of columns.
You can also set various parameters in the sorting window.
Sorting data in Excel is arranging tabular data in the desired order, for example, ascending (from smallest to largest) or descending (from largest to smallest). Numeric and text values, date and time values, and formats are sorted. Data sorting is possible both by columns and rows. Hidden rows and columns must be shown before sorting.
Types of data to be sorted and sort order
Sort numeric values in Excel
Ascending sorting of numeric values is an arrangement of values in which the values are arranged from smallest to largest (minimum to maximum).
Accordingly, sorting numerical values in descending order is the arrangement of values from largest to smallest (from maximum to minimum).
Sorting text values in Excel
"Sorting from A to Z" - sorting data in ascending order;
"Sort from Z to A" - sorting data in descending order.
To sort text values alphabetically, these values are compared with each other. As you know, when saving text, a computer uses a scheme in which each character has its own unique number, called the character code. It is these codes that are compared to determine which text value is greater and which is less.
Text values can contain alphabetic, numeric, and special characters. In this case, numbers can be saved both in numeric and text format. Numbers stored in numeric format are smaller than numbers stored in text format. To correctly sort text values, all data must be saved in text format. Additionally, when text data from other applications is inserted into cells, the data may contain leading spaces. Before you start sorting, you must remove leading spaces (or other non-printable characters) from the data being sorted, otherwise the sorting will not be performed correctly.
You can sort text data in a case-sensitive manner. To do this, you need to check the "Consider case" box in the sorting options.
Typically, uppercase letters have lower numbers than lowercase letters.
Sort date and time values
"Sort Oldest to Newest" is to sort date and time values from the earliest value to the latest value.
"Sort New to Old" is to sort date and time values from the latest value to the earliest value.
Sorting formats
Microsoft Excel 2007 and later provides sorting by formatting. This sorting method is used when a range of cells is formatted using a cell fill color, font color, or icon set. Fill and font colors in Excel have their own codes, and it is these codes that are used when sorting formats.
Sort by custom list
Tabular data can be sorted by custom lists, such as a list of months, a list of days of the week, a list of structural divisions of an enterprise, a list of employee personnel numbers, and so on. Excel has the ability to create your own data sorting lists. With this sorting order, the data to be sorted and the list values must match.
Sort Options
Sort by column
In any version of Excel, sorting by column is set by default, that is, the values of the cells of the selected column are arranged in the desired order, and the rows of the range are swapped depending on the position of the cell in the sorted column.In order to sort a table by column, just set the selection marker in any cell of the table and click one of the icons symbolizing sorting from minimum to maximum, or from maximum to minimum. The table will be sorted by the column that contains the selection marker.
Sort by String
In the program settings, you can change sorting by column to sorting by row. If sorting by row is set, then the values of the cells of the selected row are arranged in the specified order, and the columns are swapped depending on the positions of the cells in the sorted row.
Multi-level sorting
So, if you sort by a column, then the rows are swapped, if the data is sorted by a row, then the columns are swapped.
Excel allows you to sort data not only by one column or one row, but by creating a different number of sorting levels. In Excel 2007, for example, there are 64 of them. Sorting levels can be added, deleted, copied and swapped.
Among other things, you can either take case into account or ignore case when sorting.
Add-in for sorting data in Excel
Microsoft Excel provides users with a fairly large set of standard tools for sorting values of various types, but there are tasks that are either inconvenient or time-consuming to solve using standard tools, for example, sorting each row/each column in such a way that sorting is done only within the row/ column and did not affect neighboring cells.
In most cases of working with data, they are organized vertically, so when processing is meant, it means vertical data processing, including sorting. Those. If you need to sort numbers in ascending order, it is assumed that at the top of the list there will be numbers with minimum values, and at the bottom of the list with maximum values. It is precisely this “vertical” organization of data that is associated with extreme popularity (relative to the same).
In this material, we do not consider standard “vertical” sorting; it has received enough attention both on the Internet and in ours. In the course, we looked at sorting by one or several criteria, as well as sorting taking into account cell attributes.
But still, how sort Excel cells horizontally? For horizontal sorting in Excel, there are several options that complement each other:
- Using the standard custom sorting mechanism: Home tab/Editing group/Sort and Filter command/Custom sorting. Next, in the parameters, select sorting by range columns.
The advantages of this sorting option are that you can sort any type of information (both numbers and text). The disadvantage of this option is that if you need to sort a large amount of data, you will have to configure each individual row separately.
- Using the functions or . This method solves the problem of a large amount of data vertically; by skillfully operating, you can easily do horizontal sorting for a large amount of data. But this method has one significant drawback, which, by the way, is absent in the first option - the ability to sort only numerical information. Yes, unfortunately, it is not suitable for text.
- Using a mechanism. We have already repeatedly used this tool when solving competition tasks ExcelWorldChamp and. At first glance, the solution may seem cumbersome, but as you master Power Query, you can get used to it. And, if you plan to process data in Excel, then now you simply cannot do without using this tool.
Using horizontal sorting with Power Query
To perform any manipulations in Power Query, the data must first be loaded. On the data tab, depending on the type of data source, we use the appropriate command. In our simple case, it is “From table/range”.
In the Power Query editor window itself, go to the “Add Column” tab, “Custom Column” command. In the column settings dialog box, enter the List.Sort() function, passing columns as arguments. By default, the function sorts in ascending order, so this optional parameter must be specified if sorting is needed in descending order.
List.Sort(([Column1],[Column2],[Column3],[Column4],[Column5],[Column6],[Column7]), Order.Ascending)
which is equivalent
List.Sort(([Column1],[Column2],[Column3],[Column4],[Column5],[Column6],[Column7]))
and here is the formula for descending order
List.Sort(([Column1],[Column2],[Column3],[Column4],[Column5],[Column6],[Column7]), Order.Descending)
Since the result is an element of the list type, you need to decide on further sorting actions. If you need to get a column with values, select the “Expand to new rows” option, however, if you are sorting for a large amount of data, it is better to use the “Extract values...” option. When extracting values, they are all extracted into one cell with a certain separator (or without). If you then need to split the values between cells, you can do this in several ways, we have discussed them in this article, however, the best option would be to use the “Split Column” command of the “Home” tab in Power Query itself.
