Sort by multiple columns

This article demonstrates how to sort a data set by multipe columns using the SORTBY function, Ecel Table and Excel formulas.  The example data used here is a table from the Premiere league, see image above.

This means that we need to sort the table displayed in the image above first by column K (PTS), second by column J (GD - Goal difference), and third by column H (GF - Goals for).

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

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

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.

Get the Excel file

Team-ranking-with-criteriav2.xlsx

Back to top

Recommended articles

Sort Two or More Columns (Contextures)

How to do a Multiple Level Data Sorting in Excel

Rank category

How to rank text uniquely without duplicates
Table of Contents How to rank text uniquely without duplicates How to rank uniquely based on a condition Get Excel […]

Excel categories

Comments (7)