## 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:**

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**

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**

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**

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**

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**

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

#### How to enter an array formula

- Double click on cell B8
- Copy an paste above formula
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once

Recommended article

**Learn the basics of Excel arrays**

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**

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**

Comments(22) Filed in category: Excel

**Sort text cells alphabetically from two columns using excel array formula**

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

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

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

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

Comments(0) Filed in category: Excel

### Category: Sort values

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

Sort dates within a date range using excel array formula

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]Comments(23) Filed in category: Excel, Sort values

Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2 (array formula) How to […]Comments(16) Filed in category: Excel, Sort values

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]Comments(13) Filed in category: Excel, Sort values, Vlookup

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

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]Comments(12) Filed in category: Dates, Excel, Sort values

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

Comments(7) Filed in category: Excel, Mod, Sort values

Sort values in a cell using a custom delimiter (vba)

The following macro let´s you select a cell range and a delimiting character. The macro sorts the values in each […]Comments(5) Filed in category: Excel, Sort values

### 3 Responses to “Sort a range from A to Z using array formula in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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

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

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