How to sort data in an excel table. How to Use the Data Sort Feature in Microsoft Excel

If the data is text, it can be sorted alphabetically (“from A to Z” or “from Z to A”). If the data is numeric, you can sort it in ascending or descending order. If a data range has a row or column that contains time or date data, you can sort it in forward or reverse chronological order. It is also possible to sort pre-formatted data by formatting elements.

You can sort the data by one condition (for example, sorting a list of employees by last name) or several (for example, sorting a list of employees by position, and within each position sort the last names in alphabetical order). Data can be sorted by a column (or multiple columns) or by row.

Sort by one criterion

Step by step steps:

In the column by which sorting is to be performed, you need to select any cell (you do not need to select the entire column).
On the tab Data find a group of commands.

Note that the letters on this button indicate only the sorting direction, and the appearance of the button remains the same for both text and numeric data.

There is another convenient way to sort data: by right-clicking on the cell of the column by which sorting will be performed, select the item in the context menu Sorting, and then - the required sorting option.

Step by step steps:

Select one cell from the data array being sorted.

If a data range contains empty columns or rows, then Excel automatically perceives them as the boundaries of the sorted data array. In this case, you should select all the data to be sorted.

On the tab Data find a command group and select a command from it Sorting .
Sequentially set sort levels (determined by the column name).

By clicking on the arrow next to the three fields ( Column, Sorting, Order) you must select:

The name of the column to sort.
Type of criterion (depending on whether sorting will be done by data values in the column, or by cell design, or by cell icon).
Sort order (descending or ascending).

If the column selected for sorting contains the names of months or days of the week, then in the list of fields Order you can select the option Custom list and in a new window, select one of the proposed sorting options.

Sort by formatting

Often, to analyze data, cells (or fonts) are filled with color. Sorting can also help you organize data based on formatting.

Step by step procedure:

Click on any cell in the column that you want to sort by.
On the tab Data select group and then select team Sorting.
In field Column specify the column by which the sorting will be performed.
In field Sorting Select sorting criterion from the pop-up menu: cell color, font color or cell icon.
Field Order contains two drop-down lists. In the first, you need to select the type of criterion, and in the second, the placement of cells sorted by this criterion (line Above or From below ).
If necessary, add another sorting criterion in the window Sorting you need to select a button Add level.

You can also use the “Copy Level” command, replacing the previous value in the “Order” field with a new one.

After selecting sorting, press the button OK.

You can sort the list alphabetically, numerically, or chronologically by up to three fields. For each column, you can specify an ascending or descending sort order.

Ascending sort order:

The numbers will be sorted in order from smallest negative to largest positive number.

Date and time values will be sorted from earliest to latest value.

The text will be sorted alphabetically. In this case, the numeric values specified as text will be located first.

When sorting Boolean values in ascending order, the value FALSE will be displayed first, followed by the value TRUE.

The error values will be sorted in the order in which they were encountered (from a sorting perspective, they are all equal).

Empty cells will appear at the end of the sorted list.

Descending sort order:

In the case of a descending sort order, Excel sorts the records in the reverse order described above. Only empty cells will still be shown at the end of the list.

Custom sort order:

Instead of ascending and descending sort order, you can also use a custom sort order determined by a specified custom list.

Sorting a list

To sort a list, place the cell pointer inside the list and execute the Data – Sort command.

Excel automatically selects the list and displays the Sort Range dialog box, where you can specify sorting options.

You can set sorting by up to three list fields and specify a different sort order for each field.

Excel automatically recognizes field names if the format of the cells containing the names is different from the format of the cells containing the data.

Sort Range dialog box.

If the range selection performed by the program is not entirely correct, set the switch at the bottom of the dialog box to the desired position (Identify fields by “labels (first row of the range)” or “sheet column designations”).

The range and Sort Options settings you specify in the Sort dialog box will be saved and displayed in the dialog box the next time you open it.

You can also choose to sort range columns instead of rows. To do this, click the “Options” button in the “Sorting Range” dialog box and in the “Sorting Options” dialog box, in the “Sort” group, set the switch to the “Range Columns” position.

26. Filtering data in Excel.

