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

Answer:

range-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), 1)

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

Extract a unique distinct list sorted alphabetically removing blanks from a range

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

Comments(21) Filed in category: Excel

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

Unique distinct values from multiple columns using array formula

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

Comments(23) Filed in category: Excel, Unique distinct values

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:

Extract a unique distinct list sorted from A-Z from range in excel

Inspired from a comment in this article Unique values from multiple columns using array formulas I have now created this […]

Comments(3) Filed in category: Excel, Unique distinct values

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

Extract duplicates from a range using excel array formula

Overview Inspired by a comment in this post Unique values from multiple columns using array formulas, I  created an array […]

Comments(0) Filed in category: Duplicate values, Excel

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

Sort a range by occurence using array formula in excel

Learn how to sort cell values by frequency.

Comments(4) Filed in category: Excel, Sort values

How to enter an array formula

  1. Double click on cell B8
  2. Copy an paste above formula
  3. Press and hold CTRL + SHIFT simultaneously
  4. Press Enter once

Recommended article

Learn the basics of Excel arrays

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

Comments(2) Filed in category: Count values, Excel

How to copy array formula

Copy cell B8 and paste to cells below.

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)

Recommended article

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 […]

Comments(1) Filed in category: Excel, Excel table, Sort values

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 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 […]

Comments(22) Filed in category: Excel

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 […]

Comments(13) Filed in category: Excel, Sort values

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 […]

Comments(76) Filed in category: Drop down lists, Excel

Sorting numbers and text cells descending also removing blanks using array formula in excel

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

Comments(0) Filed in category: Excel