4 Ways to Sort Drop Down Lists Automatically in Excel
Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
Learn 4 ways to sort drop-down lists automatically in Excel when changes are made to the source data.
Download the sample file: www.excelcampus.com/tips/sort...
This video is based on a question from Suliman, Wim, and Colin from my previous video on automatically updating drop-down lists (data validation lists).
I explain the following 4 ways to automatically sort data:
1. VBA Macro
2. Free List Search Add-in
3. Power Query
4. Dynamic Array Formulas and Spill Ranges
There are many ways to go about sorting data automatically. I chose these four techniques because at least one solution should be available to everyone, regardless of which Excel version you are using.
Here are additional resources and videos on drop-down lists:
How to Add New Items to Excel Drop-down Lists Automatically: • Add New Items To Excel...
How to Create Drop Down Lists in Cells - The Complete Excel Guide: • How to Create Drop Dow...
Searchable Drop Down Lists in Excel - The List Search Add-in: • How To Search Drop Dow...
Excel Drop-down List Arrow Always Visible: • Keep Your Excel Drop-d...
New Excel Features: Dynamic Array Functions & Formulas that Spill: • Dynamic Array Function...
00:00 Introduction
01:01 VBA Macro
04:07 Will work for Mac & Windows desktop version of Excel
04:18 Free List Search Add-In
06:36 Available on Windows versions of Excel (2007 or Later)
07:01 Power Query
11:00 Available on Windows versions of Excel 2010 or later
11:14 Synamic Array Fucntions
Пікірлер: 31
0:59 1 VBA macro 4:17 2 Jon's search add-in 6:56 3 power query 11:13 4 dynamic array function ("best for last")
Great...i am your new subscriber and a beginner in excel 😣 Can't wait to watch your all videos as it looks so helpful 🖒
Great video, Jon. Thanks for this. Is there a way to avoid having the sorted list in a separate range in the workbook, e. g. by using the SORT() function directly in the data validation dialog box?
This time it is bit faster... no problem I can watch repeatedly to get along! Thank you again!
@ExcelCampus
5 жыл бұрын
Thanks for letting me know Fazal.
Thank you Jon..the video is so helpful great work 🙏
@ExcelCampus
5 жыл бұрын
Thank you Suliman! I appreciate you asking the question and being the inspiration for this video. I hope it helps others as well. :-)
Thanks Jon - very helpful video, much appreciated
@ExcelCampus
5 жыл бұрын
Thanks again for asking the question Colin! :-)
Dynamic array looks good to me. thanks
Great video , Thanks
thanks great video never seen before can you please provide if statement video?
Great video John!
@ExcelCampus
5 жыл бұрын
Thanks Andrey! :-)
With the first example of sorting new data "VBA MACRO" is there anyway to automatically sort data that way but within multiple columns. I receive about 1000 line items and would hope to condense that into about 5 columns or so.. I would really appreciate your help! Thank you
Hi, its great, thanks.
What about if you want to sort in excel by the last digit? Is it possible? Ex. WGA344, WBA677, WBA199 and you want sort or filter it using the last digit as your reference? Thank you
Great video as usual John thank you! Question: if items are removed from a source list (because they are no longer needed but still need to exist in "older" previous drop downs further up, but don't need to be in the drop down anymore), does removing them from the source list remove them from the old ones?
@ExcelCampus
5 жыл бұрын
Hi Peter, Great question! When you remove an item from the source list then it will be removed from ALL drop-downs that use that range as their source. However, this will NOT delete existing values in the cells. Let's say you have the word "coffee" in your list and you've already filled some cells with the word coffee from the drop-downs. If you then delete coffee from the source range, those cells that contain the word coffee will NOT change. When you click the drop-down for those cells, you will NOT see the word coffee in the drop-down. I explain more about this behavior in my video on How to Create Drop Down Lists (kzread.info/dash/bejne/fXui2KWEmbKyfZM.html). I hope that helps. Thanks again for the nice feedback! :-)
@peterbarron6488
5 жыл бұрын
Excel Campus - Jon Fantastic thank you!
Hi John, really knowledgeable thanks; do u have any past or pre video related to below analysis: I have n no's of Emp details in a city level and their sales in n no's of months. I want to analyze their individual sales different in graff as well as in summery too - please confirm
@ExcelCampus
5 жыл бұрын
Hi Kavita, you can definitely use pivot tables and pivot charts for this type of analysis. Checkout my 3 part video series on pivot tables and dashboards. Here is the link to the first video. kzread.info/dash/bejne/a4KJzKp7frrLabA.html I hope that helps. Thanks again and have a nice day! :-)
I am using Excel 365. I have created a dropdown using a "ComboBox". I have used a VLOOKUP to link the ComboBox to specific product number for each item. When I try to sort the specific number into chronological order it will not move the description of the item within the combobox with the product number. Can you sort an excel 365 spreadsheet with a "ComboBox Dropdown" and a product number located in a cell next to it in chronological order?
How we add formulas list in data validation list please guide about this.
Recent Selected Items. A twist on sorting. Anyone know how to? Keep the last 5 or so recently selected items on the top of the list. i.e. after a number of favs are kept on top, it would just refresh the list as things are selected. Or maybe just open the list with the last one selected.?
Where's the download link for the vba code
List Search is not available in Add-in
I don't even know why Excel bothers pretending to offer a Mac version. 80% of features and functions beyond =SUM() are not available on a Mac. 😑 Sorting a dropdown range is ridiculously basic, yet somehow impossible on Mac Excel? Could've done this in 10 secs with G Sheets. (sorry for the vent...)
someone please point me to a 1 minute video that explains this ratgher than these long drawn out 15 min videos that make me hate youtubers