Filtering data in the list allows you to display only those records that meet specified conditions.

Autofilter

When applying an autofilter, the field names will be supplemented with drop-down lists from which you can select the desired field value or set a custom criterion.

Inserting an AutoFilter

1. Place the cell pointer inside the list.

2. In the Data – Filter submenu, select the “Autofilter” command. Arrow buttons will appear next to the field names, which you can click to open the list.

3. Open the list for the field whose value you want to use as a filter (selection criterion). The list will show the values of the cells of the selected field.

4. Select the desired item from the list. Only those entries that match the specified filter will be displayed on the screen.

5. If necessary, select the required element from the list of another field. Only those records that match all specified filtering conditions will be displayed on the screen (the conditions of individual fields are combined using the logical operation “AND”).

Buttons for opening autofilters for fields for which filtering conditions are specified are colored blue.

If you selected one or more columns before executing the AutoFilter command, drop-down lists will be added only to the corresponding fields.

To display all list entries on the screen again, execute the “Display all” command from the Data – Filter submenu.

You can remove the filtering criterion for an individual field by selecting the “All” item in the autofilter list for this field.

To deactivate the autofilter function (remove drop-down lists), select the command Data – Filter – Autofilter again.

Applying a custom autofilter

Using a custom autofilter, you can connect individual record selection conditions using logical operators.

Insert an autofilter into the list by selecting the command Data – Filter – Autofilter.

Open the autofilter list for the desired field and select the element (Condition) in it.

In the “Custom AutoFilter” dialog box that opens (Fig. 6.3.27.), specify the first criterion.

Select a logical operator that combines the first and second criteria.

“Custom AutoFilter” dialog box.

You can set one or two criteria for an individual field in a custom autofilter. In the latter case, they can be combined with the logical operator “AND” or “OR”.

Set the second criterion.

Click the “OK” button. Excel will filter the records based on the criteria you specify.

Advanced filter

To set complex conditions for filtering list data, Excel provides a so-called advanced filter to help the user.

Range of criteria

The criteria can be set in any free space on the worksheet. In the criteria range you can enter and combine two types of criteria:

Simple criteria: the program will compare the contents of the fields with the specified criterion (similar to using an autofilter).

Calculated criteria: In this case, you can also specify the values returned by the formula that are not listed.

When specifying simple criteria, you must first specify field names in the criteria range, and you can enter only those field names for which you are specifying criteria.

The line(s) immediately below the field names line is where you specify the criteria. Excel is not case sensitive when specifying criteria. Can you use wildcards when specifying criteria? And *.

All criteria specified on one line must be executed simultaneously (corresponding to the logical operator “AND”). To specify the connection of criteria using the “OR” operator, specify the criteria on different lines.

Applying an advanced filter

Once you have created a criteria range, you can run the advanced filter and filter the list data.

1. Place the cell pointer inside the list. In this case, Excel will automatically recognize the list range and present a link to the range in the dialog box.

2. Execute the command Data – Filter – Advanced filter. Place the input cursor in the “Condition Range” field and highlight the corresponding range in the worksheet.

3. Close the dialog box by clicking the “OK” button. The screen will now display records that meet the specified criteria.

You can only apply one advanced filter per worksheet.

If you do not want duplicate records to be displayed as a result of applying the advanced filter, select the Unique records only check box in the Advanced Filter dialog box.

To set the display of all list entries in the worksheet after filtering, run the command Data – Filter – Display All.

When sorting data in Excel, you may encounter the need to sort data into several columns at the same time. Today I’ll tell you how to do this.

As an example, let's take a table that contains a list of products with their cost and expiration date. The task is to sort the products in alphabetical order and by expiration date.

Select all the cells of the table to which we will apply sorting. In our case, you can simply stand on any cell within the range. Next on the tab home click on the icon Sorting and Filter and from the drop-down menu select the item Custom sorting.

The sorting settings window will open. The first thing you need to do is make sure that the checkbox is checked My data contains headers. This setting allows you to remove the first row from the range and not participate in future filtering.

Now we will add columns by which the sorting will take place. The first, in our case, is the column Products. In field Sorting we exhibit Meaning and install Order from A to Z.

