Sorting in Excel is a feature built into Office Microsoft package Office, which allows you to carry out analytical research, quickly find the required indicators. If you thoroughly learn how to do sorting in Excel, many data analysis tasks will be performed quickly and with absolute precision.

Special filters are used to find the information you need.

Analysis of the work done, sales volumes, profit growth, student performance, purchases of additional materials are accompanied by the selection of parameters that have maximum and minimum indicators. Of course, if the table is small, then the user can just find the best indicator. But in cases where Excel has an excessively large number of rows and columns, without using the built-in functions that allow you to sort the table, you can find the desired indicator, but you will have to spend a lot of time.

You can do much more practical, familiarize yourself with the information on how to sort in Excel, and immediately begin to practice the knowledge gained.

Ascending and descending filter

Sorting data in ascending or descending order is easy. It is only necessary to find out if the table is accompanied by numerous formulas. If this is the case, then it is best to transfer the table to a new sheet before sorting the data, which will avoid violations in the formulas or accidental breaking of links.

In addition, a fallback version of the table never hurts, because sometimes, getting confused in your own reasoning, wanting to return to the original version, it will be difficult to do this if a preliminary copy is not created.

So, first you need to select the table to be analyzed. Next, go to a new sheet, right-click, and then click on the line “ Paste special". A window with parameters will appear on the screen in front of the user, among which you need to select the "Value" parameter, and then click "OK".

Now the duplicate variant has been created, so you can proceed with further actions. To fully understand how to sort a table in Excel in ascending order, you need to select the entire table again, then go to the "Data" tab, there, among several tools, there will be the desired "Sort" on which you need to click.

In the opened parameters window there is a line "My data contains headers". There is a small window next to it, in which you should put a tick. It remains to put in the below opened windows the column to be analyzed, as well as the option of the desired sorting: ascending or descending. Next, we agree with the set parameters, after which the table will instantly demonstrate the desired result, saving you from hours of exhausting work.

If you need to sort not in the entire table, but only in one column, the actions will be almost the same, except for only two points. Initially, you should select not the entire table, but only the desired column, and later, when Excel prompts you to automatically expand the range in order to sort, you need to refuse this by checking the box next to the phrase "Sort within the specified range".

Sorting by other parameters

Sometimes, working in Excel, it becomes necessary to sort not numerical values ​​in ascending or descending order, but somewhat different parameters, so you can familiarize yourself with practical advice experienced users, thanks to whom it is easy to figure out how to sort by date or by cell format in Excel.

Setting a filter by date and format

The sorting principle remains essentially the same. The user needs to select the table, indicate the column to be analyzed, and then click on one of the suggested actions: "Sort from old to new" or "Sort from new to old". After such actions, the table will sort all information or data by date.

Sometimes it is very important to do this again. This feature is built into the table. There is no need to re-enter several desired parameters, just click on the "Repeat" element in the filter.

Sometimes the filter can fail, but the reason is most likely the incorrect display of some formats. In particular, if in some cells the data is entered in a non-date format, the sorting will not be carried out quite correctly.

If there is an urgent problem to sort the table by cell format, initially it is also desirable to transfer it to a new sheet, only now, after right-clicking on the "Paste Special" line, you must select the "Formats" parameter. Then, not only all data will be transferred to the new sheet, but also the applied formats, and the formulas will again be excluded.

It remains to re-enter the filter, in its parameters, select sorting by the color of the cells, then in the color window that opens, select the color that, after sorting, should be at the top or bottom. After clicking on "Ok" the table will give an instant result.

If something went wrong, you need to know how to reverse sorting in Excel using one of two simple steps. You can simply press several times two simultaneously held keys (Ctrl + Z) until the table returns to its original form. If a lot of actions have been taken, it is easier to cancel sorting in the second way, closing the table without saving, then reopen it and start working again.

So, working with a filter in Excel is not difficult. It is enough to sort the data once, relying on the recommendations, and later everything will become so clear that it will be easy for you to master the rest of the filter parameters on your own.

Sorting data in Excel is a tool for presenting information in a user-friendly way.

Numerical values ​​can be sorted in ascending and descending order, text values ​​alphabetically and in reverse order. Options are available - by color and font, in any order, by several conditions. Columns and rows are sorted.

Sort order in Excel

There are two ways to open the sort menu:

Commonly used sorting methods are represented by a single button on the taskbar:

Sorting a table by a separate column:

Selecting the entire table and sorting will sort the first column. The data in the rows will be in accordance with the position of the values ​​in the first column.

