Search
Close this search box.

Automatically Sort Data in Alphabetical Order using Formula

FREE EXCEL TIPS EBOOK - Click here to get your copy

Excel built-in data sorting is amazing, but it isn’t dynamic. If you sort data and then add data to it, you would need to sort it again.

Sort Data in Alphabetical Order

In this post, I will show you various ways to sort data in alphabetical order using formulas. This means you can add data, and it will automatically sort it for you.

When the Data is all Text with No Duplicates

Suppose you have a data as shown below:Sort data in alphabetical order - text dataset

In this example, all the data is in text format (no numbers, blanks or duplicates). To sort this, I will use a helper column. In the column next to the data, use the following COUNTIF formula:

=COUNTIF($A$2:$A$9,"<="&A2)

sort data in alphabetical order - Helper column with countif

This formula compares a text value with all the other text values and returns its relative rank. For example, in cell B2, it returns 8, as there are 8 text values that are lower than or equal to the text ‘US’ (alphabetical order).

Now to sort the values, use the following combination of INDEX, MATCH and ROWS functions:

=INDEX($A$2:$A$9,MATCH(ROWS($B$2:B2),$B$2:$B$9,0))

sort data in alphabetical order - Formula Index to get sorted dataThis formula simply extracts the names in the alphabetical order. In the first cell (C2), it looks for the country name that has the lowest number (Australia has 1). In the second cell, it returns Canada (which has the number 2) and so on..

Allergic to Helper Columns??

Here is a formula that will do the same without the helper column.

=INDEX($A$2:$A$9,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),0))

This is an array formula, so use Control + Shift + Enter instead of Enter.

I will leave it for you to de-code.

Try it Yourself.. Download Example FileDownload File Pic

This formula works well if you have text or alphanumeric values.

But it fails miserably if:

  • You have duplicates in the data (try putting US twice).
  • There are blanks in the data.
  • You have a mix of numbers and text (try putting 123 in one of the cells).
Also read: Separate First and Last Name in Excel

When Data is a Mix of Numbers, Text, Duplicates, & Blanks

Now this one is a bit tricky. I will use 4 helper columns to show you how it works (and then give you a huge formula that will do it without the helper columns). Suppose you have a data as shown below:sort data in alphabetical order - blank duplicate 1

You can see there are duplicate values, blank and numbers. So I will use helper columns to address each of these issues.

Helper Column 1

Enter the following COUNTIF formula in Helper Column 1

=COUNTIF($A$2:$A$9,"<="&A2)

sort data in alphabetical order - blank duplicate 2

This formula does the following:

  • It returns 0 for blanks.
  • In the case of duplicates, it returns the same number.
  • Text and numbers are processed parallelly and this formula returns the same number for text and number (for example 123 and India both get 1).

Helper Column 2 

Enter the following IS Function in Helper Column 2:

=--ISNUMBER(A2)

sort data in alphabetical order - blank duplicate 3

Helper Column 3

Enter the following formula in Helper Column 3:

=--ISBLANK(A2)

sort data in alphabetical order - blank duplicate 4

Helper Column 4

Enter the following formula in Helper Column 4

=IF(ISNUMBER(A2),B2,IF(ISBLANK(A2),B2,B2+$C$10))+$D$10

sort data in alphabetical order - blank duplicate 5

The idea for this formula is to segregate blanks, numbers and text values.

  • If the cell is blank, it returns the value in cell B2 (which would always be 0) and adds the value in cell D10. In a nutshell, it will return the total number of blank cells in the data
  • If the cell is a numerical value, it will return the comparative rank and add the total number of blanks. For example, for 123 it returns 2 (1 is the rank of 123 in the data, and there is 1 blank cell)
  • If it is text, it returns the comparative rank and add the total number of numerical values and blanks. For example, for India, it add the text’s comparative rank in text (which is 1) and adds the number of blank cells and the number of numerical values.

Final Result – Sorted Data

Now we will use these helper columns to get the sorted list. Here is the formula:

=IFERROR(INDEX($A$2:$A$9,MATCH(SMALL($E$2:$E$9,ROWS($F$2:F2)+$D$10),$E$2:$E$9,0)),"")

sort data in alphabetical order - blank duplicate 6

