Article updated on February 18, 2018

I am inspired once again by the article Sorting Text in Excel using Formulas at Pointy haired Dilbert. In Chandooยดs article he sorts text with a "helper" column. My goal with this article is to show you how to sort text cells alphabetically without a helper column.

Here is an example, cell range \$A\$2:\$A\$15 contains text values.

Here is how to automatically sort text cells without any user interaction in cell B2:

Array formula in cell B2:

=INDEX(\$A\$2:\$A\$15, MATCH(SMALL(COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), ROW(1:1)), COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), 0))

Sort both numerical and text values with blank cells:

Sorting numbers and text cells also removing blanks using an array formula

Question: How do I sort text and numbers and also removing blanks using an array formula? Answer: Array formula in [โฆ]

This article demonstrates how to filter a unique distinct list sorted from A to Z:

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. [โฆ]

The following post shows you how to sort and filter duplicate values alphabetically:

Extract a list of alphabetically sorted duplicates from a column

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula [โฆ]

Learn how to sort a multi-column cell range alphabetically and display the values in a single column:

Sort a range from A to Z using array formula in excel

Question: How do I sort a range alphabetically using excel array formula? Answer: Array formula in B8: =INDEX(\$B\$2:\$E\$5, MIN(IF(SMALL(COUNTIF(\$B\$2:\$E\$5, "<"&\$B\$2:\$E\$5), [โฆ]

Sort a data set using an array formula:

Sort a table with an array formula

The sorted table to the right is created with an array formula using the data in the table to the left. Array [โฆ]

Tip! You can sort a data set in any way you like it, if you convert it to an excel defined table: Excel table - Sort data

### How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

Copy cell B2 and paste it down as far as needed.

Recommended article

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

### Explaining array formula in cell B2

=INDEX(\$A\$2:\$A\$15, MATCH(SMALL(COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), ROW(1:1)), COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), 0))

Step 1 - Count "smaller" values

COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition

=INDEX(\$A\$2:\$A\$15, MATCH(SMALL(COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), ROW(1:1)), COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), 0))

COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15)

becomes

COUNTIF({MM,BB,VV,EE,TT,GG,FF,KK,KK,II,CC,HH,LL,AA,NN}, "<"&MM,"<"&BB,"<"&VV,"<"&EE,"<"&TT,"<"&GG,"<"&FF,"<"&KK,"<"&II,"<"&CC,"<"&HH,"<"&LL,"<"&AA,"<"&NN)

The first value in this array formula is "MM". Letยดs see what happens when COUNTIF calculates how many values is small than "MM".

COUNTIF({MM,BB,VV,EE,TT,GG,FF,KK,II,CC,HH,LL,AA,NN}, "<"&"MM"

becomes

COUNTIF({MM"<"&"MM",BB"<"&"MM",VV"<"&"MM",EE"<"&"MM",TT"<"&"MM",GG"<"&"MM",FF"<"&"MM",KK"<"&"MM",II"<"&"MM",CC"<"&"MM",HH"<"&"MM",LL"<"&"MM",AA"<"&"MM",NN"<"&"MM"}

MM<MM is FALSE and BB<MM is TRUE and so on.. The array becomes:

COUNTIF({FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE}

becomes

COUNTIF({0, 1, 0, 1,0, 1 , 1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0} and the total is 10 which is the first number in the returning array. 0+1+0+1+0+1+1+1+1+1+1+1+1+1+1+1+0 = 10

Next is COUNTIF({MM,BB,VV,EE,TT,GG,FF,KK,II,CC,HH,LL,AA,NN}, "<"&"BB")

becomes

COUNTIF({MM"<"&"BB",BB"<"&"BB",VV"<"&"BB",EE"<"&"BB",TT"<"&"BB",GG"<"&"BB",FF"<"&"BB",KK"<"&"BB",II"<"&"BB",CC"<"&"BB",HH"<"&"BB",LL"<"&"BB",AA"<"&"BB",NN"<"&"BB"})

becomes

COUNTIF({FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,FALSE , TRUE, FALSE})

becomes

COUNTIF({0, 0, 0, 0,0, 0, 0,0,0,0,0,0,0,0,0,1,0}) and the total is 1 which is the second number in the returning array.

and so on...

COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15) returns this array:

{10, 1,13,3,12,5,4,8,7,2,6,9,0,11}

Recommended article

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

Step 2 - Return the k-th smallest number in array

SMALL(array,k) returns the k-th smallest row number in this data set.

SMALL({10, 1,13,3,12,5,4,8,7,2,6,9,0,11}, ROW(1:1)) returns the smallest number in this data set, 0 (zero).

Recommended article

How to use the SMALL function

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

Step 3 - Find position in array

MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value

MATCH(SMALL(COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), ROW(1:1)), COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), 0)

becomes

MATCH(0, {10, 1,13,3,12,5,4,8,7,2,6,9,0,11}, 0) and returns 13.

Recommended article

How to use the MATCH function

Identify the position of a value in an array.

Step 4 - Return value

INDEX(array,row_num,[column_num]) returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

=INDEX(\$A\$2:\$A\$15, MATCH(SMALL(COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), ROW(1:1)), COUNTIF(\$A\$2:\$A\$15, "<"&\$A\$2:\$A\$15), 0))

becomes

=INDEX(\$A\$2:\$A\$15, 13) and returns AA in cell B2.

Recommended article

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

Sorting-text-cells-using-array-formulas.xls
(Excel 97-2003 Workbook *.xls)

Recommended posts

Sorting numbers and text cells also removing blanks using an array formula

Question: How do I sort text and numbers and also removing blanks using an array formula? Answer: Array formula in [โฆ]

Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined [โฆ]

Create a drop down list containing only unique distinct alphabetically sorted text values

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using [โฆ]

Sorting numbers and text cells in an descending order also removing blanks

This blog article is one out of five articles on the same subject. Sorting text cells using array formula in [โฆ]

## Two columns sorting by the second column

Array Formula in cell E2:

=INDEX(\$B\$2:\$B\$9, MATCH(SMALL(COUNTIF(\$B\$2:\$B\$9, "<"&\$B\$2:\$B\$9), ROW(1:1)), COUNTIF(\$B\$2:\$B\$9, "<"&\$B\$2:\$B\$9), 0))

Array Formula in cell D2:

=INDEX(\$A\$2:\$A\$9, SMALL(IF(E2=\$B\$2:\$B\$9, MATCH(ROW(\$B\$2:\$B\$9), ROW(\$B\$2:\$B\$9))), SUM(--(E2=\$E\$2:E2))))