How to Rearrange Columns Alphabetically in Excel (5 Methods)

If you are working with data or items of different categories in your  Excel worksheet, then you might need to rearrange columns in your Excel alphabetically. In this article, I’ll show you how to rearrange columns alphabetically in an Excel workbook.


Download Practice Workbook


5 Methods to Rearrange Columns Alphabetically in Excel

In this section, you’ll find 5 easy methods for rearranging columns in Excel alphabetically. Let’s try them now!


1. Alphabetically Rearrange Columns Using Excel Sort Feature

Sometimes, we may need to rearrange items on the basis of columns, in this section I’ll show you the way of reorganizing items based on column. Here, the dataset will be a list of some people with their purchased products.

Dataset for rearranging columns alphabetically

Now, let’s start the procedure.

  • First of all, select the column you want to rearrange, go to the Data tab and click Sort & Filter, and choose A to Z.

Rearranging columns in excel alphabetically

  • Click Expand the selection and click OK.

  • Now your column is rearranged alphabetically.

Rearranged columns alphabetically in excel

  • Similarly, you can reform your column by Sorting Z to A.

Rearranged columns in alphabetically descending order in excel

  • And you now will find your column in descending order.

That’s how we can rearrange a column in Excel either by ascending order (A-Z) or descending order (Z-A) as per our choice.


2. Filter Option to Rearrange Columns Alphabetically

For the previous dataset, we will now apply the Filter feature for alphabetizing cells of a column in Excel. So, let’s start.

  • Firstly, select the header of the columns you want to reform by alphabet.

Dataset for rearranging columns in excel using filter

  • Go to the Data tab and click Sort & Filter and then click Filter.

Rearranging columns in excel alphabetically using Filter

  • Now you will find a small arrow at the right end of the cells you have selected.

  • Click the arrow of the column you want to reorganize and a dialogue box will come. Select whether you want to Sort by A to Z or by Z to A. Then click OK.

Rearranging columns in excel alphabetically using filter

  • Your column will be sorted now.

Rearranging columns in excel alphabetically

Thus we can rearrange as many columns as we want alphabetically by using the Filter feature.


3. Reorder Column Headings Alphabetically

Sometimes we might need to sort the headings of columns alphabetically in Excel. Suppose, we have got a dataset of delivery detail of different types of products to different addresses. The headings of the columns are ID>Customer>Product>Weight>Address. We want to rearrange them into: Address> Customer> ID> Product> Weight.

Dataset for rearranging columns alphabetically in excel

  • Firstly, Select the whole Columns, go to Data Tab, select Sort & Filter and click the Sort option.

  • After that, a dialogue box will show up. Click Option on the box, choose Sort left to right, and click OK.

  • Then click the Sort by option and choose the position of the column heading (row number) and click OK.

Rearranging columns in excel alphabetically

  • After that, your column headings will be sorted alphabetically.

Rearranging columns in excel alphabetically

Thus, if we have random column headings, we can easily reform them alphabetically.


4. Reforming Multiple Columns with Excel SORT Function

In this method, we will reorganize columns using the SORT function. Suppose, we have a dataset of a different number of purchased products for different persons.

dataset for rearranging columns alphabetically in excel

We want to rearrange data firstly by person and then by purchased products. So, let’s check it.

  • Firstly, create another column where you want to get your result. Then apply the SORT function like the following formula.
=SORT(B7:D14,1,1)

  • Array is your data range (B7:D14)
  • [Sort_Index] is the column you want to sort on. (1)
  • [Sort_Order] is where you can specify the order. (1)
  • Then press ENTER and your output will be ready.

Rearranging columns in excel alphabetically

Thus you can reform your data alphabetically column by column using this method. You can give priority to one column and if that column has repeated text items, you can further sort them in the next column by this method.


5. Alphabetically Sorting Columns by Last Text

Suppose we have a dataset of names of some people and we want to sort them alphabetically by their last name.

So, let’s start.

  • First of all, apply a relevant formula to the desired cell. The formula will be formed using the RIGHTLEN, and FIND functions.
=RIGHT(B5,LEN(B5)-FIND(” “,B5))

Formula Breakdown

  • FIND(” “,B5) – explores the space character within the value of cell B5 and provides the position of that.

         Output: 5

  • LEN(B5) – provides the length of the string within B5.

         Output: 11

  • LEN(B5)-FIND(” “,B5) – becomes 11-5.

         Output: 6

  • RIGHT(B5,LEN(B5)-FIND(” “,B5)) – becomes RIGHT(B5,6) and extracts 6 characters from the right of the string within B5 (Mike Harbor).

         Output: Harbor

  • Then press ENTER and you will get the output.

  • Drag the formula to the desired cells and you will get the column of last Names.

  • Now, select the column, Go to Data Tab > Sort & Filter. Select Filter.

Rearranging columns in excel alphabetically

  • There will be a short arrow at the right of the header indicating Filter.

  • Click the arrow, Sort by A to Z, and click OK.

  • Finally, you will get the column of last names sorted alphabetically.

Thus if there are a number of words in the cells of a column, we can just separate them by the last texts and categorize them alphabetically.


Conclusion

In this article, we have learned how to rearrange columns in Excel alphabetically. I hope from now on you can undoubtedly reorganize columns alphabetically in Excel. However, if you have any queries or recommendations about this article, please don’t forget to leave a comment below. Have a great day!

Rafi
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo