Excel - Sort with a Formula - Excel - Episode 1308

Microsoft Excel Tutorial: Sorting data with a Formula in Excel.

Welcome back to another exciting episode of the Dueling Excel podcast! In this episode, Bill Jelen from MrExcel and Mike Girvin from Excel Is Fun will be tackling the challenge of sorting data with a formula. This is episode number 58, and we have a great question from one of our viewers, OhmPaola from YouTube. They asked how to sort data while keeping the original order intact, and I have a feeling that Mike is going to come out on top with this one.

The challenge becomes even more difficult when there are ties involved. Bill's solution involves using the RANK function and a clever trick with font colors to hide the numbers used for sorting. However, this solution fails when there are ties in the data. Mike's solution, on the other hand, uses the LARGE and INDEX functions to sort the data and then uses the SMALL function to handle duplicates. This solution is more complex, but it works even when there are ties in the data.

One of the great things about these dueling Excel podcasts is that you get to see different approaches to solving the same problem. In this episode, you'll learn about the RANK, COUNTIF, VLOOKUP, LARGE, INDEX, and SMALL functions, as well as some handy tricks like using font colors to hide data and using the MrExcel trick for COUNTIFs. And if you're using Excel 2010 or later, you can even use the AGGREGATE function to avoid having to use array formulas.

We hope you enjoyed this episode and learned something new. Don't forget to download the workbook to see the solutions in action and try them out for yourself. And be sure to tune in next time for another exciting dueling Excel podcast from MrExcel and Excel Is Fun. Thanks for watching!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Table of Contents:
(00:00) Difficulty with ties in sorting formula
(00:29) Solution for sorting without ties
(00:50) Solution for sorting with ties
(01:01) Trick for hiding numbers in a formula
(01:20) Copying the formula for multiple columns
(01:30) Introduction to Mike's solution
(03:10) Explanation of the RANK, COUNTIF, and VLOOKUP functions
(03:26) Introduction to Bill's solution
(03:36) Explanation of using the LARGE function
(04:00) Explanation of using the ROWS function
(04:25) Explanation of using the INDEX function
(05:01) Explanation of using the MATCH function
(05:55) Explanation of using the SMALL function
(06:29) Explanation of using the IF function
(07:27) Explanation of using the COUNTIF function
(08:01) Explanation of using the AGGREGATE function
(08:43) Conclusion and handover to MrExcel
(09:20) Alternative solution from MrExcel
(09:30) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Aggregate function
Countif function
Index function
Insert cells in Excel
Large function
Match function
RANK function
Small function
Sort with a Formula
Ties in sorting
VLOOKUP function


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...

Mike "Excel Is Fun" Girvin and Bill "MrExcel" Jelen look at the uses of the RANK, VLOOKUP, COUNTIF, LARGE, ROWS, INDEX, MATCH and SMALL Functions - plus a few shortcuts you may find useful!