How to alphabetize a table in excel. Linked drop down list in Excel alphabetically

How to make a drop down listExcel alphabetically to select a last name by letter of the alphabet? When there are many surnames in the drop-down list, it is convenient to make an alphabet in the drop-down list, and then, by selecting the desired letter, select the surname. For this you need to do linked Excel dropdown lists.
For example, on the first sheet we have the main table, into which we will insert a drop-down list with last names.
On the second sheet excel books Let's make a list of names. Sort this list alphabetically. The list is like this.
How to make out big list with duplicate last names a list without repetitions for a drop-down list, see the article "Creating a list in Excel without repetitions".
Now let's give a name to this list. How to assign a name to a range, read the article "Range in Excel". We named the range - "Surnames".
In order for the range boundaries to change automatically when adding or subtracting surnames, you need to make it dynamic. See the article "To automatically resize an Excel table".
Now we need to create a list of letters that begin with the last names in our list.
How to write the alphabet in Excel with a formula.
First, in column C, we write the usual alphabet. This can be quickly done with a formula. In cell C, write the formula.
=CHAR(192+STRING(Z1)-1)
192 is the code for the letter A. Copy the formula down the column to the letter Y. The alphabet is ready.
Now in the next column you need to write only those letters with which the names in our list begin. Here will help usExcel array formula. In cell D2, we wrote such a long formula.
=IFERROR(INDEX($C$2:$C$33,SMALL(IF(COUNTIF
(Last names;$C$2:$C$33&"*")>0;STRING($C$2:$C$33);"");
STRING(Z1))-ROW($C$1));"")
This is an array formula, p.e. we press not just "Enter", but the key combination "Shift" + "Ctrl" + "Enter". The formula will be in braces. Drag the formula down the column. We got it like this.

Everything. Now in cell D1 we select a letter, and in cell E1 a drop-down list of surnames only for this letter will appear. For example. or

There is a category Excel users, which works with books containing, instead of 3 standard sheets, a much larger number of them. Someone in these books of estimates, the numbers of which are the names of the sheets, someone has acts of work performed by contractors, someone has fuel consumption of fixed-route taxi cars, someone has an assembly of economic calculations, and someone electrical characteristics technological equipment recorded daily on separate sheet(or generally every hour). Well, if the sheets of such a book are sorted in advance at least somehow, but if not? Even a simple search for the desired sheet in the book becomes far from a fun activity. So I want to line up the sheets alphabetically, but in standard Excel there is no such possibility.

I present to you an easy-to-use add-in (macro) for Excel, which allows sort sheets workbook Excel both ascending and descending . If someone has come across not quite correct sorting, when Sheet11, and not Sheet2, immediately follows Sheet1, they may not worry. In this add-in, this problem is solved, it will sort sheets with the same quality, the names of which consist of letters, numbers, and a combination of letters, numbers, spaces and symbols. And it will do it even with hidden sheets. The add-in correctly and quickly sorts sheets with names consisting of only numbers, such as 1, 2, 3, 4, 5, as well as sheets with names like 1-1-1, 1-1-2, 1-1 -3.

The add-in is launched by a button displayed in the Excel menu, when pressed, the user sees a dialog box with which he can choose how to sort the sheets in ascending or descending order. There is alsosort sheets by tab color and custom lists.

It is also possible. The add-in is easy to install, and for those who have not yet encountered the installation of add-ins in Excel, there is step-by-step instruction. In my opinion this worthy alternative manual drag and drop of sheets.

AT this example Let's take a look at the fastest effective methods sorting data in Excel according to various criteria: in ascending or descending numbers, by dates from old to new and from new to old, alphabetically from A to Z, as well as vice versa alphabetical order.

Sort rows by column in Excel

The figure below shows a selection of information from the database on the company's employees:

Let's say we need to sort the table with respect to the "Bonus" column in descending order of amounts. To solve this problem, we will use the simplest (basic) sorting tools in Excel.

Move the keyboard cursor to any cell in column G (you can even go to the heading of column G1). And Then select the tool: "HOME" - "Editing" - "Sort and Filter" - "Sort Descending".

As a result, the data was ordered and organized in a certain structure:

As it turned out, the employee "Eugene Prutkiy" received the highest award.

Attention! The list to be sorted must not contain merged cells.

Note. The tool for basic sorting of ranges is also available on the tab: "DATA" - "Sort in ascending/descending order".

And built into the drop-down menus of table headers in autofilter or smart table mode:

Also built into context menu, which appears when you click right click mice:

How to remove sorting in Excel

If the table needs to be restored to its original form, then before each sort, create a row numbering column. In this example, this is column A (No. p / p). It is often necessary to bring a table back to its original form after performing numerous and complex sortings of data on it according to different columns, criteria, or conditions. Then it is enough to sort by the column with row numbers so that the table takes its original form.

How to sort a column in Excel

Sometimes you need to perform an independent sort only within one column of a table without binding to or changing other columns. For example, column B is "Name". This problem can be solved just as easily:

1. Select the entire column B by left-clicking on the sheet column heading.
2. Select the tool: "HOME" - "Editing" - "Sort and Filter" - "Sort Descending".
3. In the appeared dialog box "Detected data in unspecified range", select the option: "sort within the specified selection". And click on the Sort button.

