How to use the SORTBY function

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array. It sorts values by column but keeps rows.

It is located in the Lookup and reference category and is only available to Excel 365 subscribers.

1. Syntax

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

2. Arguments

Argument	Text
array	Required. Cell range or array.
by_array1	Required. A cell range or array.
[sort_order1]	Optional. Sort order for argument by_array1. 1 - Ascending order (A to Z or small to large) -1 - descending order (Z to A or large to small). 1 is the default value if the argument is not specified.
[by_array2]	Optional. The cell range or array.
[sort_order2]	Optional. Sort order for argument [by_array2]. 1 - Ascending order (A to Z or small to large) -1 - descending order (Z to A or large to small). 1 is the default value if the argument is not specified.

3. Example

Formula in cell D3:

The formula demonstrated in the image above sorts the data in cell range B3:D8 by column B and then column C and lastly column D in ascending order, and returns the sorted array to cell F3. This is not possible using the SORT function, the SORTBY function is more advanced.

4. What is a spilled array formula?

Excel 365 automatically expands the output range based on the number of values in the array, this without requiring the user to enter the formula as an array formula.

This new behavior of Excel is called spilled array formula and is something only dynamic array formulas can do. Dynamic array formulas are only available to Excel 365 subscribers.

Back to top

5. Function not working

If the needed cell range is populated by any other value a #SPILL! error is returned by the SORTBY function. You have two options:

• Remove value leaving the cell blank.
• Enter the dynamic formula in another cell that has empty adjacent cells.

If a cell returns #NAME! error you have either spelled the function name wrong or you use an incompatible Excel version.

The image above shows that I spelled the SORTBY function wrong in the formula bar, cell F3 displays #NAME! error.

Only Excel 365 subscription version supports the new dynamic array formula like the SORTBY function, older Excel versions like Excel 2019, 2016, 2013, 2010, 2007 and earlier versions do not support the SORTBY function.

Here is how to find out your Excel version: Get your Excel version

5.1 Troubleshooting the error value

When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error if you press with left mouse button on it.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.

#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.

#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.

#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.

#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.

#NAME error - The #NAME error happens if you misspelled a function or a named range.

#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.

#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.

#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

5.2 The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue.

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

The image above shows cell reference C3:C8 converted to hard-coded value using the F9 key. The SORTBY function requires numerical values between 0 (zero) and 1 which is not the case in this example. We have found what is wrong with the formula.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

5.3 Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

Back to top

6. Does the function differentiate between upper and lower letters?

No, the SORTBY function is not case sensitive. The image above shows that item "APPLE" in upper letters is not sorted differently than item "apple" in lower letters.

Back to top

7. How to sort two columns from A to Z

Cells B3:B8 contains items and cells C3:C8 contains the corresponding code. The formula in cell E3 returns values to E3 as well as to cells below and to the right as far as needed. This is called spilling and is a new feature in Excel 365. The values are sorted based on the input values specified in the second and fourth arguments. In this case ascending order or from A to Z in both columns.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

The third argument and then every other argument lets you choose the sort order. If omitted 1 is used.

1 -> Ascending order (A to Z or small to large)
-1 -> descending order (Z to A or large to small)

The formula above sorts values from cell range B3:C8 by cell range B3:B8 from A to Z, then by C3:C8 from A to Z. The formula refreshes instantly if a value or more values are changed.

B3:C8 is a relative cell reference meaning it changes if you copy the cell and paste it to another cell. The dollar signs let you control between relative and absolute references. For example, $B$3:$C$8 is an absolute cell reference locked both horizontally and vertically.

Back to top

8. How to sort two columns from Z to A

Cells B3:B8 contains items and cells C3:C8 contains the corresponding code. The formula in cell E3 returns values to E3 as well as to cells below and to the right as far as needed. This is called spilling and is a new feature in Excel 365. The values are sorted based on the input values specified in the second and fourth arguments. In this case descending order or from Z to A in both columns.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

The third argument and then every other argument lets you choose the sort order. If omitted 1 is used.

1 -> Ascending order (A to Z or small to large)
-1 -> descending order (Z to A or large to small)

The formula above sorts values from cell range B3:C8 by cell range B3:B8 from Z to A, then by C3:C8 from Z to A. The formula refreshes instantly if a value or more values are changed.

B3:C8 is a relative cell reference meaning it changes if you copy the cell and paste it to another cell. The dollar signs let you control between relative and absolute references. For example, $B$3:$C$8 is an absolute cell reference locked both horizontally and vertically.

Back to top

9. Sort from smallest to largest

This example demonstrate how to sort a data set containing numbers from smallest to largest. Cells B3:B8 contain random integers and so does cells C3:C8. The formula in cell E3 returns values to E3 as well as to cells below and to the right as far as needed. This is called spilling and is a new feature in Excel 365. The values are sorted based on the input values specified in the second and fourth arguments. In this case ascending order or from small to large in both columns.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

The third argument and then every other argument lets you choose the sort order. If omitted 1 is used.

1 -> Ascending order (A to Z or small to large)
-1 -> descending order (Z to A or large to small)

The formula above sorts values from cell range B3:C8 by cell range B3:B8 from small to large, then by C3:C8 from small to large. The formula refreshes instantly if a value or more values are changed.

B3:C8 is a relative cell reference meaning it changes if you copy the cell and paste it to another cell. The dollar signs let you control between relative and absolute references. For example, $B$3:$C$8 is an absolute cell reference locked both horizontally and vertically.

Back to top

10. Sort from largest to smallest?

This example is almost identical to the one above except that the sort order is the opposite. It sorts the numbers in cell B3:B8 and C3:C8 from large to small. The formula in cell E3 returns values to E3 as well as to cells below and to the right as far as needed. This is called spilling and is a new feature in Excel 365. The values are sorted based on the input values specified in the second and fourth arguments. In this case descending order or from large to small in both columns.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

The third argument and then every other argument lets you choose the sort order. If omitted 1 is used.

1 -> Ascending order (A to Z or small to large)
-1 -> descending order (Z to A or large to small)

The formula above sorts values from cell range B3:C8 by cell range B3:B8 from large to small, then by C3:C8 from large to small.

Back to top

11. Sort by multiple columns?

The image above demonstrates a dynamic array formula in cell F3 that sorts values from cell range B3:D8 by column B in descending order, then by column C in ascending order, and lastly by column D in ascending order.

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

