HomeApplicationOffice information

How to Sort Microsoft Excel Columns Alphabetically

Microsoft Excel is a great tool for organizing your information. Here's a guide to a basic but extremely useful function, sorting your data alphabetically. Format the header row. The header row is the top row of your spreadsheet, with the...
Method 1 of 2:

Sorting Alphabetically

  1. How to Sort Microsoft Excel Columns Alphabetically Picture 1How to Sort Microsoft Excel Columns Alphabetically Picture 1
    Format the header row. The header row is the top row of your spreadsheet, with the names of your columns. Excel will sometimes sort this row thinking it is part of your data, especially if your spreadsheet is entirely text. Here are a few ways to prevent this:
    1. Format your header row differently. For example, bold the text or make it a different color.[1]
    2. Make sure there are no blank cells in your header row.[2]
    3. If Excel still screws up the sort, select the header row and use the top ribbon menu to click Home → Editing → Sort & Filter → Custom Sort → My data has headers.[3]
  2. How to Sort Microsoft Excel Columns Alphabetically Picture 2How to Sort Microsoft Excel Columns Alphabetically Picture 2
    Select the column you wish to alphabetize. You can click the header cell for that column, or the letter above it (A, B, C, D, etc.).
  3. How to Sort Microsoft Excel Columns Alphabetically Picture 3How to Sort Microsoft Excel Columns Alphabetically Picture 3
    Open the Data tab. Click Data in the top menu to view Data options in the ribbon menu above your spreadsheet.
  4. How to Sort Microsoft Excel Columns Alphabetically Picture 4How to Sort Microsoft Excel Columns Alphabetically Picture 4
    Look for the Sort and Filter section. The ribbon menu is divided into related areas, with the name under each one. Look for the area labeled Sort & Filter.
    1. If you don't see it on the Data menu, try returning to the Home tab and looking for a Sort & Filter button in the Editing section.
  5. How to Sort Microsoft Excel Columns Alphabetically Picture 5How to Sort Microsoft Excel Columns Alphabetically Picture 5
    Click the A → Z button. To sort the spreadsheet in alphabetical order, just click the A → Z symbol in the Sort and Filter section. This will rearrange the spreadsheet in alphabetical order of the selected column. On most versions of Excel, this button is in the top left corner of the Sort and Filter section.
    1. To sort it in reverse alphabetical order instead, click the Z → A symbol instead.
Method 2 of 2:

Sorting by Last Name

  1. How to Sort Microsoft Excel Columns Alphabetically Picture 6How to Sort Microsoft Excel Columns Alphabetically Picture 6
    Use this when your spreadsheet uses full names in a single cell. If you have full names listed in a single column, alphabetizing will only sort by the first name. With these instructions, you can divide the names into two columns first so you can sort by the last name column instead.[4]
  2. How to Sort Microsoft Excel Columns Alphabetically Picture 7How to Sort Microsoft Excel Columns Alphabetically Picture 7
    Insert a new blank column. Place this immediately to the right of the names column.
  3. How to Sort Microsoft Excel Columns Alphabetically Picture 8How to Sort Microsoft Excel Columns Alphabetically Picture 8
    Enter the formula for first names. Enter this formula in the top cell of the new column: =LEFT(A1,FIND(" ",A1)) making sure to include the space between the quotation marks. This formula will look in the full name column and copy everything before the space
    1. If necessary, replace both instances of A with the letter of the column that has the full names listed.
    2. Replace both instance of 1 with the number of the row you are typing in.
  4. How to Sort Microsoft Excel Columns Alphabetically Picture 9How to Sort Microsoft Excel Columns Alphabetically Picture 9
    Copy this formula to the entire column. Click the header of this new column and copy-paste the formula you just entered. You should see all the first names appear on their own in this column.
  5. How to Sort Microsoft Excel Columns Alphabetically Picture 10How to Sort Microsoft Excel Columns Alphabetically Picture 10
    Create the last name column. Create a new column to the right of the first name column. Copy-paste this formula to populate the column with last names:
    1. =RIGHT(A1,LEN(A1)-FIND(" ",A1))
  6. How to Sort Microsoft Excel Columns Alphabetically Picture 11How to Sort Microsoft Excel Columns Alphabetically Picture 11
    Sort by the last name column. You may now sort the last name column alphabetically, as described in the method above.
4.8 ★ | 4 Vote
columnsortnamesrow

You should read it

Maybe you are interested

Office information