Sort by cell color and font

Excel provides the user with rich formatting options. Therefore, you can operate with different formats.

Let's make the "Total" column in the study table and "fill" the cells with values ​​of different shades. Let's sort by color:

The program sorted the cells by accents. The user can independently choose the color sorting order. To do this, in the list of tool capabilities, select "Custom sorting".

In the window that opens, enter the necessary parameters:

Here you can select the order in which cells of different colors are presented.

By the same principle, data is sorted by font.

Sorting in Excel by multiple columns

How to set secondary sort order in Excel? To solve this problem, you need to set several sorting conditions.

The program allows you to add several criteria at once to perform sorting in a special order.

Sort rows in Excel

By default, data is sorted by column. How to sort by rows in Excel:

Thus, a table is sorted in Excel by several parameters.

Random sort in Excel

Built-in sorting options prevent data from being randomly arranged in a column. The RAND function will cope with this task.

For example, you need to randomly arrange a set of certain numbers.

We put the cursor in the adjacent cell (left-right, it doesn't matter). Enter RAND () into the formula bar. We press Enter. We copy the formula to the entire column - we get a set of random numbers.

Now let's sort the resulting column in ascending / descending order - the values ​​in the original range will be automatically arranged in random order.

Dynamic sorting of a table in MS Excel

If you apply a standard sorting to the table, then when the data changes, it will not be relevant. You need to make sure that the values ​​are sorted automatically. We use formulas.

If you need to do a dynamic sorting in descending order, use the LARGE function.

You will need array formulas to sort text values ​​dynamically.

Subsequently, when adding data to the table, the sorting process will be performed automatically.

Excel allows you to analyze data by different parameters... The built-in analysis element is sorting. For the element to work, the information must be presented in the form of a list, formatted in a certain way.

The data in the table is divided into rows and columns. The columns contain values ​​of the same type. Numbers, dates, text. But not mixed. Sorting will not work if there are empty rows or columns.

Sorting data in Excel

In Excel, you can organize your lists:

• Ascending;
• descending;
• by user-specified parameters (by days of the week, months, etc.).

To sort in ascending order means to order the data:

• from 0 to 9;
• from A to Z;
• from A to Z.

Descending - in reverse order.

To invoke a custom sort order, you must perform the following sequence: "data" - "sort" - "order" - "custom list". A menu of this kind appears.

How to sort data in Excel table by column?

We have a study table with a list of employees and salaries:

Let's sort the salary in descending order, from highest to lowest. Select the required column - right button mouse - sorting option - from maximum value to minimum value - input:

The sorting range expands automatically. It turns out like this:

How to sort in ascending order in Excel?

Select the column - right mouse button - this time we indicate: from the minimum value to the maximum value.

Result:

How to sort alphabetically in Excel?

Select the column with last names - right mouse button - select: custom sorting.

"Sort by" - select the name of the column, the values ​​of which should be sorted alphabetically. We have - "full name". In sorting, we choose which parameters the tool will be guided by. It can be cell color, font, cell icon. In our example - "values" (analysis of values ​​in a cell). "Order" - from A to Z or from Z to A. Let's make a direct order. Click OK.

Sorting alphabetically:

How to sort by date in Excel?

Initial data:

We need to sort the goods by the date they arrived at the warehouse.

Procedure:

Check that the date column has the correct format ("Date"). Select the column and sort from old to new (new to old).

We choose from "old to new". List values ​​are arranged in chronological order.

You can sort the data in the range by days of the week, by months. To do this, in the Custom Sorting menu, select Custom List.

Sorting data by meaning

Let's say that the information in the table needs to be divided by positions, military ranks, certificates, customers and goods, depending on their importance for a particular enterprise, etc. For these purposes, there are custom lists that you can compose yourself.

Select a new list from the Custom List menu. We form a hierarchy of any objects. Separators can be commas or the Enter key.

When the hierarchical objects are added, click "Add".

And here is the result of sorting the training table by job title:

Sorting data by multiple columns

If you need to sort information by different conditions in different columns, use the Custom List menu. There is an "Add Level" button.

The data in the table will be sorted by several parameters. In one column, for example, ascending. In the other ("then by ...") - alphabetically, etc. As a result, the range will be formed as specified by complex sorting.

Levels can be added, deleted, copied, swapped using the "Up" - "Down" buttons.

These are the most simple ways sorting data in tables. Formulas can be used for these purposes. For these purposes, the built-in tools "SMALL", "STRING", COUNTIF "are suitable.

If you need to find a specific value in a table, then it is most convenient to do this in sorted data. Next, we will look at how to sort a table in Excel in descending or ascending order, the color of text or cells, and more.

To sort tables in Excel, use the menu item Sort & 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 values ​​and numeric values.

For correct sorting, all the cells to be sorted must have the same format, for example, all cells with numbers must be in numerical format. Also, there should be no extra spaces in front of the values, and there should be no hidden rows and columns in the sorted range.

Sorting values ​​in an Excel table begins by selecting 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 & Filter, and indicate "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. This is because, by default, the first row in a table is considered headers, i.e. column names, and is not sorted.

The range of sorted data is expanded 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 you want to sort and choose to sort in ascending or descending order, Excel will display a message prompting you to choose a sorting option. Option will be available "Automatically expand the allocated range", which will select the entire table, and Sort Within Specified Selection, which will sort only the selected column without affecting the data in the adjacent columns. In this case, sorting will again ignore the first cell.

To sort tables in Excel completely by all selected cells, you should go to the menu Sort & Filter select item "Custom sorting ...".

In this case, we will immediately see which part of the Excel table is allocated for further sorting.

To select the entire data area, follow in the window that appears "Sorting" uncheck the item "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, the data in which you want to sort. In the second column "Sorting" it is necessary to select the condition by which the sorting will be performed. Options available "Values", "Cell color", "Font color" and "Cell icon"... In our case, we will be sorting the values. Well, in the last column "Order" you can choose the sort order of values "Ascending", Descending and "Custom List"... Let's choose ascending. As a result, the values ​​of the middle column are sorted.

You can also customize and sort the individual column. In addition, in Excel, you can configure several levels of sorting. For example, let's sort the first column in ascending order, and then sort the result of the second column in ascending order. And the last third column will be 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" push the button "Add level", while the order of the levels matters.

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 their subsequent analysis.

You can sort the information alphabetically, by ascending or descending value, by date or by icon, by text or cell color. This is exactly what there will be a speech in this article.

Numbers

Everything is quite simple here. Take the following table as an example. Let's sort the data by column C in it. To do this, select it and on the "Home" tab click on the button Sort & 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 in descending order.

I have column C between two others that 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 selection" and click "Sort".

Alphabetically

It is done in the same way as described above. Select the desired range and press the button Sort & Filter... The items in the drop-down menu have changed. Choose either A to Z or 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 those cells in which they are written. Select them and on the Home tab look at the Number group. Either the "Date" format is best, short or long, or "(all formats)" - the date can be recorded different ways: DD.MM.YYYY, DD.MMM, MMM.YY.

This point is very important, because otherwise, the dates can be sorted simply in ascending order of the first two numbers, or by month in alphabetical order.

After that, select the desired range of cells and click on the button Sort & Filter... In 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 the text in the cells in the Excel spreadsheet or the cells themselves are painted in a different color. For example, let's take a column of numbers filled with different colors. Let's sort it so that the numbers are filled in red first, then green and black.

Select the entire range, click on the button Sort & Filter and choose from the menu "Customizable ...".

In the next window, uncheck the box if you selected them without top line, which is the header of the table. 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" - "Above". We have sorted the red numbers.

Now you need green numbers in the column. Click on the button "Add level"... All settings are the same, just choose "green". Click OK.

Our column is sorted as follows.

As you can see, the numbers are out of order. Let's sort the numbers in ascending order. Select the column, click Sort & 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", the order "Ascending"... Click OK.

Now our column is sorted by both text color and data ascending order.

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 Sort & Filter and choose "Customizable ...".

Let's sort the class in ascending order, and the GPA in the same way.

In the sorting window, put a tick in the box "My data contains headers"... In the "Column" section, select from the "Class" list, sort by "Value", and the order "Ascending".

To do the same for the average score, click on the button "Add level"... In the "Column" section, select "Average." Click OK.

The data in the table is sorted.

Now, in the "Name" column, fill in the cells with the boys in Blue colour, cells with girls in pink. In order not to do this for each cell individually, read the article How to select cells in Excel - it says how to select non-adjacent cells.

Let's sort this column by the color of the cell: 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 has the highest, the second has the lowest, and so on. That is, if we want to sort the data in the girls / boys table first, then by class, and then by the average score, we need to arrange the levels in this order.

Click on the button "Add level"... In the "Column" section, select "Name", sorting - "Cell color", order - "pink", "Top".

Now, using the arrows, move given line to the top of the list. Click OK.

The sorted data table looks like this.