Back to top

12. Sort by another column?

The dynamic array formula in cell E3 sorts values from cell range B3:B8 by cell range C3:C8 in ascending order.

The SORTBY function allows you to sort values from a column by another column keeping rows intact. The image above demonstrates this, the fruits in column B are sorted by values in column C in ascending order (alphabetically).

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

Back to top

13. Sort by the first letter/digit? (Example 1)

Formula in cell D3:

Step 1 - Extract first character of each item

The LEFT function extracts a specific number of characters always starting from the left.

LEFT(text, [num_chars])

LEFT(B3:B10,1)

becomes

LEFT({"Banana"; "Avocado"; "Apple"; "Breadfruit"; "Blackcurrant"; "Blackberries"; "Blueberries"; "Apricots"}, 1)

and returns

{"B"; "A"; "A"; "B"; "B"; "B"; "B"; "A"}

Step 2 - Sort contents of cell range B3:B10 based on array

SORTBY(B3:B10,LEFT(B3:B10,1),1)

becomes

SORTBY(B3:B10,{"B"; "A"; "A"; "B"; "B"; "B"; "B"; "A"},1)

becomes

SORTBY({"Banana"; "Avocado"; "Apple"; "Breadfruit"; "Blackcurrant"; "Blackberries"; "Blueberries"; "Apricots"}, {"B"; "A"; "A"; "B"; "B"; "B"; "B"; "A"},1)

and returns

{"Avocado"; "Apple"; "Apricots"; "Banana"; "Breadfruit"; "Blackcurrant"; "Blackberries"; "Blueberries"}

in cell D3 and cells below.

Back to top

13.1 Sort by the first letter? (Example 2)

The values in cell range B3:B10 don't begin with a letter, they begin with a number, we need another formula to extract the first found letter.

Formula in cell C3:

Step 1 - Count characters

The LEN function counts the characters in a given value.

LEN(B3)

becomes

LEN("110 Banana")

and returns 10. "110 Banana" contains 10 characters excluding the double-quotes.

Step 2 - Create a sequence from 1 to the number of characters in cell

The SEQUENCE function returns an array of numbers based on given limits.

SEQUENCE(rows,[columns],[start],[step])

SEQUENCE(LEN(B3))

becomes

SEQUENCE(10)

and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}. There are ten numbers in the array from 1 to 10.

Step 3 - Extract each character in the cell

The MID function returns a part of a value based on the start character and number of characters.

MID(B3, SEQUENCE(LEN(B3)), 1)

becomes

MID(B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, 1)

becomes

MID("110 Banana", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, 1)

and returns the following array.

{"1"; "1"; "0"; " "; "B"; "a"; "n"; "a"; "n"; "a"}

Step 4 - Multiply each value in the array with 1

The purpose of this step is to check if the character is a letter or a number. Multiplying a number works fine, however, multiplying a letter with a number results in an error.

MID(B3, SEQUENCE(LEN(B3)), 1)*1

becomes

{"1"; "1"; "0"; " "; "B"; "a"; "n"; "a"; "n"; "a"}*1

and returns

{1; 1; 0; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!}

Step 5 - Check each value in array is an error

The ISERROR function returns true if the value is an error value and False if anything else.

ISERROR(MID(B3, SEQUENCE(LEN(B3)), 1)*1))

becomes

ISERROR({1; 1; 0; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!})

and returns

{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 6 - Replace each error with corresponding character and replace numbers with nothing

The IF function replaces error values with the value itself and other values with nothing.

IF(ISERROR(MID(B3, SEQUENCE(LEN(B3)), 1)*1)), MID(B3, SEQUENCE(LEN(B3)), 1), "")

becomes

