This blog article is one out of five articles on the same subject.

Sorting text cells using array formula in excel
Sorting numbers and text cells also removing blanks using array formula in excel
Sorting numbers and text cells descending also removing blanks using array formula in excel
Sort text cells alphabetically from two columns using excel array formula
Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
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(tbl, MIN(IF(SMALL(COUNTIF(tbl, "<"&tbl), ROWS($B$8:B8))=COUNTIF(tbl, "<"&tbl), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(SMALL(COUNTIF(tbl, "<"&tbl), ROWS($B$8:B8)), COUNTIF(tbl, "<"&INDEX(tbl, MIN(IF(SMALL(COUNTIF(tbl, "<"&tbl), ROWS($B$8:B8))=COUNTIF(tbl, "<"&tbl), ROW(tbl)-MIN(ROW(tbl))+1)), , 1)), 0), 1) + CTRL + SHIFT + ENTER copied down as far as necessary.

Named ranges
tbl (B2:E5)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your list starts at, for example, F2. Change $B$8:B8  in the above formula to F2:$F$2.

Download excel example file.
range-sorted-from-a-to-z.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified 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

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

ROWS(array) returns the number of rows in a reference or an array

This blog article is one out of five articles on the same subject.

Sorting text cells using array formula in excel
Sorting numbers and text cells also removing blanks using array formula in excel
Sorting numbers and text cells descending also removing blanks using array formula in excel
Sort text cells alphabetically from two columns using excel array formula
Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
Sort a range from A to Z using array formula in excel

Related posts:

1. Sort text cells alphabetically from two columns using excel array formula
2. Sorting numbers and text cells descending also removing blanks using array formula in excel
3. Sorting numbers and text cells also removing blanks using array formula in excel
4. Sorting text cells using array formula in excel
5. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
6. Sort a range by occurence using array formula in excel
7. Filter unique rows and sort by date using array formula in excel
8. Sort values in parallel (array formula)
9. Filter duplicate rows and sort by date using array formula in excel