Catalog

How to Sort Columns Alphabetically in Excel (Easy & Simple)

October 16, 2023 409 views

Sorting data is a fundamental operation in Excel that enables users to transform raw information into structured insights. While sorting columns alphabetically seems straightforward, complications can arise due to various factors. So, how do you sort columns alphabetically in Excel? This guide will provide insights and clarity to help users effectively approach this task.

How to Sort Columns Alphabetically in Excel?

If you want to know how to sort columns alphabetically in Excel, there are two methods that you can follow: the Sort Tool method and the VBA method. Follow the instructions below to learn how to use each technique.

Method 1: With Sort Tool

The Sort Tool looks at how to sort alphabetically in Excel with multiple columns.

Step 1: Select the entire data table you want to be sorted. Then, go to the “Data” tab from the Excel Ribbon.

Dataset in Microsoft Excel selecting the Data tab

Dataset in Microsoft Excel selecting the Data tab

Step 2: In the Sort & Filter section, click on “Sort”. That will open the Sort window.

Sort tab in Microsoft Excel with Sort dialogue box open

Sort tab in Microsoft Excel with Sort dialogue box open

Step 3: In the “Sort” window, under “Column,” select the column you want to sort the table by. That will be the Product Name column in this example.

“Sort dialogue box with Column selected to sort a column of the dataset”

“Sort dialogue box with Column selected to sort a column of the dataset”

  1. Under “Sort On”, select “Cell Values”.

Sort dialogue box with Sort On tab selected to sort dataset by Cell Value

Sort dialogue box with Sort On tab selected to sort dataset by Cell Value

  1. Under “Order”, select A to Z. You can also set this to Z to A to sort data in descending order.

“Sort dialogue box with Order tab selected to sort dataset alphabetically”

“Sort dialogue box with Order tab selected to sort dataset alphabetically”

Step 4: Select “OK”.

Selecting OK in the Sort dialogue box”

Selecting OK in the Sort dialogue box”

  1. After selecting OK, the column “Product Name” will be rearranged in alphabetical order.

Product Name column rearranged alphabetically”

Product Name column rearranged alphabetically”

Using the Sort Tool might be more manageable if you want a quick way to learn how to sort columns alphabetically in Excel. It is easy to follow along, making the process much smoother without any complicated steps.

Method 2: With VBA

Step 1: Go to the “Developer” tab in the Excel Ribbon. In the “Code” section, select “Macros”. That will open the Macro dialogue box.

“Macro dialogue box open in Microsoft Excel in the Developer tab”

“Macro dialogue box open in Microsoft Excel in the Developer tab”

Step 2: Enter a name for your macro under “Macro name”. Then, click on “Create”.

Macro dialogue box with macro name and Create execution selected

Macro dialogue box with macro name and Create execution selected

Step 3: This will take you to the VBA code editor.

Open and empty VBA code editor

Open and empty VBA code editor

Step 4: In the code editor, enter the code below:

Sub AZSort()

Dim R As Range

ActiveSheet.Sort.SortFields.Clear

Set R = Selection.Columns(1)

R.Select

R.Sort Key1:=R.Cells(1), Order1:=xlAscending, Header:=xlNo

End Sub

VBA code editor with code written inside of desired order to be executed

VBA code editor with code written inside of desired order to be executed

Code Explained:

  • VBA automatically creates the first and last lines and marks the Macro's start and finish. We named the macro "AZSort" in this example. If you've used a different name, that name will appear instead of "AZSort."

  • The following line defines the variable "R" as a "Range." You can change "R" to any other name; remember to stay consistent throughout the code, as this variable is addressed many times.

  • Next, we refer to the current sheet and then clear all previous sorting so that our new sorting can take its place. Then, we set the variable "R" to equal the first column of selected cells. "R.Select" specifies the variable "R."

  • Finally, the "R.Sort" line sorts the column. The "Header" parameter indicates whether the selection includes a header. Now that you understand the Macro you just wrote let's use it in the column.

Step 5: Go back to the Macro dialogue box. Select your Macro and then click on "Options." Set a shortcut key for the Macro and select "OK."