IF({FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, MID(B3, SEQUENCE(LEN(B3)), 1), "")

becomes

IF({FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}, {"1"; "1"; "0"; " "; "B"; "a"; "n"; "a"; "n"; "a"}, "")

and returns

{""; ""; ""; " "; "B"; "a"; "n"; "a"; "n"; "a"}.

Step 7 - Join all remaining values in array

The TEXTJOIN function concatenates all values in an array.

TEXTJOIN("", TRUE, IF(ISERROR((MID(B3, SEQUENCE(LEN(B3)), 1)*1)), MID(B3, SEQUENCE(LEN(B3)), 1), ""))

becomes

TEXTJOIN("", TRUE, {""; ""; ""; " "; "B"; "a"; "n"; "a"; "n"; "a"})

and returns "Banana".

Step 8 - Remove leading and trailing blanks

The TRIM function removes leading and trailing blanks.

TRIM(TEXTJOIN("", TRUE, IF(ISERROR((MID(B3, SEQUENCE(LEN(B3)), 1)*1)), MID(B3, SEQUENCE(LEN(B3)), 1), "")))

becomes

TRIM("Banana")

and returns "Banana".

Step 9 - Shorten formula

The LET function lets you shorten big formulas.

TRIM(TEXTJOIN("", TRUE, IF(ISERROR((MID(B3, SEQUENCE(LEN(B3)), 1)*1)), MID(B3, SEQUENCE(LEN(B3)), 1), "")))

becomes

LET(x, MID(B3, SEQUENCE(LEN(B3)), 1), TRIM(TEXTJOIN("", TRUE, IF(ISERROR((x*1)), x, ""))))

Formula in cell E3:

Back to top

13.2 Sort by the first digit? (Example 3)

Formula in cell C3:

Step 1 - Count characters in cell

The LEN function counts the characters in a given value.

LEN(B3)

becomes

LEN("Banana 52")

and returns 9. There are nine characters in "Banana 52" excluding the double-quotes.

Step 2 - Create an array with numbers

The SEQUENCE function returns an array of numbers based on given limits.

SEQUENCE(rows,[columns],[start],[step])

SEQUENCE(LEN(B3))

becomes

SEQUENCE(9)

and returns {1; 2; 3; 4; 5; 6; 7; 8; 9}

Step 3 - Extract each character

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

Here is the syntax: MID(text, start_num, num_chars)

MID(B3, SEQUENCE(LEN(B3)),1)

becomes

MID(B3, {1; 2; 3; 4; 5; 6; 7; 8; 9},1)

becomes

MID("Banana 52", {1; 2; 3; 4; 5; 6; 7; 8; 9},1)

and returns {"B";"a";"n";"a";"n";"a";" ";"5";"2"}.

Step 4 - Identify numbers

This step multiplies all values in the array with 1, a letter returns an error value and a number returns a number.

MID(B3, SEQUENCE(LEN(B3)),1)*1

becomes

{"B";"a";"n";"a";"n";"a";" ";"5";"2"}*1

and returns {#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 5; 2}.

Step 5 - Create boolean values True or False

The IFERROR function returns a specified value if a value is an error and nothing for everything else.

IFERROR(MID(B3, SEQUENCE(LEN(B3)),1)*1,"")

becomes

IFERROR({#VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; #VALUE!; 5; 2},"")

and returns {"";"";"";"";"";"";"";5;2}.

Step 6 - Join values in array

The TEXTJOIN function combines text strings, here is the syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN("",TRUE,IFERROR(MID(B3, SEQUENCE(LEN(B3)),1)*1,""))

becomes

TEXTJOIN("",TRUE,{"";"";"";"";"";"";"";5;2})

and returns "52" in cell C3.

Back to top

14. Sort by absolute number?

Formula in cell E3:

Step 1 - Remove negative signs

The ABS function converts negative numbers to positive numbers.

ABS(C3:C8)

becomes

ABS({-5;-2;3;2;5;-5})

and returns {5; 2; 3; 2; 5; 5}.

Step 2 - Sort by numbers from small to large

SORTBY(B3:C8, ABS(C3:C8), 1)

becomes

SORTBY(B3:C8, {5; 2; 3; 2; 5; 5}, 1)

becomes

SORTBY({"Lemon",-5; "Apple",-2; "Banana",3; "Pear",2; "Orange",5; "Lime",-5}, {5; 2; 3; 2; 5; 5}, 1)

and returns

{"Apple",-2; "Pear",2; "Banana",3; "Lemon",-5; "Orange",5; "Lime",-5} in cell E3.

Back to top

15. Sort by the last word in a cell?

The image above demonstrates a dynamic array formula in cell D3 that sorts values from cell range B3:B8 by the last word in each cell.

Formula in cell D3:

Step 1 - Repeat blank

The REPT function repeats a text string based on a given number.

REPT(" ", 200)

returns a text string with 200 space characters.

Step 2 - Substitute blank with repeated blanks

The SUBSTITUTE function replaces all instances of a specific text string in a value.

SUBSTITUTE(B3:B8, " ", REPT(" ", 200))

becomes

SUBSTITUTE({"Lemon pie"; "Apple sauce"; "Banana split"; "Pear cream dessert"; "Orange jello"; "Lime cream"}," ",REPT(" ",200))

and returns

{"Lemon pie";"Apple sauce";"Banana split";"Pear cream dessert";"Orange jello";"Lime cream"}.

WordPress removes duplicate space characters, I hope you get the idea anyway.

Step 3 - Extracts 100 characters starting from the right

The RIGHT function extracts a specific number of characters always starting from the right.

RIGHT(SUBSTITUTE(B3:B8, " ", REPT(" ", 200)), 200)

returns

{" pie";" sauce";" split";" dessert";" jello";" cream"}

All blanks are not shown, WordPress removes duplicate space characters.

Step 4 - Remove remaining blanks

TRIM(RIGHT(SUBSTITUTE(B3:B8, " ", REPT(" ", 200)), 200))

returns

{"pie";"sauce";"split";"dessert";"jello";"cream"}

Step 5 - Sort B3:B8 by last word

SORTBY(B3:B8, TRIM(RIGHT(SUBSTITUTE(B3:B8, " ", REPT(" ", 200)), 200)))

becomes

SORTBY(B3:B8, {"pie";"sauce";"split";"dessert";"jello";"cream"})

becomes

SORTBY({"Lemon pie"; "Apple sauce"; "Banana split"; "Pear cream dessert"; "Orange jello"; "Lime cream"}, {"pie";"sauce";"split";"dessert";"jello";"cream"})

and returns

{"Lime cream"; "Pear cream dessert"; "Orange jello"; "Lemon pie"; "Apple sauce"; "Banana split"}

Back to top

16. Sort by first name?

The image above demonstrates a dynamic array formula in cell E3 that sorts values from cell range B3:B8 by the first name.

Formula in cell E3:

Back to top

17. Sort by last name?

The easiest way to sort last names and first names in the image above is to use the SORT function.

Formula in cell D3:

It sorts the array by the first column from A to Z if you omit all optional arguments.

SORT(array, [sort_index], [sort_order], [by_col])

Back to top

18. Sort by a custom list?

The formula in cell E3 rearranges the rows in cell range B3:C14 based on a custom list specified in cell range B17:B28.

Formula in cell E3:

Step 1 - Find position of item in a custom list

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(B3:B14, B17:B28, 0)

becomes

MATCH({"KT"; "MK"; "SZ"; "SQ"; "KP"; "XV"; "BA"; "OJ"; "JY"; "WD"; "TG"; "FV"},{"WD"; "SQ"; "TG"; "JY"; "OJ"; "KP"; "FV"; "KT"; "XV"; "SZ"; "BA"; "MK"},0)

and returns {8; 12; 10; 2; 6; 9; 11; 5; 4; 1; 3; 7}. These numbers represent the position in the custom list and they tell you how to sort the values.

Step 2 - Sort cell range B3:C14 based on numbers

SORTBY(B3:C14,MATCH(B3:B14,B17:B28,0))

becomes

SORTBY(B3:C14, {8; 12; 10; 2; 6; 9; 11; 5; 4; 1; 3; 7})

becomes

SORTBY({"KT","Banana"; "MK","Water melon"; "SZ","Blueberries"; "SQ","Lime"; "KP","grapefruits"; "XV","Apricots"; "BA","Lemon"; "OJ","Avocado"; "JY","Apple"; "WD","Blackberries"; "TG","Oranges"; "FV","Citrus"}, {8; 12; 10; 2; 6; 9; 11; 5; 4; 1; 3; 7})

and returns

{"WD","Blackberries"; "SQ","Lime"; "TG","Oranges"; "JY","Apple"; "OJ","Avocado"; "KP","grapefruits"; "FV","Citrus"; "KT","Banana"; "XV","Apricots"; "SZ","Blueberries"; "BA","Lemon"; "MK","Water melon"}

in cell E3 and cells below.

Back to top

19. Sort by month?

Formula in cell E3:

Step 1 - Convert values to numbers

The MATCH function allows you to calculate a position in a given array or cell range based on a lookup value. It returns a number that represents the position in the array.

MATCH(B3:B8, {"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"}, 0)

becomes

MATCH({"March"; "June"; "February"; "January"; "April"; "May"}, {"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"}, 0)

and returns

{3; 6; 2; 1; 4; 5}

The first number in the array is 3, it corresponds to March in cell range B3:B8. March is the third month. The second month in cell range B3:B8 is June, the corresponding number is 6. June is the sixth month.

Step 2 - Sort array based on numbers

SORTBY(B3:C8, MATCH(B3:B8, {"January"; "February"; "March"; "April"; "May"; "June"; "July"; "August"; "September"; "October"; "November"; "December"}, 0))

becomes

SORTBY(B3:C8, {3; 6; 2; 1; 4; 5})

becomes

SORTBY({"March", "Banana"; "June", "Avocado"; "February", "Apple"; "January", "Apricots"; "April", "Blueberries"; "May", "Blackberries"}, {3; 6; 2; 1; 4; 5})

and returns

{"January", "Apricots"; "February", "Apple"; "March", "Banana"; "April", "Blueberries"; "May", "Blackberries"; "June", "Avocado"}

Back to top

20. Sort by word length

The image above demonstrates a dynamic array formula in cell D3 that sorts the cell values in cell range B3:B14 by word length.

Formula in cell D3:

Step 1 - Calculate word length

The LEN function counts the number of characters in a cell or text string.

LEN(B3:B14)

becomes

LEN({"Banana"; "Water melon"; "Blueberries"; "Lime"; "grapefruits"; "Apricots"; "Lemon"; "Avocado"; "Apple"; "Blackberries"; "Oranges"; "Citrus"})

and returns

{6; 11; 11; 4; 11; 8; 5; 7; 5; 12; 7; 6}

Step 2 - Sort cell range B3:B14 by word length number

SORTBY(B3:B14, LEN(B3:B14))

becomes

SORTBY(B3:B14, {6; 11; 11; 4; 11; 8; 5; 7; 5; 12; 7; 6})

becomes

SORTBY({"Banana"; "Water melon"; "Blueberries"; "Lime"; "grapefruits"; "Apricots"; "Lemon"; "Avocado"; "Apple"; "Blackberries"; "Oranges"; "Citrus"}, {6; 11; 11; 4; 11; 8; 5; 7; 5; 12; 7; 6})

and returns

{"Lime"; "Lemon"; "Apple"; "Banana"; "Citrus"; "Avocado"; "Oranges"; "Apricots"; "Water melon"; "Blueberries"; "grapefruits"; "Blackberries"}

in cell range D3:D14.

Back to top

21. Sort in random order

Formula in cell D3:

Step 1 - Count the number of rows in cell range B3:B7

The ROWS function returns the number of rows from a cell range or array.

ROWS(B3:B7)

becomes

ROWS({"Banana";"Lemon";"Apple";"Pear";"Orange"})

and returns 5. There are five rows in cell range B3:B7.

Step 2 - Create random values between 0 (zero) and 1

The RANDARRAY function returns an array of randomized numbers, you can specify the size of the array, and if decimals or whole numbers are to be returned, also the min and max numbers.

RANDARRAY([rows],[columns],[min],[max],[whole_number])

We are going to specify only the row argument which makes the RANDARRAY function return random numbers based on the ROWS function.

RANDARRAY(ROWS(B3:B7))

becomes

RANDARRAY(5)

and returns 5 random decimal numbers.

{0.853939211740133; 0.581193997533527; 0.1410253631491; 0.747189425872485; 0.394921711301841}

Step 3 - Sort values in B3:B7 based on random values

SORTBY(B3:B7,RANDARRAY(ROWS(B3:B7)))

becomes

SORTBY(B3:B7, {0.853939211740133; 0.581193997533527; 0.1410253631491; 0.747189425872485; 0.394921711301841})

becomes

SORTBY({"Banana"; "Lemon"; "Apple"; "Pear"; "Orange"}, {0.853939211740133; 0.581193997533527; 0.1410253631491; 0.747189425872485; 0.394921711301841})

and returns values from cell range B3:B7 randomly.

{"Lemon";"Banana";"Orange";"Apple";"Pear"}

My formula is inspired by a formula made by David Hager found here: Excel #8 – Making a Bingo Card Using Only Formulas with Dynamic Array Functions

Back to top

22. Sort in unique numbers in random order

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and without enabling iterative calculation in excel options and not using "helper" columns?

Answer:

The numbers in cell range A2:A11 are random and between 1 and 10.

Press function key F9 to generate a new random sequence.

Array formula in A2:

Excel 365 formula in cell A2:

How to enter an array formula

1. Select cell A2
2. Copy / Paste above array formula to formula bar
   
3. Press and hold CTRL + SHIFT
4. Press Enter
5. Release all keys

Recommended articles

Copy cell A2 and paste down as far as needed.

Explaining array formula in cell A2

Step 1 - Create an array

ROW($1:$10) creates this array {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}

Step 2 - Create a criterion to avoid duplicate numbers

COUNTIF($A$1:A1, ROW($1:$10)) makes sure no duplicate numbers are created. The formula has both absolute and relative cell references ($A$1:A1). When the formula are copied down to cell A3 the cell reference changes to $A$1:A2. The value in cell A2 can´t be randomly selected again.

In cell A2, COUNTIF($A$1:A1, ROW($1:$10)) creates this array: {0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

Recommended articles

Step 3 - Create a new dynamic array

ROW($1:$10)*NOT(COUNTIF($A$1:A1, ROW($1:$10))) creates this array in cell A2: {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}

If the array formula  randomly selects the number 2 in cell A2, the formula in cell A3 creates this array: {1, 0, 3, 4, 5, 6, 7, 8, 9, 10}

Number 2 can´t be selected anymore.

Step 4 - Calculate the number range in Randbetween(bottom, top)

The bottom value is always 1. The top value changes depending on current cell.

In cell A2 the top value is 10.

In cell A3 the top value is 9

and so on..

Formula in cell A2: 11-ROW(A1) equals 10. (11-1=10)

Formula in cell A3: 11-ROW(A2) equals 9. (11-2=9)

and so on..

Step 5 - Create a random number

=LARGE(ROW($1:$10)*NOT(COUNTIF($A$1:A1, ROW($1:$10))), RANDBETWEEN(1,11-ROW(A1)))

RANDBETWEEN(1,11-ROW(A1))

becomes

RANDBETWEEN(1,11-1)

becomes

RANDBETWEEN(1,10)

and returns a random number between 1 and 10.

Recommended articles

Step 6 - Select a random number in array

=LARGE(ROW($1:$10)*NOT(COUNTIF($A$1:A1, ROW($1:$10))), RANDBETWEEN(1,11-ROW(A1)))

becomes

=LARGE({1, 2, 3, 4, 5, 6, 7, 8, 9, 10}, RANDBETWEEN(1,10))

becomes

=LARGE({1, 2, 3, 4, 5, 6, 7, 8, 9, 10}, random_number) and returns a random number between 1 and 10.

Recommended articles

How to customize array formula to your excel work sheet

If your list starts at F3 change $A$1:A1 to $F$2:F2 in the above array formula. To change the numbers from 1 to 10 to, for example, 2 to 12, change $1:$10 to $2:$12 also in the above array formula.
Press F9 to generate a new random list of unique numbers.

23. Sort by week number

The image above shows a dynamic array formula that sorts values from cell range B3:C19 by the corresponding week number.

Formula in cell E3:

Step 1 - Calculate week number

The ISOWEEKNUM function calculates a number of the ISO week number of the year for a given date.

ISOWEEKNUM(B3:B19)

returns

{22; 22; 22; 43; 18; 28; 23; 13; 34; 12; 41; 46; 49; 20; 14; 49; 42}

Step 2 - Sort by week number

SORTBY(B3:C19, ISOWEEKNUM(B3:B19))

becomes

SORTBY(B3:C19, {22; 22; 22; 43; 18; 28; 23; 13; 34; 12; 41; 46; 49; 20; 14; 49; 42})

and returns

{43909,"Milena"; 43918,"Rogan"; 43924,"Paolo"; 43949,"Dolores"; 43968,"Kingsley"; 43976,"Rohan"; 43979,"Finlay"; 43976,"Sneha"; 43985,"Tyson"; 44018,"Leandro"; 44066,"Hasnain"; 44114,"Mai"; 44116,"Jane"; 44124,"Winifred"; 44149,"Collette"; 44169,"Manraj"; 44166,"Virginia"}.

Back to top

24. Sort by quarter

The image above shows a dynamic array formula that calculates quarters based on dates in column B, it then sorts values from cell range B3:V19 by the quarters from small to large.

Formula in cell E3:

Step 1 - Calculate month number

The MONTH function returns a number from 1 to 12 from an Excel date representing the position. 1 - January, ... , 12 - December.

MONTH(B3:B19)

becomes

MONTH({43976; 43979; 43976; 44124; 43949; 44018; 43985; 43918; 44066; 43909; 44114; 44149; 44169; 43968; 43924; 44166; 44116})

and returns

{5; 5; 5; 10; 4; 7; 6; 3; 8; 3; 10; 11; 12; 5; 4; 12; 10}

Step 2 - Calculate quarter

The MATCH function returns a number representing the quarter, in this specific example.

MATCH(MONTH(B3:B19), {0;4;7;10}, 1)

becomes

MATCH({5; 5; 5; 10; 4; 7; 6; 3; 8; 3; 10; 11; 12; 5; 4; 12; 10}, {0;4;7;10}, 1)

and returns

{2; 2; 2; 4; 2; 3; 2; 1; 3; 1; 4; 4; 4; 2; 2; 4; 4}.

January -> 1 -> Quarter 1
February -> 2 -> Quarter 1
March -> 3 -> Quarter 1
April -> 4 -> Quarter 2
May -> 5 -> Quarter 2
June -> 6 -> Quarter 2
July -> 7 -> Quarter 3
August -> 8 -> Quarter 3
September -> 9 -> Quarter 3
October -> 10 -> Quarter 4
November -> 11 -> Quarter 4
December -> 12 -> Quarter 4

Step 3 - Sort by quarter

SORTBY(B3:C19, MATCH(MONTH(B3:B19), {0;4;7;10}, 1))

becomes

SORTBY(B3:C19, {2; 2; 2; 4; 2; 3; 2; 1; 3; 1; 4; 4; 4; 2; 2; 4; 4})

and returns

{43918,"Rogan"; 43909,"Milena"; 43976,"Rohan"; 43979,"Finlay"; 43976,"Sneha"; 43949,"Dolores"; 43985,"Tyson"; 43968,"Kingsley"; 43924,"Paolo"; 44018,"Leandro"; 44066,"Hasnain"; 44124,"Winifred"; 44114,"Mai"; 44149,"Collette"; 44169,"Manraj"; 44166,"Virginia"; 44116,"Jane"}

in cell F3.

Back to top

25. Sort based on row count

The image above demonstrates a formula in cell E3 that extracts unique distinct rows from cell range B3:C11 sorted by row count.

For example, row 3 contains "Banana" and "20". Row 7 contains "Banana" and "20" as well, which makes the total row count 2 for that particular row.

The dynamic array formula in cell G3 returns 2 in cell G4 which corresponds to "Banana" and "20" in cell range E4:F4.

Formula in cell E3:

Step 1 - Count duplicate rows

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

We have two columns and must use two pairs of arguments to be able to count rows. The result is an array containing numbers for each corresponding row.

COUNTIFS(B3:B11, B3:B11, C3:C11, C3:C11)

becomes

COUNTIFS({"Banana"; "Lemon"; "Banana"; "Pear"; "Banana"; "Lemon"; "Banana"; "Lemon"; "Pear"}, {"Banana"; "Lemon"; "Banana"; "Pear"; "Banana"; "Lemon"; "Banana"; "Lemon"; "Pear"}, {20; 30; 10; 5; 20; 30; 10; 30; 10}, {20; 30; 10; 5; 20; 30; 10; 30; 10})

and returns {2; 3; 2; 1; 2; 3; 2; 3; 1}.

The first number in the array corresponds to the first row and so on. 2 means that there is a duplicate row in cell range B3:C11, the blue arrows show where that duplicate row is located.

Step 2 - Sort array based on row count

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

The SORTBY function sorts values from cell range B3:C11 by the result array from the COUNTIFS function from large to small.

SORTBY(B3:C11, COUNTIFS(B3:B11, B3:B11, C3:C11, C3:C11), -1)

becomes

SORTBY(B3:C11, {2; 3; 2; 1; 2; 3; 2; 3; 1}, -1)

becomes

SORTBY({"Banana",20; "Lemon",30; "Banana",10; "Pear",5; "Banana",20; "Lemon",30; "Banana",10; "Lemon",30; "Pear",10}, {2; 3; 2; 1; 2; 3; 2; 3; 1}, -1)

and returns

{"Lemon",30; "Lemon",30; "Lemon",30; "Banana",20; "Banana",10; "Banana",20; "Banana",10; "Pear",5; "Pear",10}

Step 3 - Extract distinct rows

The UNIQUE function extracts unique distinct rows from the array.

UNIQUE(array,[by_col],[exactly_once])

UNIQUE(SORTBY(B3:C11, COUNTIFS(B3:B11, B3:B11, C3:C11, C3:C11), -1))

becomes

UNIQUE({"Lemon",30; "Lemon",30; "Lemon",30; "Banana",20; "Banana",10; "Banana",20; "Banana",10; "Pear",5; "Pear",10})

and returns

{"Lemon",30,3; "Banana",20,2; "Banana",10,2; "Pear",5,1; "Pear",10,1}

in cell E3.

Formula in cell G3:

I recommend a Pivot Table if you have a very large data set and experience a slow responding worksheet, it is lightning fast.

Back to top

26. Sort numbers based on proximity to a given number

The image above demonstrates an array formula in cell C5 that sorts numbers, specified in cell range B5:B17, based on how far off they are from the given number in cell C2. Example, 9 and 11 are closest to 10 and are extracted first, displayed in cell C5 and C6.

Excel 365 formula:

Previous Excel versions:

27. Sort based on frequency row-wise

In this section, I will demonstrate two techniques for counting per row. The first example is simple and straightforward.

The second example is a bit more complicated but dynamic and automatic, you only need to provide a search string, everything else is calculated by array formulas.

29.1. Count value per row

The first example has this data table, see image above. It is an Excel Table, select any cell in the data set and press CTRL + T to convert the data to an Excel Table.

I want to count the value "C" per row.
In cell J3, type:

Copy cell I2 and paste to I3:I10. You don't need to do this if you use an Excel Table as I did, see the image above. The Excel Table copies the formulas to cells below automatically.

The COUNTIF function counts the number of cells that meet a given condition.

There are 4 C's in row 2 so the formula returns 4 in cell J3. If you change the value in cell J2 to "B", the formulas in J3:J11 recalculates and return new values.

Let us sort the Excel table.

Drop-down list arrows appear next to each header, see picture above. Press with left mouse button on the arrow next to "C".

Press with left mouse button on "Largest to smallest". The data table shows the count of "C" per row, sorted largest to smallest.

Back to top

Learn more about the COUNTIF function.

Do you know why $I$1 in COUNTIF(B2:H2,$I$1) has dollar signs?
Read this post: Absolute and relative cell references

Back to top

29.2. Dynamic counting per row sorted from large to small

Now on to a more interesting and complicated example. The formula here returns names sorted in column E based on the number of "C"s per row.

How is this possible? The MMULT function is able to count cells per row based on a condition, it returns an array that corresponds to the number of rows. Change the value in cell C3 and the list in cell range E3:F11 is instantly changed.

I have applied conditional formatting to cell range B14:I22 so you can easily verify the calculated numbers in E3:E11.

Array formula in cell E3:

Excel 365 dynamic array formula in cell E3:

The formula above works only in Excel 365, it contains a new function: SORTBY.

Array formula in cell E3:

Back to top

Explaining array formula in cell E3

Step 1 - Find search value in cell range

The equal sign compares the value in cell C3 to values in cell range c14:I22.

$C$14:$I$22=$C$3

becomes

{"C", "C", "B", "C", "C", "A", "B";"B", "B", "B", "C", "C", "C", "A";"C", "B", "B", "A", "A", "A", "A";"A", "B", "B", "C", "C", "C", "C";"A", "B", "C", "C", "C", "C", "B";"C", "B", "B", "A", "A", "C", "B";"A", "A", "C", "C", "C", "C", "B";"B", "A", "A", "A", "A", "B", "B";"B", "B", "A", "B", "A", "C", "B"}="C"

and returns

{TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE}

Step 2 - Convert boolean values to numerical values

The parentheses lets you control the order of calculation, we want to compare $B$14:$H$22 with $B$3 before we multiply with 1.

($B$14:$H$22=$B$3)*1

becomes

{TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE;FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE}*1

and returns

{1, 1, 0, 1, 1, 0, 0;0, 0, 0, 1, 1, 1, 0;1, 0, 0, 0, 0, 0, 0;0, 0, 0, 1, 1, 1, 1;0, 0, 1, 1, 1, 1, 0;1, 0, 0, 0, 0, 1, 0;0, 0, 1, 1, 1, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0}.

Step 3 - Create an array containing corresponding column numbers

The COLUMN function returns the column number from a cell reference.

COLUMN($B$14:$H$22)

returns {2, 3, 4, 5, 6, 7, 8}.

Step 4 - Change numbers in array to 1

COLUMN($B$14:$H$22)^0

becomes

{2, 3, 4, 5, 6, 7, 8}^0

and returns {1, 1, 1, 1, 1, 1, 1}.

Step 5 - Evaluate MMULT function

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0))

becomes

MMULT({1, 1, 0, 1, 1, 0, 0;0, 0, 0, 1, 1, 1, 0;1, 0, 0, 0, 0, 0, 0;0, 0, 0, 1, 1, 1, 1;0, 0, 1, 1, 1, 1, 0;1, 0, 0, 0, 0, 1, 0;0, 0, 1, 1, 1, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0}*1, {1, 1, 1, 1, 1, 1, 1})

and returns

{4; 3; 1; 4; 4; 2; 4; 0; 1}

Step 6 - Extract kth largest number

The LARGE function returns the kth largest number from a cell range or array.

LARGE(array, k)

LARGE(MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), ROWS($B$1:B1))

becomes

LARGE({4; 3; 1; 4; 4; 2; 4; 0; 1}, ROWS($B$1:B1))

The ROWS function returns a number representing the number of rows in a cell reference.

Cell reference $B$1:B1 grows when the cell is copied and pasted to cells below. This makes the ROWS function return a new number in each cell.

LARGE({4; 3; 1; 4; 4; 2; 4; 0; 1},1)

and returns 4.

Step 7 - Count values above the current cell

The COUNTIF function counts cells that equal a given condition. The cell reference $E$2:$E2 grows when the cell is copied and pasted to cells below, this lets the formula check all previous values.

COUNTIF($E$2:$E2, $B$14:$B$22)=0

becomes

{0;0;0;0;0;0;0;0;0}=0

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 8 - Filter count numbers based on previous values above

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(COUNTIF($E$2:$E2, $B$14:$B$22)=0, MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), "")

becomes

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

and returns {4; 3; 1; 4; 4; 2; 4; 0; 1}.

Step 9 - Find the relative position of the kth largest number in the array

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(LARGE(MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), ROWS($B$1:B1)), IF(COUNTIF($E$2:$E2, $B$14:$B$22)=0, MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), ""), 0)