Now the products will be sorted first by the first column and additionally by the second. In this case, the second sorting does not affect the first. You can sort by more columns in the same way.

By the way, sorting can also be done by rows. I will tell you how to do this in the next lesson.

In the two previous lessons we removed duplicates. You can read about this too. In this lesson we will implement search for duplicates.

In this lesson I will tell you how to attach a header to each Excel page. This may be necessary if you are working with large tables. Usually, when they are printed, documents with a large number of pages are produced, and in order to make it convenient to work with them, it is necessary to save the table header on each sheet.

In this lesson I will tell you how to put a plus sign or zero before a number at the beginning of a cell in Excel. Let's imagine a situation where you need to enter a phone number in a cell in the format "+7 987...". Normally, Excel will simply remove this plus sign.

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

You can sort information alphabetically, by ascending or descending values, by date or by icon, by text or cell color. This is exactly what this article will discuss.

Numbers

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

I have column C located between two others, which 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 allocation" and click "Sort".

Alphabetically

It is done according to the same principle as described above. Select the desired range and press the button "Sorting and 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 the cells in which they are written. Select them and on the “Home” tab look at the “Number” group. The best format is “Date”, short or long, or “(all formats)” - the date can be written in various ways: DD.MM.YYYY, DD.MMM, MMM.YY.

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

After that, select the desired range of cells and click on the button "Sorting and 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 there is text in cells in an Excel table or the cells themselves are painted in different colors. For example, let's take a column of numbers painted in different colors. Let's sort it so that the numbers colored in red come first, then green and black.

Select the entire range and click on the button "Sorting and Filter" and select from the menu "Customizable...".

In the next window, uncheck the box if you selected them without the top line, which is the table header. 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 color” - “Top”. We sorted the numbers in red.

Now you need the column to contain green numbers. Click on the button "Add level". All settings are the same, just select “green color”. Click OK.

Our column is sorted as follows.

As you can see, the numbers are not in order. Let's sort the numbers in ascending order. Select the column and click "Sorting and 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”, order "Ascending". Click OK.

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

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

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

In the sorting window, check the box "My data contains headers". In the “Column” section, select “Class” from the list, sort by “Value”, and order "Ascending".

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

The data in the table is sorted.

Now in the “Name” column, color the cells with boys blue and the cells with girls pink. To avoid doing this for each cell separately, read the article on how to select cells in Excel - it tells you how to select non-adjacent cells.

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

Click on the button "Add level". In the “Column” section, select “Name”, sort – “Cell Color”, order – “pink”, “Top”.

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

The table with sorted data looks like this.

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

To sort tables in Excel, use the menu item "Sorting and Filter" on the home tab. The simplest sorting given in an Excel table is ascending or descending, which can be applied to both text values and numeric values.

For proper sorting, all cells being 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 or columns in the sorted range.

Sorting values in an Excel table begins with selecting the column to be sorted. It is enough to even place the cursor in the first cell of the column. In our example, select the first cell of the second column and select from the menu "Sorting and 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 remains in its place. 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 sorted data expands automatically, i.e. Excel automatically selects the entire table and sorts the data into rows according to the selected column. If you select all the cells in a column that you want to sort and select ascending or descending sort, Excel will display a message asking you to select a sort option. Option will be available "Automatically expand the selected range", which will select the entire table, and "Sort within specified selection", which will allow you to sort only the selected column without affecting the data in adjacent columns. In this case, the first cell will again not be taken into account when sorting.

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

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

To select the entire data area, in the window that appears, "Sorting" remove the check mark from 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 in which you want to sort the data. In the second column "Sorting" You must 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 sort the values. Well, in the last column "Order" you can select the sort order of values "Ascending", "Descending" And "Custom List". Let's choose in ascending order. As a result, the middle column values are sorted.

You can also customize the sorting of an individual column. In addition, you can configure several sorting levels in Excel. For example, let's sort the first column in ascending order, and then sort the result of sorting the second column in ascending order. And we will sort the last third column first by cell color, and then by font color. To add a new level you need to in the window "Sorting" press the button "Add level", and the order of the levels matters.