Next Live Training Date:

JHB:9-11 May/ DBN:16-18May

Not sure which Excel course you should do? Try out our free Which Excel Course Should you do" questionnaire. 20 questions and you will know exactly what course you should do!

Automatic Sorting in Excel

Automatic Sorting in Excel
Watch this video

How to perform Automatic Sorting in Excel using the VLOOKUP and RANK functions with no VBA. See how it works on sports competitions for league standings.

NEW: Go to  VLOOKUP Explanations with downloadable exercises and detailed solutions including this Automatic Sorting example

For other options of how to sort data automatically view the Auto Sort Excel lists blog post

  • 5:35
  • Skill Level:
  • What's my skill level
    Flying screens

    All of our videos are listed by user Excel Skill Level.

    To find out your personal skill level, download our quick assessment.

    Once you've completed the test, send it to us and we'll evaluate for you.

    Download Test

  • Watch this Video

Automatic Sorting in Excel

In Excel, it is possible to rank a piece of information in a list so you can see where it sits. However you may want to do automatic sorting in Excel to sort your list into the appropriate order.

Related Items: Automatic Sorting in Pivot TablesData Sort Video Clip, How to differentiate items with the same rank

I am not a big fan of using Macros if it can be done at all automatically. And in this segment, you will learn how to find and activate the relevant functions being Rank and VLOOKUP and how to combine these functions to automatically sort lists in Excel.

In this example we have a list of Sales People, their Units Sold, Average Sales Price, and therefore what their Total Sales were. What we’d like to do is to first Rank how they’ve done in terms of total sales, but then we’d like Excel as well to give us the final rankings in the correct order, pulling through all the correct information that needs to come through.

To do this you would do the following, the first thing we need to do is to Rank the list of sales people, and we need to put this in front of the list, the reason for this will become clear with the VLOOKUP section, if you are unfamiliar with the Rank feature, there is information available on our website.
So what you do is;

You now want Excel to pull through this information, sort it automatically, and pull through the relevant cells. In order to do this,

What this means now is, you have a listing that automatically sorts itself, so for example at the moment Antoinette is at the top with 100 units. But lets say it was a finger typing error and it should’ve only been 10 units

There are some risks with using this technique. One of the main problems is the way Excel handles situations where the ranking is exactly the same. So for example you’ll see Mary has sold 67 units and Cary has sold 67 units;

Another potential risk with this technique is that VLOOKUP makes use of a lot of memory in a spreadsheet. And so if you perform this technique on a very large list you will find that your spreadsheet will increase in size exponentially

Free Tips & Tricks Newsletter

Please fill in your details below to get regular Excel tips and tricks and a free Quick Reference Download!

Handy Tips & Tricks

Excel can help you read large numbers

Excel has a feature in it that can read text or numbers to you. Although its Afrikaans is not great, it will correctly read a large number with all the billions and millions in the right place. So whether you … Continue reading

Shortcut to insert a symbol in Excel

If you regularly work with Excel and use it to make estimates, reports, quotes etc you may need to make use of symbols that are not available on the keyboard e.g. plus minus ( ± ), copyright ( © ), metres squared ( … Continue reading

I can testify that since I have been trained by you online, I really improved my Excel knowledge. But as you know, I am not living in South Africa but I am in Democratic Republic of Congo.

Please let me know if you have an online version of this session and I will be part of it.

– Congo

Video Library & Tutorials //

View Entire Video Gallery ››

Making the same change to multiple worksheets in Excel

Making the same change to multiple worksheets in Excel

Tables in Excel for PowerPivot use

Tables in Excel for PowerPivot use

Excel see formula instead of value 2013

Excel see formula instead of value 2013

Excel Quick Sum 2013

Excel Quick Sum 2013

Some of our Featured Clients