becomes

MATCH(4, {4; 3; 1; 4; 4; 2; 4; 0; 1}, 0)

and returns 1.

Step 10 - Get value

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX($B$14:$B$22, MATCH(LARGE(MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), ROWS($B$1:B1)), IF(COUNTIF($E$2:$E2, $B$14:$B$22)=0, MMULT(($C$14:$I$22=$C$3)*1, TRANSPOSE(COLUMN($C$14:$I$22)^0)), ""), 0))

becomes

INDEX($B$14:$B$22, 1)

and returns "Wilhelm" in cell D3.

Back to top

Check out: Dynamic scoreboard

29.3 Get Excel file

Back to top

28. Sort by multiple columns using an Excel Table

The Excel Table is capable of sorting multiple columns in a specified order ascending and descending and which order sorting columns by. The image above shows buttons next to each column header name, next to the black triangle is a down-pointing arrow indicating that the column is sorted.

The image shows three down-pointing arrows, column J (PTS), I (GD) and G (GF). The table is sorted record by record based on the values in column J, I and G and in that order.

Convert data to an Excel Table

1. Select any cell in the data set.
2. Press CTRL + T to open the "Create Table" dialog box. See image above.
3. Press with left mouse button on OK button.

Apply custom sort to an Excel Table

1. Press with right mouse button on on any cell in the Excel Table.
2. A pop-up menu appears, see image above. Press with left mouse button on or hover over "Sort".
3. Another pop-up menu shows up, press with left mouse button on "Custom Sort...".
4. A dialog box appears.
   
