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.
To create convenient and comfortable conditions for the user when viewing and analyzing information placed in tables, the MS Excel program provides fairly wide possibilities for sorting data. Sorting records in a large database in Excel...
Makes it somewhat 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. numeric values are sorted according to the principle “from smallest to largest” or vice versa.
2. Columns with cells containing text values are sorted alphabetically in forward or reverse directions.
3. Columns with cells containing dates, are sorted according to the principle “from oldest to newest” or vice versa.
Sorting in Excel 2007 and newer versions can additionally be done by formatting criteria - by cell fill color, by cell font color, by a set of conditional formatting icons.
Let’s continue working with the DB2 database “Production of metal structures by site No. 2”, created in the article “ ».
The training database under consideration consists of only 6 fields (columns) and 10 records (rows). Real databases typically 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 such understanding that we must look at our subsequent actions.
Further work will be structured as follows: in this and the following articles of this series we will use the various capabilities of the program MS Excel , formulate instant answers to various questions and tasks that arise in the practical activities of the production site manager. That is, we will solve real production problems!
Maintaining a database - collecting information - in itself does not give anything; on the contrary, it takes us some time! But the results of analyzing this information, allowing you to give accurate answers to a variety of questions, can greatly improve the manageability of database objects in real life!
In production, with proper construction and careful maintenance of databases, the use of analysis results allows you to double and sometimes triple the volume of production using the same areas, equipment and with the same composition of technical and technical equipment.
The simplest sorting.
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 with a 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 by clicking cell E7 with the column heading “Weight of 1 piece, t” (you can activate any cell in the column that interests us).
3. Click the “Sort Descending” button on the “Standard” toolbar.
4.
We read the answer to the question posed in the very top line of the database (line No. 8). The heaviest product in the database is Beam 045 from order No. 2. Beam 045 was manufactured from April 23 to April 25, 2014 (see entries in Excel lines No. 8-10).
5. You can return the database to the form before 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 across two or three columns.
Task #2:
Present database records grouped by orders and with product names within orders arranged alphabetically.
1
.
We activate any database cell by clicking the mouse (for example, cell C11).
2. Click the main menu button “Data” and select “Sorting...”.
3. In the “Sorting Range” window that pops up, select the values from the drop-down lists as shown in the screenshot on the left and click “OK”.
4. Task No. 2 completed. The records are, firstly, sorted by order numbers and, secondly, within each order they are arranged alphabetically by product names.
Results.
In this second article of the series about information storage and data management We looked at the basic capabilities of sorting database records in Excel. This very simple mechanism of Excel is certainly familiar to most of you, dear readers. I hope I didn’t take up too much of your time with a banal story about a well-known service. But... the path to the complex begins with the simple!
To be honest, in practice you rarely have to resort to sorting. Why? The answer to this question will be in the following articles of the series, which will talk about other, including more advanced and powerful Excel mechanisms for fast and efficient data processing.
I beg respectful 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 your reviews and comments in the comments.
Even beginners can sort data in Excel. It's hard not to notice three buttons on the ribbon at once.
But sometimes they ask how to sort data in an Excel table by columns, by color, etc. All these tasks can be easily solved in Excel.
Sort by multiple columns in Excel
You can sort rows in Excel by multiple columns at the same time. In this case, the first column specified is sorted. Then those rows where cells in the sorted column are repeated are sorted by the second specified column, and so on. Thus, the sales report can be organized first by region, then within each region by manager, then within one region and manager by product group.
Having selected one cell or the entire table, call the sort command on the ribbon. If necessary, check the box My data contains headers and add the required number of levels.
Sort by your own list
Most users know how to sort rows in Excel by alphabetical or ascending numbers. But sometimes you need to arrange lines in a given order, which does not necessarily correspond to the alphabet or ascending sequence. This could be a hierarchy of elements, cities and countries in a convenient sequence, etc. In general, something that has its own logic.
The simplest (and therefore worthy of attention) method is to put numbers in a column next to each other in accordance with the desired order of the rows, and then sort the table by this column.
This method is good, but it requires creating an additional temporary column. If the operation is repeated frequently, it will take time. If you have to sort the same data, you can create a special list, on the basis of which the sorting will then take place. This is the same list used in .
Let's go to File – Options – Advanced – General – Edit Lists…
Here we create manually or import a list sorted in the desired order.
Now in the sorting window in the field Order need to choose Custom list...
And in the next window, specify the desired list.
The created list can also be used in other Excel files.
Sort in Excel by cell color, font, icon
In the sorting settings you also have the option to use cell color, font and icon (from ). If you use fill to format individual cells (for example, to indicate problematic products or customers), then you can easily move them to the top of the table using sorting.
Sorting by Columns
You can also sort by columns in Excel. To do this, select the table along with the column names. Then in the sorting window, first click Options and put the switch on range columns.
The following settings are usual: set the line (!) and order. The only thing is that now you can’t use row names (by analogy with column names), there will only be numbers.
Sort subtotals
Excel has a tool like Subtotals. In short, it is needed to automatically create subtotals under a group of cells that are homogeneous in some way.
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 sorting 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 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 on up to 64 columns at once.
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.
