This article was co-authored by How.com.vn staff writer, Darlene Antonelli, MA. Darlene Antonelli is a Technology Writer and Editor for How.com.vn. Darlene has experience teaching college courses, writing technology-related articles, and working hands-on in the technology field. She earned an MA in Writing from Rowan University in 2012 and wrote her thesis on online communities and the personalities curated in such communities.
This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources.
This article has been viewed 726,643 times.
Learn more...
Sorting your data alphabetically can help you organize directories, email lists, and lists of clients. This How.com.vn article will teach you how to sort Microsoft Excel data alphabetically.
Steps
- Open your project in Microsoft Excel. You can do this by going to File > Open within Excel or you can right-click the project file and select Open with > Excel.
- 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:
- 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 - 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.).
- Press and hold Ctrl/Opt to select more than one column.
- Click the Data tab. Click Data in the top menu to view Data options in the ribbon menu above your spreadsheet.
- 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.
- 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.
- 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.
- To sort it in reverse alphabetical order instead, click the Z → A symbol instead.
- Insert a new blank column. 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.
- 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.
- 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.
- 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.
- Create the last name column. 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.
- Sort by the last name column. You may now sort the last name column alphabetically, as described in the Sorting Alphabetically method.
Community Q&A
- QuestionWhat is the shortcut key for merging cells?Community AnswerHold 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.
- QuestionIs there a maximum number of lines in Excel?Community AnswerIn Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384, so I don't think so.
Tips
- If the top "ribbon" menu has disappeared, double-click any tab to expand it again.Thanks
- 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
Video
References
About This Article
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.
Reader Success Stories
- "I compiled a list of my favorite songs and a separate list of favorite places to visit and included tips. However, it was frustrating running through the entire lists each time I looked for a song or an article. I Googled "How to "alphabetize" my lists and came across your website. Brilliant! With one swoop my frustrations were gone. Thanks a million."..." more
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.