5. Press with mouse on the first drop-down list, see image above.
6. Press with mouse on the column header that you want to sort first by. I chose PTS.
7. The "A to Z" drop-down list changed to "Smallest to Largest". Press with mouse on it to expand and see all drop-down list values.
   
8. Press with mouse on "Largest to Smallest".
9. Press with mouse on "Add Level" button.
   
10. Now select the next column header name to sort by. I chose "GD".
11. Repeat with the next column header name (GF).
12. Press with left mouse button on OK button to apply settings.

Back to top

29. Sort by multiple columns using the SORTBY function

Formula in cell B3:

The SORTBY function is available for Excel 365 subscribers, it has the following arguments:

SORTBY(array, by_array1, [sort_order1], [by_array2] , [sort_order2],…)

array - An array or cell reference to be sorted

by_array1 - An array or cell reference to be sorted on, this does not have to be a column in the array range. It can be a column outside the array range as well.

[sort_order1] - 1 for ascending, -1 for descending. Default value is -1 (ascending).

The arguments in brackets are optional arguments.

The formula above is a dynamic array formula meaning it spills values across columns and rows automatically.

Spilling is something dynamic array formulas do when they extend to adjacent cells as far as needed. The #SPILL! error is returned if a cell is not empty meaning dynamic array formulas will never overwrite populated cells.

