Here is a simple macro-free alternative to Excel Data Sort, which will seamlessly sort data in a financial model or spreadsheet.

In some instances model users might prefer this approach, because it permits the preservation of source input data, whilst also allowing this data to be presented in an ascending or descending fashion.

To present a cleaner financial model, this video integrates Excel Conditional Formatting and Excel Data Group into this demonstration of sorting data.

Strategize Financial Modelling – Sorting data without Excel Data Sort.xls

Sorting data in Ascending Order

Firstly a COUNTIF of each row (preferably in a spare column such as column A) of input data is required, which will achieve the ranking of the individual cell compared to the range of data inputs. Remember blank data rows will return zero, which will be excluded from the precedent, sorted data table located underneath. It is fundamental to use the following formula or an alternative; otherwise the ranking of data rows will be erroneous.

=COUNTIF($B$5:$B$50,”<=”&B5)

VLOOKUP

Next, the Excel VLOOKUP function must be used in the sorted output table, in order to correctly sort each row of data from the precedent data table. Here is the formula, which must be applied in cell B53 of the above attached spreadsheet:

=VLOOKUP(ROW()-ROW($B$52),$A$5:B$51,COLUMNS($A:B),FALSE)

This is a complex formula, so please take your time in keying the exact details. However, the precedent, sorted table must only calculate non-blank rows of data (i.e. COUNTIF cells not equal to zero). Therefore, the above VLOOKUP formula must integrate an IF(ISERROR component, which will result in the following formula in cell B53:

=IF(ISERROR(VLOOKUP(ROW()-ROW($B$52),$A$5:B$51,COLUMNS($A:B),FALSE)),””,VLOOKUP(ROW()-ROW($B$52),$A$5:B$51,COLUMNS($A:B),FALSE))

Copy this formula across the entire table from row 53 to row 98.

Changing the Sorted column

Now the infrastructure of the sorted data output is in place, we can easily adjust the source column of the sorted output data.

What if we want to sort, in ascending order, column F Creditor instead of on column B invoice number? The COUNTIF formula range and criteria must both reflect column F. The following sorted, output table is now sorted based on each invoice row’s Creditor name.

A precedent drop-down list will restrict the financial model’s subsequent drop-down list, to simply the list of precedent business units that sell the specified product based on the input from the first drop-down list. The precedent drop-down list will prevent model users from erroneously selecting a business unit, even though this business unit does not sell the stipulated product .
 

Sorting data in Descending Order

How about if we instead want to sort data in the other fashion – in descending order? Let’s copy the previous worksheet of invoice data. For now, the hidden formatted cell will be unhidden because the previous COUNTIF formula must be adjusted, because blank rows must be discounted from the entire range of invoice rows to be counted.

Please remember, this is one approach to count a range in reverse or descending order; there are no reason why there aren’t other, alternative formula approaches.

Changing the Sorted Data

Like the Ascending Data example, perhaps we want to adjust the referenced sorted column? The following COUNTIF and COUNT formula will sort column H, invoice Description, which we will be adjusted change in the sorted VLOOKUP output invoices.

Again to highlight this. The source column to base the sort will be amended to reference column F – the invoice’s Creditor name.

As we can see below, the output table is sorted in descending order – based on the invoice’s Creditor name.

In Conclusion

So we now have a financial model that sorts data, either in ascending or descending order, which is an alternative to the Excel Data Sort function.