MrExcel Publishing
Your One Stop for Excel Tips & Solutions
Microsoft MVP

Excel Sort With a Formula Using SORT and SORTBY

September 25, 2018 - by Bill Jelen

Excel Sort With a Formula Using SORT and SORTBY

This week at the Ignite Conference in Orlando Florida, Microsoft debuted a series of new, easier array formulas in Excel. I will be covering these new formulas every day this week, but if you would like to read ahead:

Sorting with a Formula in Excel used to require an insane combination of formulas. Take a look at this data which will be used throughout this article.

Data in A3:C11
Data in A3:C11.

In order to sort this with a formula before this week, you would just have to knock out RANK, COUNTIF, MATCH, INDEX and INDEX. Once you finished this set of formulas, you would be ready for a nap.

The old way to sort with a formula
The old way to sort with a formula

Joe McDaid and his team have brought us SORT and SORTBY.

Let's start with SORT. Here is the syntax =SORT(Array, [Sort Index], [Sort Order], [By Column])

continue reading »

More Reading

Excel Formulas Now Spill Into Adjacent Cells - Major Calc Engine Change - Breaking News

Excel Formulas Now Spill Into Adjacent Cells - Major Calc Engine Change - Breaking News »

September 24, 2018 - by Bill Jelen

Today at the Ignite Conference in Orlando Florida, Microsoft debuted a major change to the calculation engine. Every function is now treated as an array formula and there is no more need to press Ctrl+Shift+Enter. This will lead to a rapid increase in the number of functions introduced in Office 365.

Notes and Threaded Comments in Excel

Notes and Threaded Comments in Excel »

September 18, 2018 - by Bill Jelen

Build 1810 of Excel in Office 365 introduces a new threaded comment style. Thankfully, the old comments are still around, now called Notes.

read more articles »

IMA Data Analytics Leveraging Excel

Featured Products

Your Excel Survival Kit

Your Excel Survival Kit »

This book helps you realize that Excel is on your side. It can be your friend, not your enemy. If and when you upgrade your Excel skills you will experience many positives: you will learn to love Excel, you will learn how to use Excel to become an invaluable asset in your workplace.

Excel 2016 in Depth

Excel 2016 in Depth »

Experienced with Excel? Don’t let Excel 2016 make you feel like a beginner again! This new full-color edition of the bestselling book has been completely overhauled. Gone is unnecessary and rarely used content; emphasis is on the most-used and new aspects of Excel 2016.

Excel Dynamic Arrays Straight to the Point

Excel Dynamic Arrays Straight to the Point »

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.

Excel JavaScript UDFs Straight to the Point

Excel JavaScript UDFs Straight to the Point »

JavaScript custom functions - UDFs can be used like any other native functions or UDFs in Excel. This book shows the process of creating JavaScript UDFs in Excel Developer Preview for Office Insider program subscribers.

Supercharge Power BI

Supercharge Power BI »

If you want to be able to supercharge Power BI, you need to learn to write DAX. Data Analysis Expressions (DAX) is the formula language of Power BI and Power Pivot for Excel. This book prompts the reader to put your new skills to the test. Written to give you hands-on practice using Power BI Desktop and writing DAX.

100 Excel Simulations

100 Excel Simulations »

Covering a variety of Excel simulations, from gambling to genetics, this introduction is for people interested in modeling future events, without the cost of an expensive textbook.

see more products »