As soon as you delete the contents of populated cells the dynamic array formula automatically extends to neighboring cells.

Back to top

30. Sort by multiple columns using formula (Previous Excel versions)

The following formula sorts the table keeping the rows intact just like the above examples, the data source is in cell range B25:J44.

Array formula in cell B3:

How to enter an array formula in cell B3

1. Copy the above formula.
2. Double press with left mouse button on cell B3, a prompt appears in cell B3.
3. Paste formula to the cell.
4. Press and hold CTRL and SHIFT simultaneously.
5. Press Enter once.
6. Release all keys.

The formula begins and ends now with a curly bracket, like this: {=array_formula}

How to copy array formula

1. Press with mouse on cell B3 to select it.
2. Press CTRL + c to copy cell.
3. Select cell range C3:J3.
4. Paste to cell range C3:J3. CTRL + v.
5. Select cell range B4:J22.
6. Paste to cell range B4:J22. CTRL + v.

Explaining formula in cell B3

The "Evaluate Formula" tool allows you to troubleshoot and examine formulas in great detail. Go to tab "Formulas" on the ribbon. Press with mouse on "Evaluate Formula" button.

A dialog box appears, see image above. Press with left mouse button on the Evaluate button repeatedly to move through the calculation step by step.

Step 1 - Get points from column J

