Sort a range from A to Z [Array formula]

Author: Oscar Cronquist Article last updated on November 06, 2018

Question: How do I sort a range alphabetically using excel array formula?

Answer:

Cell range $B$2:$E$5 contains text values in random order, the formula in cell B8 extracts values sorted from A to Z.

Array formula in B8:

=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0))

Filter unique distinct values sorted from A to Z with no blanks from a multi-column and multi-row cell range:

Recommended articles

Extract unique distinct values A to Z from a range and ignore blanks
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

How to enter an array formula

Double press with left mouse button on cell B8
Copy an paste above formula
Press and hold CTRL + SHIFT simultaneously
Press Enter once

Learn how to filter unique distinct values from a multi-column and multi-row cell range:

Recommended articles

Extract unique distinct values from a multi-column cell range
Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

How to implement array formula to your workbook

If your list starts at, for example, F2. Change $B$8:B8 in the above formula to F2:$F$2.

The following article demonstrates how to filter a unique distinct list sorted from A to Z, from a multi-column and multi-row cell range:

Recommended articles

Extract a unique distinct list sorted from A-Z from range
The image above shows an array formula in cell B8 that extracts unique distinct values sorted alphabetically from cell range […]

How to copy array formula

Copy cell B8 and paste to cells below.

How to filter duplicate values from a multi-column and multi-row cell range:

Recommended articles

Extract duplicates from a multi-column cell range
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]

Explaining formula in cell B8

Step 1 - Rank values based on their relative position if they were sorted

The COUNTIF function counts cells based on a condition, however, in this case, I am using a cell range instead of a single cell in the second argument.

Also, the ampersand & concatenates each value with a less than sign. This makes the COUNTIF function compare each value against the others, the number returned represents the corresponding values position if the list were sorted.

COUNTIF($B$2:$E$5,"<"&$B$2:$E$5)

returns {1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}.

The image above shows the array in cell range B7:E10, "Apple" is 0 (zero) which is the first position if the list were sorted.

Step 2 - Extract the k-th smallest value in array

The SMALL function lets you get the k-th smallest value from a cell range or array.

SMALL(array, k)

SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))

The ROWS function allows you to keep track of the number of cells, the cell reference is expanding as we copy the formula and paste to cells below.

SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}, ROWS($B$8:B8))

becomes

SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}, 1) and returns 0 (zero).

Step 3 - Compare value to array

The logical expression in the following IF function determines if the value of the position corresponding to the k-th smallest value will be a row number or FALSE.

IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF(0=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF(0={1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)

becomes

IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, {1;2;3;4})

and returns {FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, 2, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}.

Step 4 - Extract the smallest number from array

The MIN function ignores boolean values TRUE and FALSE.

MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1))

becomes

MIN({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, 2, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE})

and returns 2. We now know that the first value to be extracted is located on row 2 relative to cell range $B$2:$E$5.

Step 5 - Find column

In this case, the MATCH function returns the relative position horizontally of the value we are looking for.

MATCH(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8)),COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)

becomes

MATCH(SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1},ROWS($B$8:B8)),COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)

becomes

MATCH(SMALL({1, 10, 13, 8; 6, 15, 0, 11; 12, 7, 9, 3; 14, 4, 5, 1},1),COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)

becomes

MATCH(0,COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)

I will be using the INDEX function to extract the values we need based on a row number.

MATCH(0,COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,MIN(IF(SMALL(COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROWS($B$8:B8))=COUNTIF($B$2:$E$5,"<"&$B$2:$E$5),ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)),,1)),0)

becomes

MATCH(0,COUNTIF($B$2:$E$5,"<"&INDEX($B$2:$E$5,2,,1)),0)

becomes

MATCH(0,COUNTIF($B$2:$E$5,"<"&B3:E3),0)

becomes

MATCH(0,{6,15,0,11},0)

and returns 3. The value we are looking for is in column 3 relative to cell range $B$2:$E$5.

Step 6 - Return value based on row and column

INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0))

becomes

INDEX($B$2:$E$5, 2, 3)

and returns "Apple" in cell B8.

Get Excel *.xlsx file

Range sorted from A to Z.xlsx

This post shows you how to filter a multi-column and multi-row cell range based on frequency:

Recommended articles

Sort a range based on value frequency
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

Recommended articles

A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.

Recommended articles

How to sort a data set using three different approaches, built-in tools, array formulas, and VBA
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

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

Recommended articles

Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

Recommended articles

Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Recommended articles

Sort values category

Sort a column alphabetically
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

Extract a unique distinct list sorted from A to Z ignore blanks
The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

Sort dates within a date range
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

Lookup and return multiple sorted values based on corresponding values in another column
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

Sort two columns
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

Sort column based on frequency
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]

Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

Sort values in a cell based on a delimiting character
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]

Sort items by adjacent number in every other value
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]

Sort values in an Excel table programmatically [VBA]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]

Sort a range based on value frequency
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

Sort records based on two columns
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]

Sort a list in random order in excel
Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

Sort based on frequency row-wise
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

Sort text values by length
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]

Extract a list of alphabetically sorted duplicates from a column
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]

Sort values by corresponding text arranged in a column
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]

Distribute values across numerical ranges
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

Functions in this article

INDEX MIN IF SMALL COUNTIF ROWS ROW MATCH

More than 1300 Excel formulas

Excel formula categories

Average Choose Combine Merge Compare Concatenate Conditional Formatting Count Count values Dates Duplicates Extract Filter Filter records Filtered values Filterxml Hyperlink If cell Index Match Logic Lookups Match Max Min Numbers in sum OR logic Overlapping Partial match Records Sequence Small Sort by Sort values Sum Sumifs Sumproduct String manipulation Time Unique distinct records Unique distinct values Unique records Unique values Vlookup Vlookup return values

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

5 Responses to “Sort a range from A to Z [Array formula]”

Macro to insert array formula says:

February 7, 2014 at 10:50 am

[…] = window.adsbygoogle || []).push({}); Hi, I'm working on a simple macro that would insert a simple sorting formula depending on the user selection. I took few lines of code to call a form enabling the range […]

Reply
HR says:

November 10, 2015 at 2:53 am

Hi, What if I want to sort alphabetically from B2 to E5 and keep the words within the range B2:E5? Thanks

Reply
fasal says:

February 28, 2016 at 9:35 am

how i sort a column through an excel formula

and can u explain about tbl in your formula because i did not get that

if u now about autofilter through a formula please give the formula

Reply
Markus says:

November 8, 2017 at 4:42 pm

Hi
Thank you for all the info your providing

How do I create a formula to consider a range of numbers, sort them in descending order then add up only the 6 highest numbers?

yours truely
Markus

Reply
ardiancharsy says:

October 26, 2018 at 4:35 am

in this case "Sort a range from A to Z [Array formula]"
how about remove blank cell ?
thanks

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form