In her regular series for Cambridge Network members - now in its 15th year - software training expert Karen Roem offers handy tips to help you 'Tame your computer'. This week she describes how to create a drop-down list sorted in alphabetical order that automatically updates when data is added to your table (Microsoft Excel 365).
Tame your computer - super sorting
Excel provides more than 450 Functions - a special type of formula that produces a specific result. There are a lot of complicated Formulas, but I’m a great believer that a Formula doesn't have to be complex. Just break it down!
Preparing for a new webinar for the Cambridge Science Park - Ten Excel Functions everyone should know (and those you shouldn't) – I stumbled upon two new Functions… UNIQUE and SORT. The UNIQUE function returns a list of unique values in a list or range and if you combine it with the SORT function you can create an alphabetical drop-down list (data validation) from data in an existing table.
I realise it’s a rather specific tip, but I think this could be useful.
I’ve broken it down to make it easier to explain and understand. You can also combine step 2 and 7, but as promised I wanted to make it easy to explain and understand. (The Dutch don’t just show they are clever clogs … they make them! ;)
Next, you can create a drop-down list that automatically updates whenever you add a new person with a new job, like I did in row 16. See tip 608.
With special thanks to Paul Barnhurst for writing on LinkedIn about UNIQUE and Mark Robson for SORT.
PS If you are interested in the Functions webinar I refer to in the first paragraph, let me know. I keep meaning to schedule it and knowing I get Bums on Seats might just be the nudge I need. I also do one-to-ones.
* Unless stated otherwise this tip is written for Microsoft 365 desktop apps and Windows 10 users, but might also be useful in Office 2010, 2013 and 2016.
PS To help you put these tips into practice I have developed a series of 60-minute webinars, giving you the opportunity to see the hints, tips and time-saving shortcuts in action. Learn how to be a Clever Clogs for £24 only. And following requests, I’ve started scheduling some of my 60-minute webinars on Saturdays. Hope to see you there!
14 June 2021