How to Sort Microsoft Excel Columns Alphabetically

PDF download Download Article
PDF download Download Article

Sorting your data alphabetically can help you organize directories, email lists, and lists of clients. This wikiHow article will teach you how to sort Microsoft Excel data alphabetically.

Method 1
Method 1 of 2:

Sorting Alphabetically

PDF download Download Article
  1. You can do this by going to File > Open within Excel or you can right-click the project file and select Open with > Excel .
  2. 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:
    • Format your header row differently. For example, bold the text or make it a different color. [1]
    • Make sure there are no blank cells in your header row. [2]
    • If Excel still sorts the headers, select the header row and use the top ribbon menu to click Home > Editing > Sort & Filter > Custom Sort > My data has headers . [3]
    Advertisement
  3. You can click the header cell for that column, or the letter above it (A, B, C, D, etc.).
    • Press and hold Ctrl/Opt to select more than one column.
  4. Click Data in the top menu to view Data options in the ribbon menu above your spreadsheet.
  5. The ribbon menu is divided into related areas, with the name under each one. Look for the area labeled Sort & Filter.
    • 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.
  6. 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.
    • To sort it in reverse alphabetical order instead, click the Z → A symbol instead.
    7. Advertisement
Method 2
Method 2 of 2:

Sorting by Last Name

PDF download Download Article
  1. Place this immediately to the right of the names column.
    • 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.
    • To insert a blank column, select the letter row to the left of where you want the blank row to go (so if you want a new blank column to the right of A), select the first cell, then right-click it, and click Add Column to Right .
  2. 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.
    • If necessary, replace both instances of A with the letter of the column that has the full names listed.
    • Replace both instances of 1 with the number of the row you are typing in.
  3. 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.
  4. Create a new column to the right of the first name column and enter this formula: =RIGHT(A1,LEN(A1)-FIND(" ",A1)) . Copy-paste this formula to populate the column with last names.
  5. You may now sort the last name column alphabetically, as described in the Sorting Alphabetically method.
    6. Advertisement

Community Q&A

Search
Add New Question
  • Question
    What is the shortcut key for merging cells?
    Community Answer
    Hold the key until an overlay appears on the ribbon. Press to select the Home tab. Press to select Merge and Center. Then, press to merge and center the selected cells.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 1 Helpful 2
  • Question
    Is there a maximum number of lines in Excel?
    Community Answer
    In Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384, so I don't think so.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 1 Helpful 4
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      • If the top "ribbon" menu has disappeared, double-click any tab to expand it again.
        Thanks
        Helpful 0 Not Helpful 0
      • These instructions are for Excel versions 2003 and later. If you are running earlier versions of Excel, you may need to find these menu items in a different location.
        Thanks
        Helpful 0 Not Helpful 0
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      Video

      You Might Also Like

      How to
      Create a Currency Converter With Microsoft Excel
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Use Solver in Microsoft Excel
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      How to
      Link Sheets in Excel
      Advertisement

      References

      1. ↑ http://excelribbon.tips.net/T009423_Recognizing_a_Header_Row_when_Sorting.html
      2. ↑ http://excelribbon.tips.net/T009423_Recognizing_a_Header_Row_when_Sorting.html
      3. ↑ https://support.office.com/en-US/article/sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654

      About This Article

      Article Summary X

      1. Format the header row differently than data rows.
      2. Select a column.
      3. Click Data .
      4. Click the A to Z button in the "Sort & Filter" panel.

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 731,059 times.

      Reader Success Stories

      • Alvin Witten

        Sep 22, 2017

        "I compiled a list of my favorite songs and a separate list of favorite places to visit and included tips. However, ..." more
      More reader stories Hide reader stories
      Share your story

      Is this article up to date?

      Advertisement