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.
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.
- Click Expand the selection and click OK.
- Now your column is rearranged alphabetically.
- Similarly, you can reform your column by Sorting Z to A.
- 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.
- Go to the Data tab and click Sort & Filter and then click 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.
- Your column will be sorted now.
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.
- 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.
- After that, your column headings will be sorted 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.
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.
- 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.
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 RIGHT, LEN, and FIND functions.
- FIND(” “,B5) – explores the space character within the value of cell B5 and provides the position of that.
- LEN(B5) – provides the length of the string within B5.
- LEN(B5)-FIND(” “,B5) – becomes 11-5.
- RIGHT(B5,LEN(B5)-FIND(” “,B5)) – becomes RIGHT(B5,6) and extracts 6 characters from the right of the string within B5 (Mike 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.
- 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.
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!