This method of sorting now becomes fool-proof. I have shown you the method for 8 items, but you can extend it to as many items as you want.

Try it Yourself.. Download Example FileDownload File Pic

One Formula to Sort it All (without Helper Columns)

If you can handle extreme formulas, here is an all-in-one formula that will sort data in alphabetical order (without any helper column).

Here is the formula:

=IFERROR(INDEX($A$2:$A$9,MATCH(SMALL(NOT($A$2:$A$9="")*IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(--ISNUMBER($A$2:$A$9))),ROWS($A$2:A2)+SUM(--ISBLANK($A$2:$A$9))),NOT($A$2:$A$9="")*IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(--ISNUMBER($A$2:$A$9))),0)),"")

Enter this formula in a cell and drag it down to get the sorted list. Also, since this is an array formula, use Control + Shift + Enter instead of Enter.

This formula has real-world utility. What do you think? I would love to learn from you. Leave your footprints in the comments section!

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

81 thoughts on “Automatically Sort Data in Alphabetical Order using Formula”

  1. If you want to extract unique values with ignoring blank cells use this formula:

    {=IFERROR(INDEX($A$2:$A$9,MATCH(SMALL(IF(($A$2:$A$9″”)*(MATCH($A$2:$A$9&””,$A$2:$A$9&””,0)=ROW($A$2:$A$9)-ROW($A$2)+1),IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,”<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(–ISNUMBER($A$2:$A$9)))),ROWS($A$2:A2)),IF(($A$2:$A$9″”)*(MATCH($A$2:$A$9&””,$A$2:$A$9&””,0)=ROW($A$2:$A$9)-ROW($A$2)+1),IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,”<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(–ISNUMBER($A$2:$A$9)))),0)),"")}

    For reverse sorting data from ascending to descending replace SMALL( to LARGE(

    Reply

  5. Formula works great if the originating data list is not the result of formulas that may contain blanks. I dont mean empty cells. I mean the result of a formula that returns a blank (“”). Even the downloaded version puts the blanks on the top of the list (when changing the Column A to show a few =””. Simply put – the formula doesnt work unless the originating data does not contain “”. Shame too, it was a nice formula….until it didnt work. (If anyone figures this out, let me know) (jason@simconconcrete.com)

    Reply

    • Solution was:
      i) add a preliminary Helper 0 in column B, and shift rightward the rest of Helpers (ie. Helper 1 in col.C, etc).
      ii) in Helper 0, add the formula in cell B2
      =COUNTIF($A$2:$A$9;”=”&A2) – COUNTIF($A$2:A2;”=”&A2)
      … and extend to the end of the column range.
      iii) adjust Helper 1 column with the formula in cell C2
      =COUNTIF($A$2:$A$9;”<="&A2)-B2

      Reply

  7. Great work, thanks! Would have taken me a stupid amount of time to dope this out on my own (and I LOVE helper columns)

    Reply

    • Sorry, meant to mention.
      In my case, I was actually needing to sort column A by the alpha order of Column B. Meaning that for all the duplicates in Column B, I actually had a unique value in Column A, each of which I need to list.
      SO….
      I simply added two more helper columns, helper 4: counted the occurrences of value of column B in range column B, helper 5: added helper 4 and 5 together, and voila!

      Reply

  8. Above sort without helper column will fail with dupes. Use this following variation as the lazy person solution to dupes. If column to be sorted has varying number of rows of data, this one handles it…

    =If(a2=””,””,INDEX($A$2:$A$9,IFERROR(MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$9,”<="&$A$2:$A$9),0),MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),-1))

    Reply

  9. Very good for me ! I adjusted it and it works. But ….
    How and what must it be changed in formula to have number sorted descendig and the text unchanged, at the end ?

    =IFERROR(INDEX($A$2:$A$9,MATCH(SMALL(NOT($A$2:$A$9=””)*IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,”<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(–ISNUMBER($A$2:$A$9))),ROWS($A$2:A2)+SUM(–ISBLANK($A$2:$A$9))),NOT($A$2:$A$9="")*IF(ISNUMBER($A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9),COUNTIF($A$2:$A$9,"<="&$A$2:$A$9)+SUM(–ISNUMBER($A$2:$A$9))),0)),"")

    Thank you in the event of a reply!

    Reply

  10. Wonderfully helpful — thank you. The way you’ve structured your explanation is excellent; you’ve communicated the information with great clarity.

    Reply

  11. This only appears to work if the range is a text range rather than text returned from a formula. If the range is cells with formulas it appears to sort the blank or zeros at the top rather than the bottom. Is there a way to sort this?

    Reply

  13. Hi,

    How would you work the above solution if there were names (first name and Surname of a person)?
    For example:
    David Li
    Matthew McLennan
    Darren Findlay
    Ben Maddahi
    David Currie

    None of the above methods seem to work (I am getting the rank formula value as 0)

    Could you help me with this?

    Reply

  14. to handle duplicates, I just add the row number divided by a huge number to the rank. So, something like =A4+row()/1000000000. This will make every rank unique.

    Reply

  15. I need to sort a names list alphabetically, primarily by the last name, together with the first name and a student number. I obviously want the first name and student number to match up to the last name after sorting. Is there are a way to set this up automatically so that as I paste raw data into 3 columns – LAST NAME, FIRST NAME, STUDENT NUMBER, 3 formula columns will fill-up automatically with the sorted data?

    I have 100’s of students to sort this way each term and repeatedly using the “sort” function is tedious.

    Thank you

    Reply

  17. How can I get the following formula to ignore cells which has a formula in, but appear blank?

    =INDEX($A$2:$A$9,MATCH(ROWS($A$2:A2),COUNTIF($A$2:$A$9,”<="&$A$2:$A$9),0))

    Even better would be a formula that removes duplicates and sort the data alphabetically. This source data is text only.

    Reply

  18. Two things:
    1 – The “One formula to sort if all” did not work for me as written. I had to make an edit:
    “–ISBLANK” I had to change to “COUNTBLANK” and then it worked fine.

    2 – Duplicates still appear in the result column. How can I have it remove duplicates in the result column?

    Thank you!

    Reply

  19. Hi Sumit. The forumala doesnt seem to work for me :/ When i click enter it become N/A. At the bottom it says ciruclar reference 🙁 can you please help?

    Reply

  20. Hi i have the below formula, but it appear 0 in the cell, how can i make the cell in blank in adjusting formula.
    Thank you for your help.

    =IF(ISERROR(INDEX(‘Oct-Dec2017’!$B$3:$V$350,SMALL(IF(‘Oct-Dec2017’!$B$3:$B$392=MID(CELL(“filename”,X1),FIND(“]”,CELL(“filename”,X1))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(‘Oct-Dec2017′!$B$3:$B$392)))),ROW(1:1)),MATCH(C$3,’Oct-Dec2017’!$B$3:$V$3,0))),””,INDEX(‘Oct-Dec2017’!$B$3:$V$392,SMALL(IF(‘Oct-Dec2017’!$B$3:$B$392=MID(CELL(“filename”,X1),FIND(“]”,CELL(“filename”,X1))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(‘Oct-Dec2017′!$B$3:$B$392)))),ROW(1:1)),MATCH(C$3,’Oct-Dec2017’!$B$3:$V$3,0)))

    Reply

  22. So helpful Sumit! This works great. I am using this to alphabetize a dynamic personnel list. But when two people have the same last name, the first person shows up twice and the second person is lost. Of course it takes another level of checking (first name will do). Any advice?

    Reply

  23. trying to get a range of cells to change AUTOMATICALLY together by the last date and arranging from oldest to newest date. (i.g. pallet total – row number – product date.)

    50 A5 15/02/2017 stating something like this
    10 B3 15/01/2017
    178 A10 1/01/2017

    178 A10 1/01/2017 and ending like so
    10 B3 15/01/2017
    50 A5 15/02/2017

    I would think some sort of an array but any help would be most appreciated.

    Reply

  24. Well done, Sumit! Until I consulted your well-written article the only way I had thought of to sort a table column without using the Sort command was to use a pivot table (which still requires a refresh if the contents of the source column change). I had also tried using conditional formatting to warn the user that the column was no longer sorted, but conditional formatting rules break easily when you move the contents around. I assume your solution works with Excel tables and will find out shortly. Also glad to see someone else uses the =index(match(…)) combination, which I find more reliable than vlookup and lookup.

    Reply

    • Actually, I needed to sort a two-column list, and borrowing your approach I couldn’t find a way to do it without using a helper column. Have you figured out a way? Here’s an example:
      Letter Number
      B 3
      A 7
      A 5
      B 8
      A 4
      B 7

      Sort the above list in order of Letter and Number so that it looks like this:
      Letter Number
      A 4
      A 5
      A 7
      B 3
      B 7
      B 8

      Reply

  25. Hi Sumit, Thank you for posting such a fantastic tutorial. I needed an array like this to sort data from one source into another, and the “One Formula to Sort it All (without Helper Columns)” solution was perfect once adapted.

    One thing though, could you show me how to add a conditional filter to the match?

    I have a sorting array (thank you ) in “Sheet 2” that works perfectly to grab unsorted data from column “E” in the sheet “Level 4”, and then it sorts the data, drops any blanks and returns a list sorted by “Last Name”. What I need to add to this array formula is the filtering of data based on the “City” in column “G”.

    Here is my amended version of your formula:

    =IFERROR(INDEX(‘Level 4’!$C$2:$G$110,MATCH(SMALL(NOT(‘Level 4’!$E$2:$E$110=””)*IF(ISNUMBER(‘Level 4’!$E$2:$E$110),COUNTIF(‘Level 4’!$E$2:$E$110,”<="&'Level 4'!$E$2:$E$110),COUNTIF('Level 4'!$E$2:$E$110,"<="&'Level 4'!$E$2:$E$110)+SUM(–ISNUMBER('Level 4'!$E$2:$E$110))),ROWS('Level 4'!$E$2:$E2)+SUM(–ISBLANK('Level 4'!$E$2:$E$110))),NOT('Level 4'!$E$2:$E$110="")*IF(ISNUMBER('Level 4'!$E$2:$E$110),COUNTIF('Level 4'!$E$2:$E$110,"<="&'Level 4'!$E$2:$E$110),COUNTIF('Level 4'!$E$2:$E$110,"<="&'Level 4'!$E$2:$E$110)+SUM(–ISNUMBER('Level 4'!$E$2:$E$110))),0),3),"")

    This is what the unsorted data looks like on the "Level 4" sheet:

                C          |          D           |           E           |    F    |   G
    ——————————————————————-
    1|   id number   |  First Name    |   Last Name   |  N/A  |  City

    Where do I start?

    Reply

    • I’ve tried to carry on, but I am thoroughly stuck. I have changed the formula as follows, and although it picks up the first match correctly, it doesn’t parse through the data to give any other matches, just the same one over and over again.

      =IFERROR(INDEX(‘Level 4’!$C$2:$G$110,MATCH(SMALL(–NOT(‘Level 4’!$G$2:$G$110=”Rome”)*NOT(‘Level 4’!$E$2:$E$110=””)*IF(ISNUMBER(‘Level 4’!$E$2:$E$110),COUNTIF(‘Level 4’!$E$2:$E$110,”<="&'Level 4'!$E$2:$E$110),COUNTIF('Level 4'!$E$2:$E$110,"<="&'Level 4'!$E$2:$E$110)+SUM(–ISNUMBER('Level 4'!$E$2:$E$110))),ROWS('Level 4'!$E$2:$G2)+SUM(–ISBLANK('Level 4'!$E$2:$E$110))),–NOT('Level 4'!$G$2:$G$110=Key!$G$6)*NOT('Level 4'!$E$2:$E$110="")*IF(ISNUMBER('Level 4'!$E$2:$E$110),COUNTIF('Level 4'!$E$2:$E$110,"<="&'Level 4'!$E$2:$E$110),COUNTIF('Level 4'!$E$2:$E$110,"<="&'Level 4'!$E$2:$E$110)+SUM(–ISNUMBER('Level 4'!$E$2:$E$110))),0),3),"")

      Help! I'm spiraling into excel hell!

      Reply

  26. Sumit even i am facing same problem as prachi. can you share the sheet you have shared with prachi. Prob by prachi was:
    Hi..I am trying to use this formula but am facing a problem. My datasheet is different as i have a list of names and the scores against it ( which obviously will have duplicates). I have arranged that using helper 1 in a new column. If I use this formula, the same name gets repeated for the same score. Please help with this problem. Am stuck at this since 4 days now. Thanks in advance.

    Please help

    Reply

  27. Hi- first, you are brilliant- and this formula saved me! But, I have a question…I need the associated data in the other columns to sort WITH my “Client Name” column which I want auto-sorted in Alphabetic order so that I can add Clients without having to manually sort each time. So, for example, I have “Client Originator” and “Client Owner” that is associated with each client. When they auto sort, the Originator and Owner info needs to sort too. Help! Many thx in advance.

    Reply

  28. Hi Sumit, can you help me to have a formula for the automatic removal of the duplicate in one colum, appreciate your help. thanks

    Reply

  31. Hello,

    What if I want to sort cells with on Column A which greater than zero on Column C?

    Apple 1
    Orange 1
    Pear 0
    Watermelon 2
    Result:
    Apple 1
    Orange 1
    Watermelon 2

    Is it possible to sort it with a simple formula but not array?
    Many thank!

    Reply

  32. This works great in the first 30-40 columns, but if I try to use it in column AW for example, it doesn’t work. Any reason for that?

    Reply

  33. Hey Sumith . Can u just help me in arranging from descending order? I kinda modified the formula but dint work!

    Reply

  34. Hi, great formula but I am having some difficulties. The blanks in my data set appear after the last number in the data set therefore when I apply the formula the blanks appear at the top of my sorted list. Is there a method to make these appear below the numbers in the sorted list? Much appareciated!

    Reply

  35. I am trying to sort my data that has been pasted into a spread sheet that will transpose to another sheet and place the names in alphabetic order.

    Reply

  36. Hi..I am trying to use this formula but am facing a problem. My datasheet is different as i have a list of names and the scores against it ( which obviously will have duplicates). I have arranged that using helper 1 in a new column. If I use this formula, the same name gets repeated for the same score. Please help with this problem. Am stuck at this since 4 days now. Thanks in advance.

    Reply

  37. Hey Sumit bhai you did a awesome job…. i have also made a formula …..

    =IFERROR(INDEX($A$1:$A$15,MATCH(SMALL(IF(ISTEXT($A$1:$A$15),COUNTIF($A$1:$A$15,”<="&$A$1:$A$15)*15^2,IF(ISNUMBER($A$1:$A$15),COUNTIF($A$1:$A$15,"<="&$A$1:$A$15))),ROW(1:1)),IF(ISTEXT($A$1:$A$15),COUNTIF($A$1:$A$15,"<="&$A$1:$A$15)*15^2,IF(ISNUMBER($A$1:$A$15),COUNTIF($A$1:$A$15,"<="&$A$1:$A$15))),0)),"")

    Reply

      • Shortcut for Referencing Ranges
        A shortcut is available when referencing ranges. The shortcut uses square brackets, as shown in Table .
        Shortcuts for Referencing Ranges
        Standard Method ShortcutRange
        (“D5”) [D5]
        Range(“A1:D5”) [A1:D5]
        Range(“A1:D5, G6:I17”) [A1:D5, G6:I17]
        Range(“MyRange”) [MyRange]

        Reply

  40. Thats a great tip indeed.. it really works for me, although I had to put lot of mind to understand it.

    Reply

  42. Real world utility? I would guess it is good for feeding in a NamedRange for Data Validation, so that you validating list always come in sorted order. What you say?

    Reply

    • Thanks for commenting. Named range + Data validation would make an amazing use of it. It can also be used to get the sorted data in another location/worksheet (in case of employee names or any types of Ids). I got this query from a friend who wanted to sort id names and numbers using a formula.

      Reply

    • Thanks for commenting Rose. I know the final formula is a bit too much to handle. But I have tried making it robust to handle all situations (numbers, text, duplicates and blanks).

      Reply

  48. Great tip! I don’t understand the big formula in the end, but it seems to do the trick! I am using it going forward. Thanks for sharing

    Reply

Leave a Comment

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

VBA Loops

©TrumpExcel.com – Free Online Excel Training

DMCA.com Protection Status

Privacy Policy | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster