Excel Sort Formulas – How to Sort Dynamic Data

Apply a Normal Data Sort or Filter to Your Sheet

To isolate specific pieces of information for examination:

1. Click any cell in the column you want to filter.
2. Add filters to your data. You can do this a couple different ways.

• On the Home tab, click the Sort & Filter button in the Editing group
• – OR –
• On the Data tab, click the Filter button in the Sort & Filter group

1. Click the dropdown arrows that appear at the top of each column to reveal sorting and filtering choices for that column of data.

For example, if you uncheck the Select All box and check the box(es) for the 1123 Store number in the Store column, you can see only those sales that came from that store.

1. Review your selection. A filter icon appears by the dropdown arrows that are currently being filtered. A sort arrow appears in the column that has been sorted.
2. Click the Filter button again (see step 2) to turn filters off, or select Clear Filter From “…” in the column filter dropdown menu (see step 3). Note that the rows will remain in the order of the last sort, even after filter arrows have been removed.

This can provide you many ways to sort and view your data to help you make sense out of it. But what if your data changes frequently?

Sort Dynamic Data

The answer might be to create a formula that does your sorting work for you. As with lots of Excel "tricks" of this nature, however, what you gain in analysis you might lose in convenience. Or, in simplicity at least.

Let’s look at one way to sort dynamic information and then explore the limitations of the results!

Our example will allow us to take a table of sales figures and generate a sorted list that shows us our top performers for the quarter. We could sort our report by Quarter Totals (Column E), but then the rest of our year would be out of order. We could only see sales ranking one quarter at a time. Because we receive our source data alphabetically by salesperson, we would also have to re-sort by salesperson each time we add another quarter.

This is what our table might look like as the year progresses…

Instead, we will use the COUNTIF, INDEX, MATCH, and ROWS functions to create a formula that will sort our sales figures and display them in a new table. This takes two steps.

Create a Helper Column to Calculate Relative Rank

We will first generate a 1-10 numerical ranking of the information we want sorted. This will not "rearrange" the information, but tell us its place in the list. The formula that goes in G2 is:

=COUNTIF(E$2:E$11,"<="&$E2)

·        =COUNTIF() tells Excel to count the items in a range and under what conditions they are to be counted.

·        E$2:E$11 describes the range we want counted.

·        "<="&$E2 tell excel to check the relative rank of the value compared to the entire range.

Enter the formula, then drag to copy it into each cell in the column. The result should be a number from 1-10 in each row.

Reorder the Data based on its rank using INDEX, MATCH, and ROW

As we learned in Using the INDEX function in an Excel Formula, INDEX tells Excel to get a value in a specific row and column. We will use it here to help us match the correct rank with the correct sales total, and then return the list in ascending order. The sort formula we will put in H2 is:

=INDEX($E$2:$E$11,MATCH(ROWS($G$2:G2),$G$2:$G$11,0))

·        =INDEX() tells excel to return information from a certain cell and column

·        $E$2:$E$11 specifies the range to look in

·        MATCH(ROWS($G$2:G2),$G$2:$G$11,0 – this function tells excel which row to look in

·        MATCH() tells excel to choose the row the INDEX will use based on the value in the helper column

·        ROWS($G$2:G2) tells the MATCH function what rank in the list should be matched by "counting down" from the first cell in the series. Note that the 2nd "G2" is not absolute, meaning it will change with the row when the formula is dragged and copied.

If we want to also display the names of the salespeople next to the sorted sales totals, we would simply add the same formula to the next column, but change the INDEX function to return values from the Salesperson column:

=INDEX($A$2:$A$11,MATCH(ROWS($G$2:G2),$G$2:$G$11,0))

The Rub

Before we get too excited about this method for sorting data using an Excel function, there's a catch. Sadly, these formulas will NOT work if:

·        There are any duplicates in the data

·        There are any blank cells in the data

·        You have a mix of numbers and text. You can sort ALL text or ALL numbers, but a mixture of the two will generate errors.

But, if your data is rarely likely to generate duplicates and is very consistent such as the above example, this technique can deliver a lot of value. If your data is more complex in the above ways, you will at least have a starting place to build a sort formula that can work with the exceptions.