Note that the cell values ​​in the remaining columns remain unchanged:

This method should be used only as an auxiliary.

Sorting on multiple columns in Excel

How to set up sorting in Excel? Let's say we want to sort employees by date of birth (oldest to newest) according to each city (place of birth). For this:

The table has acquired the following structure:

First, its values ​​are sorted relative to city names in alphabetical order. Further relative to each city, dates of birth are distributed from oldest to newest.

If we work only with a table fragment where there are no column headers, but only a range of cells with current values, as shown in the figure below:

Then in the dialog box of the Custom Sort tool, you should disable the option "My data has column headings". As a result, as criteria for each first drop-down list of the "Column" section in each level, the headings of the columns of the worksheet will be used. Excel sheet(Column A, Column B, C, etc.). And when all parameters are applied, all rows and cells of the original range (table fragment) will be included in the sorting, which is the correct result in this case.

Note. The button for calling the tool for custom sorting is also available on the "DATA" - "Sort" tab.

Tables of data we receive from different employees in most cases, they are not streamlined and it is difficult to work with them without organization. There is a need to sort: text value alphabetically, numeric in ascending order, and dates from oldest to newest. Manually sorting all columns individually is not best solution for this task. Especially if there are more than a thousand columns. Therefore, it is better to use a special built-in Excel tool- custom sorting. This tool automatically sorts data by several columns at the same time. It is so flexible that it can be applied to almost any sort with any level of complexity.

In this article, I will show you how to sort data in Excel by multiple columns, by column headings in alphabetical order, and by values ​​in any row. You will also learn how to sort data non-standard ways when sorting alphabetically or by numerical value is not applicable.

I think everyone knows how to sort by a column in alphabetical order or ascending/descending order. This is done with the click of a button. AND I(A-Z) and Z-A(Z-A) in section Editing(Editing) tab home(Home) or in the section Sort and filter(Sort & Filter) tab Data(Data):

However, sorting in Excel has many more customizable options and modes of operation that are not so obvious, but can be very convenient:

Sorting on multiple columns

I will show you how to sort data by two or more columns in Excel. The operation of the tool is shown in Excel example 2010 - this is the version installed on my computer. If you're using a different version of the application, you shouldn't have any problems because sorting works in much the same way in Excel 2007 and Excel 2013. The difference can only be seen in the color dialog boxes and the shape of the buttons. So let's get started...

Sorting data by multiple columns in Excel was not difficult at all, right? However, in the dialog box Sorting(Sort) lies significantly more possibilities. Later in this article, I'll show you how to sort by row instead of column, and how to sort data on a worksheet alphabetically by column headings. You will also learn how to sort data in non-standard ways when sorting alphabetically or by numerical value is not applicable.

Sort data in Excel by row and column headings

I believe that 90% of the time Excel sorts data by value in one or more columns. However, sometimes there are not simple sets data to be sorted by row (horizontally), i.e. reorder columns from left to right based on column headings or values ​​in a particular row.

Here is a list of cameras provided by your local dealer or downloaded from the internet. The list contains a variety of features, specifications, and prices, and looks something like this:

We need to sort this list of cameras according to the parameters that are most important to us. For example, first of all, let's sort by model name:

As a result of sorting, you should get something like this:

In our example, sorting by column headings has no real practical value and is only done to show you how it works. In the same way, we can sort our list of cameras by the row that specifies dimensions, resolution, sensor type, or any other parameter that we deem more important. Let's do another sort, this time by price.

Our task is to repeat steps 1 - 3 described above. Then at step 4, instead of the line 1 select a line 4 , which contains retail prices (Retail Price). As a result of sorting the table will look like this:

Please note that the data was sorted not only in the selected row. Whole columns are swapped, but the data is not shuffled. In other words, the screenshot above is a list of cameras, sorted from cheapest to most expensive.

I hope it's now clear how sorting by row works in Excel. But what if our data needs to be sorted in a non-alphabet and non-ascending/descending order?

Random sorting (custom list)

If you need to arrange the data in some special order (not alphabetically), then you can use the custom lists built into Excel or create your own. With built-in customizable lists, you can sort by days of the week or months of the year, for example. Microsoft Excel offers two types of ready lists- with abbreviated and full names.

Let's say we have a list of weekly chores around the house and we want to sort them by day of the week or by importance.

Ready! Now household chores are ordered by day of the week:

Comment: If you plan to make changes to this data, keep in mind that new data added or existing data changed will not be sorted automatically. To redo sorting, click the button Repeat(Reapply) section Sort and filter(Sort & Filter) tab Data(Data).

As you can see, sorting data in Excel according to a custom list is not a difficult task at all. Another trick we need to learn is sorting data into our own custom list.

Sort data by your own custom list

Our table has a column priority- it indicates the priorities of tasks. To use it to organize your weekly tasks from most important to least important, follow these steps:

Repeat steps 1 and 2 from the previous example. When the dialog box opens Lists(Custom Lists), in the column of the same name on the left, click NEW LIST(NEW LIST) and fill in desired values field List Items(List entries). Carefully enter the elements of your list in the exact order in which they should be sorted.

Click Add(Add), and the list you created will be added to the existing ones. Next click OK.

This is what our list of household chores looks like, sorted by importance: