Excel data sorting. How to sort data in Excel. Custom list or preset sort options
Sorting in Excel is a built-in data analysis feature. Using it, you can put last names in alphabetical order, sort the average score of applicants in ascending or descending order, set the order of rows depending on the color or icon, etc. Also, using this function, you can quickly give the table a convenient appearance, which will allow the user to quickly find the necessary information, analyze it and make decisions.
Sorting Rows in Excel
Click to add data, format charts, and navigate large spreadsheets. Don't worry about saving. The information leads you to the function you need. On large tablets, laptops, and desktops, you can view spreadsheets for free. The functions are divided into the following categories. Add-on and automation functions.
They allow you to process data from pivot tables or dynamic links. Used for advanced analysis in information cubes. They are used when you need to place the current date and time on a worksheet. You can get the day of the week, year, week number and much more.
Video on using Excel sorting
What can you sort?
Excel can sort data by text (alphabetical or vice versa), numbers (ascending or descending), date and time (new to old, and vice versa). You can sort by one column or several at the same time. For example, you could first sort all customers alphabetically and then sort them by the total amount of their purchases. In addition, Excel can sort by custom lists or by format (cell color, text color, etc.). Usually sorting is applied only by columns, but it is possible to apply this function to rows as well.
They allow you to make calculations related to the technical field, as well as models based on formulas used in engineering. They serve for calculation and financial reporting. They indicate the state characteristics of different parts of your table, this allows you to check whether a cell is empty or not, and other functions.
They are used to compare values and produce a true or false result according to the comparison. Search functions and links. They allow you to find data according to the criteria set in the search. Mathematical and trigonometric functions.
All specified settings for this option are saved with the Excel workbook, allowing you to resort the information when you open the workbook (if necessary).
Sorting data in Excel
Sorting in Excel can be divided into simple and complex. Simple sorting is ascending or descending.
They allow statistical calculations such as median, median variance, standard deviation, and more related functions. Text functions allow you to work with alphanumeric data, allow you to compare text, change formatting, change uppercase or lowercase letters, combine text, and much more.
Custom functions. When you sort information in a spreadsheet, you can view the data the way you want and quickly find values. You can sort a range or table of data on one or more columns of data; For example, you can sort employees first by department and then by last name.
There are 2 main types of sorting - ascending and descending
So, before you begin, you need to open Excel and fill in some information. For example, you can fill 10 cells with numbers from 1 to 10. Now you need to select the entire column (in this case, all 10 cells) and select “Data - Sorting” in the menu bar. A new window will open in which you need to specify how to sort the information, ascending or descending. You can, for example, select the “descending” item and click the “OK” button. Now the numbers will go from 10 to 1. You can open the sorting window again and select “ascending” - the numbers will go from 1 to 10. Also, this procedure can be performed simultaneously in 3 columns. Although it is better to perform such sorting.
Reducing automatic translation responsibilities: This article was translated using a computer system without human intervention. Because this article has been machine translated, it may contain vocabulary, syntax, or grammar errors. For reference, you can find the English version of this article. . Could you show us how? Thank you very much for your help, Best regards, Maria. You know that filtering works by keeping some of the information you're interested in while keeping the rest of the data hidden.
For example, you can create a table that will store information about the product in the warehouse. The table will consist of 3 columns: name, color, quantity. Products must be written so that there are several of the same category. For example, men's shoes are black (3 models), men's shoes are red (2 models), women's shoes are white (4 models), etc. The quantity can be any.
This table will be nothing more than field names in a header form and a few entries below. Automatic filters are available for quick and easy filtering. These are those arrows that appear in every field name, in the table header, and that require very little explanation to use.
From now on, you can use filters at your leisure. In the same arrow you have the All option to remove the application filter or you can also go to the Data, Filter, Show All menu to remove all the filters you have and make sure that the table ahead is complete. If you understand from the filter arrows, you can also sort ascending or descending by this field.
So, to enable the autofilter, you need to select the entire sheet and select “Data - Filter - Autofilter” in the menu bar. A small icon should appear in the cells with column names (name, quantity, etc.), and when clicked, a drop-down list will open. The presence of such an arrow means that the autofilter is enabled correctly. In this list, you can sort the data in descending or ascending order, specify that the table displays only the first 10 items (in this example, this option will not work) or display a specific product (for example, men's boots). You can also select the “Condition” item and specify, for example, that the program displays all products whose quantity is less than or equal to 10.
Advanced filters: three important benefits
You should also place yourself in a table cell and go to the Data tab, Sort and Filter and click the Filter button. This way you will have the famous arrows. However, advanced filters are not as straightforward or easy to use. So what are the reasons for using them? There are essentially three advantages of advanced filters over auto filters.
Add-in for sorting data in Excel
For example, I need a list of students who scored above 5 in math and above 7 in average, but only in the third trimester. The second advantage of advanced filters is the ability to retrieve the filter result in another place, that is, without “ruining” the original table. Thus, the table remains intact, and the result is reflected in another area, even on another sheet, although it seems a priori that it cannot. When you get the result in another area, you can also select the fields you want to display in the filtered table, this would be the third advantage in my view.
If the autofilter arrow is blue, this means that this column has already been sorted.
Sorting tricks
Let's say that the user has a table that contains a column with the names of the months of the year. And when you need to sort it, for example, in ascending order, it turns out something like this: August, April, December, etc. But I would like the sorting to occur in the usual sequence, i.e. January, February, March, etc. This can be done using a special setting for a custom sheet.
Filtering data in Excel
What is the range of criteria? This is a set of cells where the conditions for filtering the table will be set. It consists of the names of the fields you want to set and the conditions. For example, if I need to host students from Madrid for 30 years, there should be something like the following somewhere on the sheet.
Sorting by Columns
The word "City" and "Age" should be written the same way as in the title of the original table, so it is best to use copy and paste. In the image above you will say that you want students whose city is "Madrid" and "they are over 30 years old, but it is quite another to ask about Madrid" or who are over 30 years old, in this case the range of criteria. In this game you have to be careful with our way of speaking, which sometimes seems to go against these rules. How have you selected students from Madrid and Seville for over 30 years?
To do this, you need to select the entire table, open the sorting window and specify the “Custom list” item in the “Order” field. A new window will open where you can select the desired sequence of months in the year. If there is no such list (for example, the names of the months are in English), then you can create it yourself by selecting the “New list” option.
Sort by multiple columns
In the following way. You know that the asterisk is usually a wildcard and you can use it to replace any string of characters of indefinite length. Well, it's still a theory, but how is this process carried out? Let's say you have a table in Exercise 11 in front and you are asked for all products in the Drinks category over 15 euros. It is clear that we need to make a series of criteria with some field containing the price and another containing the category. In this case, go to the header of the table and copy the fields "Category" and "Unit Price" and paste them, for example, at the end of the table, in cell A below, enter the criteria so that they are the same as in the image.
Sorting data is not difficult at all. But as a result, you can get a convenient table or report for quickly viewing the necessary information and making a decision.
Sorting data in Excel is a tool for presenting information in a user-friendly form.
But, the question is, do you want the result to contain all the fields of the original table or a few columns that you selected? You already have all the necessary components to perform an advanced filter: an original table, a range of criteria, and a results zone with a heading of your choice.
To my taste there are details that, although not essential, will help you speed up the process, especially if you need to make multiple filters. It's best to specify the table range name, so you can use that name every time you need to filter and need to access the table. This way you just name the range. Great, with all this in place we will see the process of applying the advanced filter.
Numeric values can be sorted in ascending and descending order, text values can be sorted alphabetically and in reverse order. Options are available - by color and font, in any order, according to several conditions. Columns and rows are sorted.
Sort order in Excel
There are two ways to open the sort menu:
Frequently used sorting methods are presented with one button on the taskbar:
If you are anywhere in the sheet, you should go to the menu "Data", "Filter", "Advanced Filter", the following window will appear. You can do this by placing each field in a dialog box and selecting the range using your mouse without typing. The fact that the links appear with or without dollars doesn't matter, but note that to specify the range of the list, I didn't have to select anything, I just had to write the rank name we gave: "products."
And if you want, is it a different sheet than the original table? That is, what if you have a table on one sheet, but copies the header of the fields you want on another sheet? Many will tell me that you can't because you tried and the following warning appears.
Sorting a table by a single column:
If you select the entire table and sort, the first column will be sorted. The data in the rows will become consistent with the position of the values in the first column.
So, let's make the target sheet active. That is, the process begins with the recipient’s sheet. You have an original table or list in one sheet and you want the result to go to another sheet, well, put yourself in any cell in that other sheet before you go into the Data menu, filter, advanced filter. So you do the whole process from the destination sheet.
By the way, what happens if you don't put anything in the criteria range section? The good thing is that you will be doing an advanced filter without criteria, meaning you will get the result of all the records that are in the source table and the fields that you selected in “Copy to”.
Sort by cell color and font
Excel provides the user with rich formatting options. Therefore, you can operate with different formats.
Let’s create a “Total” column in the training table and fill the cells with values with different shades. Let's sort by color:
The program sorted the cells by accent. The user can independently choose the color sorting order. To do this, select “Custom sorting” in the list of tool options.
What if you don't create a header with the fields you want in the result? You will have a filter with records that match the range of criteria, but with all fields from the original table in the result of the specified cell. In both cases, you will find a Data, Sort, and Filter tab. There you will see an "Advanced" button.
If you came here without sticking your head into a computer, you learned something new. To find out, follow the information below. As a practical example for better compression, we have in the picture below a table containing a list of names of runners in a track and field event and with the classification of the runners in that case.
In the window that opens, enter the necessary parameters:
Here you can choose the order in which cells of different colors are presented.
Data is sorted by font using the same principle.
Sort by multiple columns in Excel
How to set secondary sort order in Excel? To solve this problem, you need to set several sorting conditions.
Note that the classification is not shown yet, in which case we need to know the order of arrival of each athlete without calculating their respective races. Order: In this case, if we insert the value 0, the value of the position of the number in the list will be returned. If the entered value is one, the number position value will be returned in stages.
In the example below, we use the formula so that the value is displayed gradually. Notice in the image above that the function returned the end position of the runner's marker. That is, based on the arrival times of all corridors contained in the time matrix, the function brought an ordered value gradually.
The program allows you to add several criteria at once to perform sorting in a special order.
Sorting Rows in Excel
By default, data is sorted by columns. How to sort by rows in Excel:
This is how you sort a table in Excel according to several parameters.
To reproduce the function for other athletes, you must lock the cells related to the time interval. In this case, we will use the dollar sign as shown in the image below so that there are no errors when copying the formula. To replicate the formula for other athletes, simply drag the first cell onto the others using the fill handle.
For better visualization and understanding, we can also use the Sort and Filter tool located on the Home tab in the Edit tool group. So, just select any cell with values in the table and select the sort order to form increment or decrement as shown below.
Random sort in Excel
The built-in sorting options do not allow you to randomly arrange the data in a column. The RAND function will handle this task.
For example, you need to arrange a set of certain numbers in random order.
Place the cursor in the adjacent cell (left or right, it doesn’t matter). Enter RAND() into the formula bar. 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 automatically be arranged in random order.
Dynamic table sorting in MS Excel
If you apply standard sorting to a table, it will not be relevant when the data changes. We need to make sure that the values are sorted automatically. We use formulas.
If you need to do dynamic sorting in descending order, use the LARGE function.
To dynamically sort text values, you will need array formulas.
Subsequently, when you add data to the table, the sorting process will be performed automatically.
You can sort the list alphabetically, numerically, or chronologically by up to three fields. For each column, you can specify ascending and 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 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 records 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 (corresponds 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.
Sorting data in Excel is a very useful feature, but should be used with caution. If a large table contains complex formulas and functions, then it is better to perform the sorting operation on a copy of this table.
Firstly, in formulas and functions the targeting in links may be violated and then the results of their calculations will be erroneous. Secondly, after repeated sorting, 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 you should carefully separate them, since this format is not acceptable for sorting.
Sorting data in Excel
What tools does Excel have for sorting data? To give a complete answer to this question, let’s consider it using specific examples.
Preparing a table to sort data correctly and safely:
Now our table does not contain formulas, but only the results of their calculation. The merged cells are also disconnected. All that remains is to remove the extra text in the headings and the table is ready for safe sorting.
To sort an entire table based on 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 reference 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 we copy a table to a separate sheet, we transfer only its values using paste special. But the 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 copy of the table now contains the 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; “Sorting” – Cell color; “Order” – red, on top. And click OK.
At the top we now have the worst performing net profit figures.
Note. Then you can select the range A4:F12 in this table and repeat the second step of this section, only specify pink on top. Thus, the cells with color will go first, and then the normal ones.
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 by 64 columns simultaneously.
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
Sort 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.
Let's assume that we have prepared a report on sales and financial results by sales channels and managers of the following type:
It's eye-opening, isn't it? To make it easier to read this table, let’s decide what information is important for us and what is not.
Let us immediately note 2 important points.
1. As a rule, tables of this kind are created using formulas. Before sorting you need be sure to remove all formulas from all cells, otherwise it will simply not be possible to carry out the operation, and if it succeeds, then all the values will change, because formulas will reference completely different cells than they should.
2. If in the future we need remove sorting and return to the previous table view, you should take care of this at this stage. Let's number the rows of the original table in case we want to return to it, and then we will be able to sort the rows by numbering.
How to remove formulas from all worksheet cells
In order to remove all formulas at the same time, you need to:
1. Select the entire area of the sheet, for which you need to left-click on the upper-left corner of the gray field:
2. Copy the selected area (without deselecting the area, right-click the menu and select “Copy”)
3. Use Paste Special (without deselecting the area, right-click the menu and select “Paste Special” -> “Values”)
Well, now you can delete or hide extra columns without fear of changing the report.
Before proceeding directly to sorting, you need to check that the table being sorted does not contain merged cells in the header or elsewhere. In this case, Excel will swear and write: “This requires that the cells be the same size.”
If you do not intend to unmerge the header cells and change the table, you can move the column to be sorted to the leftmost row and sort it using the sort buttons.
For example, we want to sort the table in descending order of revenue. Select the rows to be sorted by the gray field without a header and click on “Sort in descending order”:
WARNING: This applies to only merged cells in the header. If there are merged cells within a table, Excel will not sort those rows. We'll have to remove the association.
If the table does not contain merged cells, you can sort directly within the table without moving the sorted column.
To do this, select the entire table area along with the header, find “Data” in the main menu and select “Sorting”:
In the pop-up window, Excel will offer to sort by all the indicators listed in the header. We will select “Net Profit”:
If we combine this type of sorting with sorting through an autofilter, we can sort the data by departments, managers and clients. Let's add a sorting column and sort the data by manager:
As you can see, as a result, we have a convenient report that very clearly demonstrates which managers bring the most profit and who works with unprofitable clients.