Comments on: How to alphabetize in Excel: sort columns and rows A-Z or Z-A

Alphabetizing in Excel is as easy as ABC. Whether you are sorting an entire worksheet or selected range, vertically or horizontally, ascending or descending, in most cases the task can be accomplished with a button click. Continue reading →

Comments page 2. Total comments: 92

  1. example:
    have the values A4APPLES; ANORANG. In all my attempts whether I use a custopm list or not A4APPLES is listed before ANORANG, my requirement is to have ANORANG before A4APPLES... (My list has 1500 entries)

    Why is this not an option in EXCEL?

    1. Hi!
      All values must be accurately specified in the sorting list. I think that in your case this is impossible. Sorting in Excel always puts a number before a letter.

  2. Excel defaults to sorting 0-9, A-Z. How can you configure it so that it sorts A-Z, 0-9?
    I tried creating a custom list "A,B,C,... etc... .. X,Y,Z,0,1..list but this does not appear to work as I have Value AA00 before AAJ4.
    Surely there must be a simple way to change this......

      1. As I said in my message I have created a custom list and it appears and I request the sort uses the custom list. Yet the order is still numeric BEFORE letters.
        Hence my post. Teh Custom List option DOES NOT WORK.

  3. how can I sort tabs alphabetically without doing it manually

  4. sorting data used to be so easy to select the column and select A -> Z and the column would alphabetize the column. I don't know what it is doing, but I have a list of Last Name, First Name, and it is not sorting in alpha order except in the filter but not on the page.

  5. I have a problem with this formula. It adds the first entry of the row in my raw data to the very end of the sorted row, regardless of where it belongs to be in the new sorted row.

    Example:

    Raw data: Adam, Eddy, Greg, Fred
    Resultant sorted data: Eddy, Fred, Greg, Adam

    Why doesn't Adam remain as the first entry?

    1. Never mind. Found the problem. There was a space in front of some of my cell items.

  6. I have a list of 150 birthdays spread into several years and i want to sort them by Month - Jan, Feb, March, etc. (the Sort option arranges it numerically.
    Can someone help? thanks and peace

    1. Hello!
      I recommend using Excel's built-in sort options.
      Select the records that you want to sort by month.
      On the Data tab, in the Sort & Filter group, click Sort.
      In the Sort dialog box, do the following:
      Under Column, select the name of the column that contains the dates.
      Under Sort on, choose Cell Values.
      Under Order, select Custom List.
      In the Custom Lists dialog box, select short names (Jan, Feb, Mar…)
      Click OK twice to close both dialog boxes.

  7. Sorting Croatian alphabet works fine until it come to letters S and Š, it seems excel doesn't recognize them as two individual letters. Any advice how to fix this problem?

  8. I have multiple names that appear several times. After sorting by the names, is there a way to subtotal each section (by name).

  9. Is it possible to sort information together in 2 or more columns? So if i have an entire page full of names (for attendance so to speak) and it fills up columns A-F, what do i do to get kids in order even if their last name begins with a C and should go in column A, but they're entered into column F to start.

  10. Awesome!! Thank you!

  11. I want to sort columns alphabetically in excel see below:
    NC BD GO CC MM
    How do I do that

    Thanks

    1. Hello James!
      I hope you have studied the recommendations in the above tutorial. Describe in detail what problem you have, and I will try to help you

  12. Dear Team ,

    I had the problem in excel sorting , I have the data is
    M10
    M3
    M14
    M23
    M25
    M42
    M100
    M6
    M65
    M8
    M95
    M120

    Please let me inform how to sort out

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what formula you used and what problem or error occurred. What result did you want to get?
      It’ll help me understand it better and find a solution for you. Thank you.

  13. Why is it that when I am sorting a to z that 2 of the ones I have or at the top and will not be in the appropriate order? It is like it skips those two. If I start a new page and put in the same information it will do the same thing and not sort those two.

  14. After sorting A-Z the same column has somehow created Two seperate A-Z sorting in the same column? I've tried unhiding rows to see if there blanks causing the issue but don't see any.
    it would look like this :
    able
    att
    bits
    zorro
    act
    bart
    cart
    in other words the column alphabetized twice as you see after zorro "act" started a new alphabetized sequence. I can't get "act" to appear under able where it should be after the A-Z sort. !!!

  15. Sorting A-Z for multiple pages? Can I sort to incorporate five pages in A-Z order?

  16. Hi,
    I'm trying to line up job codes and am having trouble getting them to sort correctly. Example:
    ACT 01
    ACT 10
    ACT 100
    ACT 11
    ACT 12

    Is there a way that can sort so it makes the numbers so in consecutive order?
    ACT 10
    ACT 11
    ACT 12
    ACT 100

    Thank you!

  17. HI,

    Great tutorial but it is not working for me. MY students have a cemetery database in excel that has about 3700 rows and 12 columns. The first two columns are last name, first name. It was working several months ago but now, the last name sorts but the first name doesn't. So for all of the last names "Adams". It sorted the first names alphabetically for the first 10 or so, but then incorrectly sorted the next 5-10 first names. I don't know what went wrong. We had no trouble with multi-level sorting the data by other headings, like Section, Row, and Grave number. That worked perfectly. But the last name first name multi-level sort is not working. We are getting things like:
    Adams Anita G.
    Adams Naomi Allen
    Adams Space
    Adams Space
    Adams Martha H.
    Adams Raymond
    Adams Kim C.
    Adams Space
    Adams Michael Earl
    Adams Mytile Evans
    Adams Dr. Eugene W Sr.
    Adams Addie W.
    Adams Minnie Pugh
    Adams Esau
    What might we be doing wrong? Help would be greatly appreciated. Thanks!

    1. Hi Deloris,

      It's hard to say what is causing this without seeing the actual data. Unexpected sorting issues sometimes occur in shared workbooks, please check this. Also, you can trying clearing all formatting in the problematic columns. If neither helps, then you can send us a sample worksheet (support@ablebits.com) and we will try to figure out the source of the problem.

  18. Hi,
    Want to do this one :
    How to sort each column alphabetically in Excel
    =INDEX(A$3:A$5,MATCH(ROWS(A$3:A3),COUNTIF(A$3:A$5,"<="&A$3:A$5),0))
    But not from A to Z but Z to A. How should I modify this to work?

  19. Hi!
    I`m trying to make your formula :
    =INDEX(A$3:A$5,MATCH(ROWS(A$3:A3),COUNTIF(A$3:A$5,"<="&A$3:A$5),0))
    to alphabetize an attendance list.
    Thing is in the Column A I already have a formula which give me who has attended the event or blank for people who did not. Ex.: A4 =IF($M4="○",$B4,"")
    The M column is mark ○ (yes) or × (no) depending on attendance. The B column is the name of invited people in order of member`s card number.
    If I use your formula then it comes back with #N/A. How can i fix this?

    1. Found the problem. SOLVED.

  20. My problem is the column I want alphabetized e.g., column C was successfully alphabetized but the information in the other columns did not go with the newly sorted names in alpha order. My list has a name, contact number. dates and remarks. The remarks have a more than a one liner. The names were alphabetized but the other data corresponding to that name did not move. Please help. Thank you.

  21. Hi,
    I have situation here, I have multiple words in the same cell in excel. I need to arrange them into Alphabetical Order A-Z in the same cell. What should be the formula for the same?
    Please help Me in this.
    Example : "Zebra Apple Machine" i need it in " Apple Machine Zebra"

    1. Hi,
      First of all, I would place each word into an individual cell by applying any of the following tools: Excel's 'Text to Columns' or Ablebits' 'Split Text'. Then I would follow Svetlana Cheusheva's advice on 'How to alphabetize each row individually in Excel' (please see the article above). You could always put the words back into one cell with the help of 'Merge Cells' — one more tool offered by Ablebits' 'Ultimate Suite'.

  22. Hi,
    I have a spreadsheet of guest names and table numbers for an event
    For the first row, for example it says: Mr. and Mrs. | William | Smith | 2 attending | Table 2
    To clarify: A2, says Mr. & Mrs., B2 has the guest's first name name, C2 has their Last Name. D2 says the number attending the event, and E2 has their table number, F2 will have a personal note on the guest
    How can arrange the names alphabetically (according to last name) , and ensure their information follows?
    Thank you in advance,

    1. Highlight all the cells you want included in the sorting, so from A2 down to the last cell in Column F. Then, when you sort by Last Name (Column C,) all the rows will stay intact.

  23. Saved the DAY. Thanks/

  24. I have a spreadsheet that appears to contain two different kind of data. Same exact columns and rows but obviously the later spreadsheet was saved differently. Now the spreadsheet doesn't alphabetize the data from the newer data into the original spreadsheet. How can I get the new data implemented with the old data?

  25. Hello,
    I want to sort description column from A to z. However the problem it is all cells of different alignment. There is a huge amount of data that i will need to align in one order for the filter to work. is there a way in which i can align all data. I tried using the alignment tab to get all the data in the column to be in same alignment. but it is not working.

    Please is there any solution!!

  26. Since there is NO value

  27. Dear Doug/Kathy,

    Sorry for the delayed reply. As you mentioned, I tried many times with the multiple column sorting. But, it doesn't work. Assume that, I've data in 3 columns like 10 to 1 in column A & B (Remember they're disorganized). Column C has data. But, only 1 to 5(Disorganized). Now if I try to sort out the data by using multiple column method, it is not sorting out all the columns either largest to smallest or vice-versa. Is there any other way? And also, as I mentioned earlier discussion, I'm getting blank cells between rows. Since there is value or data in the blank cells.

  28. One thing that I am finding is that all the columns have to be the same size in order for the system to sort.

  29. Dear Doug,

    Yes.. I was sorting out all the columns into A to Z format by manually selecting the individual columns. I tried multiple columns sort out, not coming properly. As i mentioned, lot of blank rows in between.

    Thank You

    1. Sivakumar:
      When you sort each column individually, are all the blank cells together?
      Have you checked to be certain they are empty? They may contain spaces, breaks or other non-printing characters.
      I was thinking about your situation yesterday. I wonder if it is a situation where you just have to manually clean the data. It sounds as if the multiple column sorting won't give you the result you want now.

  30. Dear Doug,

    Yes.. I was sorting out all the columns into A to Z format by manually selecting the individual columns. I tried multiple columns sort out, not coming properly. As i mentioned, lot of blank rows in between.

    Thank You

  31. Hi,

    I want to sort out nearly 100 columns and in all the columns rows number is differ. Ex, In one column, until row 10 have data in another column have data until 20. In this situation, how to sort out the data in all column in A to Z format. I tried.But, in some columns in between blank cells came. Kindly help me.

    One more question, by reading this i understood that for sorting out multiple columns i have to use add level. But, when the column numbers are more means each time how can i go change the column number. If there is other way, i could save my time.

    1. Sivakumar:
      Can you sort each column separately or do all the columns have to be sorted as one big selection?
      If you can sort each column by itself you can avoid the blank row problem.
      If the data in each column has to stay with its respective row across the entire data set, you're doing it the only way it can be done. You have my sympathy.

  32. hi friend!!!
    I have problem in this formula i used but it's not working and result showing #N/A. please solve it.

    1. Hi Sachin,

      You can download our sample workbook that contains all the formulas discussed in this tutorial and try them on your data.

  33. Hellow my name is MartinElisy. Wery capable post! Thx :)

  34. Hello,
    I need some Help on Excel.
    Help me design for me a Fixed Assets Depreciation Excel Sheet with the formulas that can calculate all the monthly fixed assets depreciation values at cost of the assets on reducing balance method of depreciation.
    Regards,
    Denis.

  35. Thanks for this, love your tutorials. Keep it up!

    1. My friend, I have a problem in sorting the entire spreadsheet.
      Ex: I have three hundred names entered from line two to fifty one and in several columns.
      I need to sort all the names in the spreadsheet, all columns at once. I tried all methods I knew, in vain. Please help me. Yhanks

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)