Sorting data in Excel is a very useful feature, but it should be used with caution. If a large table contains complex formulas and functions, then the sort operation is best performed on a copy of this table.
Firstly, in formulas and functions, addressing in links may be violated, and then the results of their calculations will be erroneous. Secondly, after multiple sortings, you can shuffle the table data so that it will be difficult to return to its original form. Thirdly, if the table contains merged cells, then they should be carefully separated, since such a format is not acceptable for sorting.
Sort data in Excel
What tools does Excel have to sort data? To give a complete answer to this question, let's look at specific examples.
Preparing the table for correct and safe data sorting:
Now our table does not contain formulas, but only the results of their calculation. Merged cells are also disconnected. It remains to remove the extra text in the headings and the table is ready for safe sorting.
To sort the entire table according to one column, do the following:
Data sorted throughout the table relative to the "Net Profit" column.
How to sort a column in Excel
Now let's sort only one column without binding to other columns and the whole table:
The column is sorted independently of the other columns in the table.
Sort by cell color in Excel
When copying a table to a separate sheet, we only transfer its values using Paste Special. But sorting capabilities allow us to sort not only by values, but even by font colors or cell colors. Therefore, we also need to transfer data formats. For this:
The table copy now contains values and formats. Let's sort by color:
- Select the table and select the "Data" - "Sort" tool.
- In the sorting options, again check the box "My data contains column headings" and indicate: "Column" - Net profit; "Sort" - Cell color; "Order" - red, on top. And click OK.
On top, we now have the worst net income figures, which have the worst performance.
Note. Then you can select the range A4:F12 in this table and repeat the second paragraph of this section, only specify pink on top. Thus, cells with color will go first, and then ordinary ones.
Sorting data is one of the important tools in Excel. Sorting is the ordering of data in the desired order, i.e. if you need to line up numbers from largest to smallest, for example. Thanks to this function, it is easy to line up data in the shortest possible time intervals in order to further analyze them or remove unnecessary ones. So how to sort in Excel?
In order to sort the data, you can select the table and right-click, select Sort
You can also sort in excel using the "Sort and Filter" function, which is located on the Home tab in the Editing panel block.
You can sort values in ascending or descending order (sort A to Z, sort largest to smallest)
For this type of ordering, you must activate the cell of the column in which you want to sort the data, and select the required one from the window that opens.
Table data can be ordered according to their cell color or font color. To do this, select a cell and select Sort by Color.
It should be noted that this function does not apply to the font type "italic" or "bold". The same applies to the font and color of the cells that are set when the book is opened. This function is convenient in that it allows you to process a huge array of data that was previously marked in a certain way - in color or in font. After this type of sorting is activated, an array of data will be obtained selected according to the type of the selected cell.
How to sort in Excel by three or more columns?
This is the so-called custom sorting. This type of sorting is carried out according to the following algorithm: Main - Sorting and filter - Custom sorting ...
The meaning of this type of sorting is that you can sort the data by two criteria at once.
For example, we need to determine by city which employee makes the most sales. You can also compare these indicators by time intervals: month, quarter, year (if there are necessary columns)
How to sort in Excel by rows or columns
Data can be sorted both by columns and by rows, following the following algorithm: data - Sort - Parameters
This type of sorting allows you to analyze information according to certain parameters specified in the headers of the data table itself.
Yes, you can sort by columns! Read about it.
Basic principles of sorting
When using the sorting function, you must follow a number of rules.
1. Important! How to include headings when sorting? We must not forget to activate the "My data contains headers" function, which is located in the Sorting tab (upper right corner in picture 3). If the function is not active, then Excel will order the headings together with the entire data array or increase the range for analysis.
2. If the data array contains columns or rows that were hidden and are invisible during processing, then the ordering function will not be applied to them.
3. If there are merged cells in the data list, then the sorting function will not be available for them. You must either unmerge the cells in the given range, or leave the range blank in the sort area.
4. The data is sorted according to certain rules. So, when processing an array of data in the final list, numbers will be placed first, followed by signs. Next is information in English, and only then information in Russian. For example, you are sorting an array of data containing numeric values and text values in Russian. After the sorting procedure in the final list, the numeric values will be located above the text ones.
5. A cell with a number format but a numeric value will come before a cell with text formats and a numeric value.
6. Blank cells are always at the end of a sorted table.
Sorting is a very important feature in Excel. Experienced users use it to the fullest, as for beginners, perhaps not everyone is aware of how many opportunities they are losing. In short, this function allows you to sort the data in the table according to some specific criterion.
The fact is that in large tables a lot of data is used, in which you can get confused. To prevent this from happening, it is logical to arrange all the information in the order you need, sort it out, so to speak. Also, sorting is very convenient. I propose to discuss the issue of how to sort data in an excel table in more detail.
Sort types
I'll start by saying that there are various criteria according to which data can be sorted. In particular, the following are distinguished:
- Sort from A to Z. With this view, you can sort the data in ascending order.
- Sorting from Z to A means, respectively, sorting the data in descending order.
- Sort by month and day of the week.
- Sort by formatting. This flavor is available in Excel 2007 and later. This sorting variation is relevant if the range of cells is formatted using font color, cell shading, or a set of icons. Since the color of the font and fill has its own code, it is the program that uses it when sorting formats.
How to sort data
As for how to sort information in an Excel table, the situation is as follows. You need to select the table and in the "Data" tab, click the "Sort" button. Next, a new window will open in which you need to set the sort column, as well as select the sort order. If you do not need to sort data in ascending or descending order, click "Custom List".
Now a new dialog box has opened, in which you can select a list of days of the week or months (namely, according to these parameters, the user most often arranges data).
If the desired list is not in the list, then you can create your own. To do this, click on the "New List" line and manually enter the elements of the proposed list, then click "Add". Please note that the list you create will be saved and you can reuse it next time.
As you can see, this function allows you to sort not only by simple criteria, such as alphabetically, in ascending order of numbers, etc., but also set your own criteria for sorting. In particular, you can use military ranks, positions, some specific goods, the value and importance of which is determined solely by you, etc.
Video to help
If you need to find a specific value in a table, it is most convenient to do this in sorted data. Next, we will look at how to sort a table in Excel in ascending or descending order, text or cell color, and more.
To sort tables in Excel, use the menu item "Sort and Filter" on the home tab. The simplest sort given in an Excel spreadsheet is ascending or descending sort, which can be applied to both text and numeric values.
For correct sorting, all cells to be sorted must have the same format, for example, all cells with numbers must be in numeric format. Also, there should be no extra spaces before the values, and there should be no hidden rows and columns in the sorted range.
Sorting values in an Excel table begins with the selection of the column to be sorted. It is enough even to place the cursor in the first cell of the column. In our example, select the first cell of the second column, select from the menu "Sort and Filter", and specify "Sort Ascending".
As a result of sorting the data in the table, the values in the second table are sorted in ascending order, but the first cell has remained in its place. All this is because, by default, the first row in the table is considered headers, i.e. column names, and is not sorted.
The range of data to be sorted expands automatically, i.e. Excel automatically selects the entire table and sorts the data row by row according to the selected column. If you select all the cells in a column that need to be sorted and select ascending or descending sorting, Excel will display a message prompting you to select a sort option. Option will be available "Automatically expand the selected range", which will select the entire table, and "Sort within the specified selection", which allows you to sort only the selected column without affecting the data in adjacent columns. In this case, when sorting, again, the first cell will not be taken into account.
To sort tables in Excel completely by all selected cells, you should in the menu "Sort and Filter" select item "Custom sorting...".
In this case, we will immediately see which part of the Excel table allocates for further sorting.
To select the entire data area, in the window that appears, "Sorting" uncheck the box "My data contains headers".
Now in this window you can configure the sorting of our data. In the first column "Column" in line "Sort by" select the column whose data you want to sort. In the second column "Sorting" you must select the condition by which sorting will be performed. Options available "Values", "Cell Color", "Font Color" And "cell icon". In our case, we will sort the values. Well, in the last column "Order" you can choose the sort order of the values "Ascending", "Descending" And "Custom List". Let's choose in ascending order. As a result, the values of the middle column are sorted.
You can also customize the sorting of an individual column. In addition, in Excel, you can set up several levels of sorting. For example, let's sort the first column in ascending order, and then sort the result of sorting the second column in ascending order as well. And the last third column is sorted first by the color of the cell, and then by the color of the font. To add a new level, you need in the window "Sorting" press the button "Add Level", while the order of the levels matters.
Consider, how to set up sorting
Excel on multiple columns and rows
, under several conditions at once. For example, so that the data is sorted alphabetically not only in the first column, but in all columns. Or, so that the data in the third column with the price are arranged in ascending order, etc. Read about the basics of sorting in the article " Sorting in Excel».
We have such a table.
We need to sort all data by all three columns:
Stores - alphabetically;
Name - alphabetically;
Prices are in ascending order.
Place the cursor on any cell in the table. On the Home tab, in the Editing section, click on the Sort and Filter button. From the list that appears, select the "Custom sorting" function.
A dialog box like this will appear. 
"Sort by" - select the name of the first column "Store". The sort order we need is "From A to Z".
In this dialog box, click on the "Add Level" button. A new line will appear. We configure this line to sort the second column "Name" - "From A to Z".
We need to sort the data of three columns, e.g. Click on the "Add Level" button. In the new, third, line (third level), select the name of the column "Price". "Order" - select "Ascending".
We filled in the dialog like this. 
Click "OK". It turned out like this.
For better visibility, we made dividing lines. How to do them automatically, see the article "Separating line of rows in an Excel table"