The following cell reference gets the points from the table.

$J$25:$J$44

returns

{48; 47; 46; 42; 41; 40; 37; 33; 30; 23; 23; 22; 21; 21; 20; 19; 18; 18; 17; 17}

Step 2 - Adjust negative numbers (GD) and divide with 1000

The Goal difference number can be negative, to adjust for that I am using the MIN function. The MIN function finds the smallest number in the cell range.

The ABS function removes the sign.

($I$25:$I$44+MIN($I$25:$I$44))/1000

becomes

({22; 36; 21; 25; 15; 1; 11; 2; 4; -5; -7; -13; -5; -5; -18; -24; -9; -19; -15; -18}+ABS(MIN($I$25:$I$44)))/1000

becomes

({22; 36; 21; 25; 15; 1; 11; 2; 4; -5; -7; -13; -5; -5; -18; -24; -9; -19; -15; -18}+ABS(MIN({22; 36; 21; 25; 15; 1; 11; 2; 4; -5; -7; -13; -5; -5; -18; -24; -9; -19; -15; -18})))/1000

becomes

({22; 36; 21; 25; 15; 1; 11; 2; 4; -5; -7; -13; -5; -5; -18; -24; -9; -19; -15; -18}+ABS(-24))/1000

becomes

({22; 36; 21; 25; 15; 1; 11; 2; 4; -5; -7; -13; -5; -5; -18; -24; -9; -19; -15; -18}+24)/1000

