Sorting data in Excel is the arrangement of tabular data in the desired order, for example, ascending (from smallest to largest) or descending (from largest to smallest). Sorting includes numeric and text values, date and time values, and formats. Sorting data is possible both by columns and by rows. Hidden rows and columns need to be shown before sorting.
Data types to sort and sort order
Sort numeric values in Excel
Sort numerical values in ascending order - this is such an arrangement of values in which the values are arranged from smallest to largest (from minimum to maximum).
Accordingly, sorting numerical values in descending order is the arrangement of values from largest to smallest (from maximum to minimum).
Sort text values in Excel
"Sort 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 symbols. In this case, numbers can be stored both in numerical and in text format. Numbers stored in numeric format are smaller than numbers stored in text format. For correct sorting of text values, all data must be saved in text format. In addition, when pasting text data from other applications into cells, this data may contain spaces at the beginning. Before sorting, you must remove leading spaces (or other non-printable characters) from the data being sorted, otherwise the sorting will not work correctly.
You can sort text data case sensitive. To do this, in the sorting options, check the "case sensitive" box.
Typically, uppercase letters are numbered lower than lowercase letters.
Sorting date and time values
"Sort old to new" is sorting date and time values from earliest to latest.
"Sort Newest to Oldest" is the sorting of date and time values from newest to oldest.
Sorting formats
Microsoft Excel 2007 and above provides sorting by formatting. Use this sorting method when a range of cells is formatted with a cell fill color, font color, or icon set. The colors of fills and fonts in Excel have their own codes, and it is these codes that are used when sorting formats.
Sorting by custom list
Table data can be sorted by custom lists, such as a list of months, a list of days of the week, a list of business units, a list of employee payroll numbers, and so on. Excel has the ability to create your own data sorting lists. With this sort 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 cell values 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 the table by column, it is enough to set the selection marker in any cell of the table and click one of the icons symbolizing sorting from the minimum to the maximum, or from the maximum to the minimum. The table will be sorted by the column containing the highlight 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 cell values 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.
Multilevel sorting
So, if the data is sorted by a column, then the rows are reversed; if the data is sorted by a row, then the columns are reversed.
Excel allows you to sort data not only by one column or one row, but also create different quantity sorting levels. In Excel 2007, for example, there are 64. Sort levels can be added, removed, copied, and swapped.
Among other things, sorting can be case-sensitive or case-insensitive.
Add-in for sorting data in Excel
Microsoft Excel provides users with a fairly large set of standard tools for sorting values. 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 does not affect neighboring cells.
Sorting data is an integral part of their analysis. You might want to alphabetize the names in the list, list the inventory and sort it in descending order, or arrange rows by color or icon. Sorting data helps you quickly visualize data and understand it better, 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 (lowest to largest or largest to smallest), and date and time (oldest to newest or newest to oldest) in one or more columns. You can also sort by custom lists that you create yourself (for example, a list of Large, Medium, and Small items), or by format, including cell color, font color, and icons.
Notes:
To find the largest and the smallest values in a range of cells or a table, such as the top 10 ratings or the bottom 5 sales totals, you can use the AutoFilter feature or conditional formatting.
additional 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 to be sorted contains numbers stored in numeric format and numbers stored in text format, you must format all cells as either numbers or text. Otherwise, numbers stored in numeric format will be higher than numbers stored in text format after sorting. To convert all selected data to text format, click CTRL+1 to open the dialog Cell Format, open the tab Number and in the section Category choose 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 the data before sorting. This can be done manually or with the TRIM function.
Sorting numbers
Notes:
Possible problems
Make sure all numbers are stored in numeric format. If the results are not what you 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 as text. For more information, see Convert numbers from text to number format.
Sorting date and time values
Notes: Possible problems
Make sure all date and time values are stored in datetime 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. See Convert dates from text format to date format for more information.
To sort by day of the week, change the cell format to display the days of the week. To sort by the days of the week regardless of the date, convert them to text using the TEXT function. However, the TEXT function returns a text value, so the sort will be based on alphanumeric data. See Display dates as days of the week for more information.
Sort by multiple columns or rows
You might want to sort on two or more columns or rows to group data with the same values in one column or row and then sort those groups with the same values in another column or row. For example, if you have "Department" and "Employee" columns, you can sort first by the "Department" column (to group all employees by department) and then by name (to arrange the names of employees in each department in alphabetical order). You can sort on up to 64 columns at a time.
Note: For best results, the range to be sorted should include column headings.
Sort by cell color, font color, or icon
If a cell range or table column was formatted 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 appear at the top, but behind them are the "Low" values, not the "Medium" values. And when sorted from Z to A, the values "Average" will be at the very top. In reality, "Middle" values should always appear in the middle regardless of sort order. You can solve this problem by creating a custom list.
Sort case sensitive
Sort from left to right
Normally, sorting is done from top to bottom, but values can be sorted from left to right.
Note: Tables do not support the ability to sort from left to right. First, convert the table to a range: select any cell in it and select items Working with tables > Convert to Range.
Note: When sorting rows that are part of a worksheet structure, Excel sorts groups the highest level(Level 1) so that the order of drill rows or columns does not change even if they are hidden.
Sort by part of the value in a column
To sort on part of a value in a column, such as part of a code (789- WDG-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 split the values in a cell into parts, you can use the text functions or the text wizard. For more information and examples, see Break text into different cells and Break 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 as it will break the link 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.
Luckily, Excel issues a warning if it detects such an attempt:
If you did not intend to sort the data in this way, select the option automatically expand the selected range, otherwise - sort within the specified selection.
If the result is not what you want, press the button Cancel .
Note: It is not possible to sort values in a table in this way.
Learn more about common sorting issues
If the results of sorting the data are not as expected, do the following.
Check if the values returned by formulas have changed If the data being sorted contains one or more formulas, the values returned by those formulas may change when the worksheet is recalculated. In this case, reapply the sort to get up-to-date results.
Show hidden rows and columns before sorting Sorting by columns does not move hidden rows, and sorting by rows does not move hidden columns. Before sorting the data, it is advisable to display the hidden rows and columns.
Check the current locale setting The sort order depends on the selected language. Make sure on control panels In chapter Regional Options 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 one line only If you want to use headers on multiple lines, set the cell to word wrap.
Enable or disable title bar It is generally recommended to display the header row when sorting by columns, as it makes the data easier to read. By default, the value in the header is not included in the sort. But in some cases, you may want to enable or disable the header so that the value in the header is included or 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 a group Editing press the button Sort and filter, select a command Custom sorting and check the box.
To include the first row of data in the sort (because it is not a column heading), on the tab home in a group Editing press the button Sort and filter, select a command Custom sorting and uncheck My data contains headers.
In most cases of working with data, they are organized vertically, so when processing is meant, it is vertical data processing, including sorting. Those. if you need to sort the 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 with such a “vertical” organization of data that extreme popularity (relative to the same) is associated.
In this material, we do not consider the standard “vertical” sorting; enough attention has been paid to it both on the Internet and in ours. In the course, we considered sorting by one, several criteria, as well as sorting based on 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, 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 version - this is the ability to sort only numerical information. Yes, unfortunately not suitable for text.
- With the help of the mechanism. We have repeatedly used this tool when solving competition tasks ExcelWorldChamp and. At first glance, the solution may seem cumbersome, however, as you master Power Query, you can get used to it. And, if you plan to process data in Excel, then without using this tool it's just not possible right now.
Use 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 the columns as arguments. By default, the function is sorted in ascending order, so this optional parameter must be specified if sorting is required 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 decreasing 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, all of them are extracted into one cell with a certain separator (well, or without). If later it becomes necessary to spread the values into cells, this can be done in several ways, we have considered them in this one, however, the best option would be to use the “Split Column” command of the “Home” tab in Power Query itself.
Hi all! Today I will continue to improve your skills in important programs, and show you how to do sorting in excel.
The skill is necessary, and if you decide to work with a meaningful table, take its duplicate. Working with the original, you run the risk of violating the accuracy of the calculations due to the violation of the integrity of the links, and multiple sorts will mix all the data so that it will become problematic to return the initial version. Please also note that the presence of merged cells is not allowed. Separating them will help here.
How to sort in excel
What does excel have in its functionality for sorting data? I will show some examples to answer the question: how to do sorting in excel.
Preparing the table:
The table is filled with results, no formulas. And the cells that were merged were replaced by disconnected ones. We clear the headings from the text, and we get a table ready for sorting.
To sort one column:
We now have data that has been sorted with respect to the Net Income column.
Sorting in a column
Sort one column without touching neighboring ones:
This is what the sorted column looks like:
Sort in Excel by cell color
The sorting possibilities are so wide that it is possible to do this even taking into account the color of the cell. Creating a separate table implies not only copying the data, but also their format. Necessary:
Separate merged cells if available.
Sort by colors.
We make a selection of the table. Select through "Data", "Sort".
Go to the options, check "My data contains column headings". Confirm with the "OK" button. 
At the top is the worst profit. 
I hope you have understood and well learned the lesson on how to do sorting in excel. I look forward to seeing you in the next lessons.
How to sort in excel
To create convenient and comfortable conditions for the user when viewing and analyzing information placed in tables, MS Excel provides a fairly wide range of data sorting options. Sorting in Excel the records of a large database...
It makes it easier for the user to find the information of interest.
When sorting, all database rows change their location in accordance with the algorithm we have chosen, while remaining, of course, unchanged.
Three main sorting algorithms in Excel.
1. numerical values are sorted from smallest to largest or vice versa.
2. Columns with cells containing text values are sorted alphabetically forward or backward.
3. Columns with cells containing dates, are sorted according to the principle "oldest to newest" or vice versa.
Sorting in Excel 2007 and newer versions can be additionally performed by formatting features - by cell fill color, by cell font color, by a set of conditional formatting icons.
Let's continue working with the database DB2 "Production of metal structures by section No. 2", created in the article " ».
The considered training database consists of only 6 fields (columns) and 10 records (rows). Real databases usually contain more than a dozen fields and thousands of records! Finding the necessary information in such a table is not easy! It is through the prism of this understanding that it is necessary to look at our subsequent actions.
Further work will be structured as follows: in this and the following articles of this cycle, we will use the various features of the program MS excel , to form instant answers to various questions and tasks that arise in the practical activities of the head of the production site. That is, we will solve real production problems!
Maintaining a database - collecting information - in itself does not give anything, on the contrary, it takes some time from us! But the results of analyzing this information, allowing you to give accurate answers to various questions, can greatly improve the manageability of database objects in real life!
In production, with proper construction and careful maintenance of databases, the application of the results of analysis allows you to double and sometimes triple the volume of output on the same areas, equipment and with the same composition of engineering and technical personnel.
The simplest sort.
The simplest sorting in Excel is done using the Sort Ascending and Sort Descending buttons located on the Standard toolbar. (In the figure below, these buttons are circled in red ellipse.)
Task #1:
Determine: which of the manufactured products is the heaviest and what is its mass? When was this product made?
1. Open the file in MS Excel.
2. We activate cell E7 with the column heading “Mass 1pc, t” by clicking the mouse (you can activate any cell in the column of interest to us).
3. Click the Sort Descending button on the Standard toolbar.
4.
We read the answer to the question posed in the topmost line of the database (line No. 8). The heaviest product in the database is Beam 045 from order No. 2. Beam 045 was made from April 23 to April 25, 2014 (see records in Excel lines No. 8-10).
5. You can return the database to the form that preceded sorting in Excel (if necessary) by clicking the "Cancel" button on the same "Standard" toolbar. Or you can apply "Sort Ascending" to the "Date" column of the database.
Sorting in Excel by multiple columns.
Sorting in this way can be done sequentially on two or three columns.
Task #2:
Present database records grouped by orders and with product names within orders arranged alphabetically.
1.
Activate by mouse click any cell of the database (for example, cell C11).
2. Press the main menu button "Data" and select "Sort ...".
3. In the pop-up "Range Sort" window, select the values from the drop-down lists as shown in the screenshot on the left and click "OK".
4. Task #2 completed. The records are, firstly, sorted by order numbers and, secondly, within each of the orders are arranged alphabetically by product names.
Results.
In this second article in the series about information storage and data management we have covered the basic sorting capabilities of database records in Excel. This very simple mechanism of the Excel program is certainly familiar to most of you, dear readers. I hope I didn't take too much of your time with a banal story about a well-known service. But… the path to the complex lies from the simple!
To be honest, in practice you rarely have to resort to sorting. Why? The answer to this question is in the next articles in the series, which will talk about others, including more advanced and powerful Excel mechanisms for fast and efficient data processing.
I beg respecting author's work subscribe for article announcements in the window located at the end of each article or in the window at the top of the page!
Dear readers, write reviews and comments in the comments.