“Macro dialogue box showing keyboard shortcut key and macro name”

“Macro dialogue box showing keyboard shortcut key and macro name”

Step 6: Select the column that you want to sort.

Step 7: Press the keyboard shortcut for your macro on your keyboard, and the order will be executed.

Using the VBA method to sort Excel columns will be much slower to set up but much quicker to use once it’s done. That will be a good exercise for people learning to sort columns alphabetically in Excel. Once this is done, all you have to do in the future to sort columns is select the desired column and use your keyboard shortcut.

Why is My Column Not Sorting Alphabetically?

Now that we’ve looked at how to sort columns alphabetically in Excel let’s look at why it might not work for you.

Leading or Trailing Spaces in Data

The data in the column may contain leading or trailing spaces that influence the sorting order. In Excel, leading and trailing spaces are considered when sorting data alphabetically.

Numbers or Special Characters in the Column

The column may contain numbers or special characters that affect the alphabetical sorting. Excel will sort numbers and special symbols differently from letters.

Merged Cells within the Column

The column may have merged cells within it. If cells are connected, Excel may not sort the data correctly.

Custom Sort Order Applied

The column may have a custom sort order applied to it. Customized sort orders can be created in Excel to sort data in a specific way, which may override the alphabetical sorting order.

Hidden or Filtered Rows in the Column

The column may have hidden or filtered rows interfering with the sorting process. If hidden or filtered rows are within the column, Excel may need to sort the full range of data alphabetically.

A Free Advanced Office Software Choice: WPS Office

WPS Spreadsheet open with a dataset in it

WPS Spreadsheet open with a dataset in it

WPS Office is a comprehensive office productivity suite offering various features and functions for creating, editing, and sharing documents. One of the critical advantages of WPS Office is that most of its features are free, making it an attractive choice for users.

Price

One of the standout features of WPS Office is its affordability. Most of its functionalities are entirely free for users. That offers a cost-effective alternative to other office suites. However, a premium version is also available for users who require advanced features and additional support.

  • One-month subscription - $3.99

  • Six-month subscription - $18.99

  • 1-year subscription - $29.99

Capability

WPS Office provides many capabilities covering all significant office suite aspects. It includes a powerful word processor, a fully-featured spreadsheet application, and a dynamic presentation tool. These applications are compatible with Microsoft Office, ensuring smooth compatibility and easy document exchange.

Easy to use

The suite offers a familiar and fluid user experience, enabling users to adapt and start working efficiently and quickly. It also provides a rich collection of templates and pre-designed elements to streamline document creation.

Perfect customer service

WPS Office offers reliable and helpful customer support to assist users with technical issues or inquiries. They provide prompt responses to user queries, ensuring a smooth user experience. Their customer service team is dedicated to addressing user concerns and providing comprehensive assistance when needed.

WPS Office delivers essential office productivity functions and offers advanced features and AI functions. This includes document collaboration, PDF editing, and integrated cloud storage. These features enhance user efficiency and productivity while providing a cost-effective solution for individuals and businesses.

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs

  1. Why is Excel not sorting the entire column?

That happens when all the columns are not a part of one table, even though it is within one sheet. Make sure that the headings are only present in the first column. Select the complete table region only. Select the "Home" tab, then "Format Table As." Then, choose any options and check the heading available when prompted.

  1. Do numbers go first in alphabetical order?

Numbers do not go before letters in an MLA-cited work. Numbers are listed in numerical order. So, an organization with a number like '24/7 Service' would be alphabetized as if it said, 'twenty-four-seven service.'

Final Words

In conclusion, learning how to sort columns alphabetically in Excel can be a fun activity and a valuable skill to learn for the future. The two methods mentioned to sort columns in Excel are very effective to carry out.

However, if you're looking for something slightly more accessible, less complicated, and cheaper, WPS Office is the go-to office productivity app. Editing spreadsheets in WPS is smooth, with a user interface that simplifies the process. Its compatibility with Microsoft Office makes it easy to start in Excel and finish in WPS Spreadsheet.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.