becomes

{46; 60; 45; 49; 39; 25; 35; 26; 28; 19; 17; 11; 19; 19; 6; 0; 15; 5; 9; 6}/1000

and returns

{0.046; 0.06; 0.045; 0.049; 0.039; 0.025; 0.035; 0.026; 0.028; 0.019; 0.017; 0.011; 0.019; 0.019; 0.006; 0; 0.015; 0.005; 0.009; 0.006}

Step 3 - Divide with 10000

We can't just add the goal difference to the points, that would destroy the sorting.

$G$25:$G$44/10000

becomes

{41; 20; 15; 40; 13; 34; 22; 22; 51; 59; 35; 29; 17; 27; 22; 19; 26; 26; 23; 21}/10000

and returns

{0.0041; 0.002; 0.0015; 0.004; 0.0013; 0.0034; 0.0022; 0.0022; 0.0051; 0.0059; 0.0035; 0.0029; 0.0017; 0.0027; 0.0022; 0.0019; 0.0026; 0.0026; 0.0023; 0.0021}

Step 4 - Add numbers

Step 4 adds the results from step to step 3.

$J$25:$J$44+($I$25:$I$44+MIN($I$25:$I$44))/1000+$G$25:$G$44/10000

becomes

{48; 47; 46; 42; 41; 40; 37; 33; 30; 23; 23; 22; 21; 21; 20; 19; 18; 18; 17; 17} + {0.046; 0.06; 0.045; 0.049; 0.039; 0.025; 0.035; 0.026; 0.028; 0.019; 0.017; 0.011; 0.019; 0.019; 0.006; 0; 0.015; 0.005; 0.009; 0.006} + {0.0041; 0.002; 0.0015; 0.004; 0.0013; 0.0034; 0.0022; 0.0022; 0.0051; 0.0059; 0.0035; 0.0029; 0.0017; 0.0027; 0.0022; 0.0019; 0.0026; 0.0026; 0.0023; 0.0021}

and returns

{48.0501; 47.062; 46.0465; 42.053; 41.0403; 40.0284; 37.0372; 33.0282; 30.0331; 23.0249; 23.0205; 22.0139; 21.0207; 21.0217; 20.0082; 19.0019; 18.0176; 18.0076; 17.0113; 17.0081}

Step 5 - Calculate k-th largest number

The LARGE function extracts the k-th largest number in an array or cell range. LARGE(array, k)

LARGE($J$25:$J$44+($I$25:$I$44+ABS(MIN($I$25:$I$44)))/1000+$G$25:$G$44/10000, ROWS($A$1:A1))

becomes

LARGE({48.0501; 47.062; 46.0465; 42.053; 41.0403; 40.0284; 37.0372; 33.0282; 30.0331; 23.0249; 23.0205; 22.0139; 21.0207; 21.0217; 20.0082; 19.0019; 18.0176; 18.0076; 17.0113; 17.0081}, ROWS($A$1:A1))

The ROWS function calculates the number of rows in a given cell reference. The cell reference in this case grows when we copy the cell and paste to cells below. This is possible if we use both an absolute and relative cell reference. This will  return a new value in each cell.

LARGE({48.0501; 47.062; 46.0465; 42.053; 41.0403; 40.0284; 37.0372; 33.0282; 30.0331; 23.0249; 23.0205; 22.0139; 21.0207; 21.0217; 20.0082; 19.0019; 18.0176; 18.0076; 17.0113; 17.0081}, ROWS($A$1:A1))

becomes

LARGE({48.0501; 47.062; 46.0465; 42.053; 41.0403; 40.0284; 37.0372; 33.0282; 30.0331; 23.0249; 23.0205; 22.0139; 21.0207; 21.0217; 20.0082; 19.0019; 18.0176; 18.0076; 17.0113; 17.0081}, 1)

and returns

48.0501

Step 6 - Find relative position

The MATCH function calculates the relative position of a given value in a cell range or an array. MATCH(lookup_value, lookup_array, [match_type])

MATCH(LARGE($J$25:$J$44+($I$25:$I$44+ABS(MIN($I$25:$I$44)))/1000+$G$25:$G$44/10000, ROWS($A$1:A1)), $J$25:$J$44+($I$25:$I$44+ABS(MIN($I$25:$I$44)))/1000+$G$25:$G$44/10000, 0)

becomes

MATCH(48.0501, $J$25:$J$44+($I$25:$I$44+ABS(MIN($I$25:$I$44)))/1000+$G$25:$G$44/10000, 0)

becomes

MATCH(48.0501, {48.0501; 47.062; 46.0465; 42.053; 41.0403; 40.0284; 37.0372; 33.0282; 30.0331; 23.0249; 23.0205; 22.0139; 21.0207; 21.0217; 20.0082; 19.0019; 18.0176; 18.0076; 17.0113; 17.0081}, 0)

and returns 1.

Step 7 - Return value

The INDEX function returns a value based on a specified row and optional column number.

INDEX($B$25:$J$44, MATCH(LARGE($J$25:$J$44+($I$25:$I$44+ABS(MIN($I$25:$I$44)))/1000+$G$25:$G$44/10000, ROWS($A$1:A1)), $J$25:$J$44+($I$25:$I$44+ABS(MIN($I$25:$I$44)))/1000+$G$25:$G$44/10000, 0), COLUMNS($A$1:A1))

becomes

INDEX($B$25:$J$44, 1, COLUMNS($A$1:A1))

The COLUMNS function returns the number of columns in a given cell range. This will make the INDEX function return the correct value in each cell.

INDEX($B$25:$J$44, 1, COLUMNS($A$1:A1))

becomes

INDEX($B$25:$J$44, 1, 1)

and returns "Arsenal" in cell B3.

Back to top

Recommended articles

Sort Two or More Columns (Contextures)

How to do a Multiple Level Data Sorting in Excel

'SORTBY' function examples

Functions in 'Lookup and reference' category

The SORTBY function function is one of 25 functions in the 'Lookup and reference' category.

Excel function categories

Excel categories

Comments (114)