How to display data alphabetically in Excel

When compiling tables in Excel, it is more convenient to arrange the data in alphabetical order. Such sorting will help to avoid confusion, increase the presentability of the table array. The main ways of arranging information in cells alphabetically will be discussed in this article.

Method 1: Sort buttons

In Microsoft Office Excel, using the built-in tools, you can quickly sort the desired plate. The process for implementing this method is as follows:

1. Select LMB the range of cells in the table that you want to sort alphabetically.

2. In the “Home” section, expand the “Editing” tool by clicking on the arrow below.
3. In the “Sort and Filter” subsection, select the editing option “From A to Z”. This is the alphabetical order.

4. In the warning window, put the toggle switch in the line “Automatically expand the selected range.”

5. Check result. The information in the highlighted area of ​​the table must be sorted alphabetically.

Pay attention! If you want to display information in Excel alphabetically, you can manually rearrange the lines on your own. However, this process will take a long time.

Method 2: Use the Custom Value Sorting feature

Such a tool allows the user to set individual parameters for sorting. With its help, the data is displayed more presentably, and individual elements of the range do not move down. To use custom sorting to display information in a table array in alphabetical order, you need to follow a few simple steps.

1. Select the entire table.

2. In the same way, in the “Home” tab, click on the “Editing” button, but this time select the “Custom sorting …” option.

3. In the window that appears, in the “Sort by” field, you must specify the column for which editing will be applied.

4. In the “Sort” field, select the “Cell Values” mode, and in the “Order” line, select the option “A to Z”.

5. Click on “OK” at the bottom of the window and check the result. The cell values ​​should now be displayed alphabetically.

Important! If necessary, the information in the array can be sorted in reverse order, i.e. “From Z to A”.

Method.3 Using a special formula

The methods discussed above are not XNUMX% effective. They will need to be repeated every time after the creation of any table in order to affect the location of the data in it. It is more convenient to use a sorting formula that will operate throughout the entire process of filling the table array and automatically correct information in the entire range of cells. The method of using the function consists of several stages, each of which will be discussed in detail below.

Stage 1. Writing a helper formula

This formula will work on the principle of encoding analysis. It is created like this:

1. Select the desired range of cells and enter a new name for it in the field under the main interface of the program window.

2. Make sure the table name has changed.
3. In the input line, write the formula “COUNTIF (name, element)”. After specifying the main words in the formula, you will need to select a specific range of cells, and then click on “Enter”.

4. In the brackets of the written formula, insert the symbol “”<="&" before the name of the first element of the table.

5. Stretch the cell with the calculated value to the end of the table.

This completes the first stage of creating a sort formula, you need to move on to the next step.

Additional Information! After stretching the formula, it is recommended to change the name of the original table.

Step 2: Create a sorting formula

After creating an automatic position detection, you can start writing the main function according to the following step-by-step algorithm:

1. Place the mouse cursor in the first cell of the empty column that follows the main array and enter the formula “=MATCH(ROW(A1)”. The entered operator will look for the position of the string. Instead of A1, you can indicate the number of a specific cell, depending on the location of the table on the Excel worksheet.

2. Click LMB on the “Formulas” section in the upper interface of the main menu of the program.
3. Expand the “Defined Names” option by clicking on the arrow at the bottom.
4. In the context menu that appears, click on the “Use in formula” option.
5. The next step is to select a range of cells with an auxiliary function and in the “Match Type” subsection, select the “Exact match” option.

6. Select the original formula in additional brackets and write the word “INDEX” in front of them. This is a function that defines an array of names.

7. Check result. In the cell of the previously specified column, the first word from the original list alphabetically should appear.
8. Stretch the formula to all cells according to the scheme discussed above.

Pay attention! After stretching the formula, all the original data will be arranged in alphabetical order.

Conclusion

Thus, in Microsoft Office Excel there are several ways to alphabetize data in a table array. The simplest and most common of them have been described above.