Not sure which of these videos to watch? Try out our 'Which Excel Formula to Use' App to get guidance. NOTE: All these free Excel video tutorials are streamed via YouTube. If your network does not allow YouTube videos, you can access the video via your smartphone or from a different network.
How to perform Automatic Sorting in Excel using the VLOOKUP and RANK functions with no VBA.
For other options of how to sort data automatically view the Auto Sort Excel lists blog post
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.
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
Pivot table not pulling all data Pivot tables are great tools but due to the lack of understanding of how they work we are often told that the pivot table is not pulling all the data. There could be a … Continue reading
2014/ 2015 Bundesliga predictions We have created a 2015 Budesliga predictions spreadsheet to use in an office pool or sweepstakes. So now you can document everyone’s opinions at the beginning of the season and then refer back to them at the end of … Continue reading
Adrian was really good. He explained everything really professionally- Bookkeeper.– Excel Testimonials
It dealt with my daily challenges at work- IS Management Accountant.
There was nothing I did not like about the workshop. Adrian was fantastic- Accountant.