Excel Weekly Challenge: Dropdown Lists
Oz du Soleil
Microsoft Excel MVP | Excel Instructor on LinkedIn | YouTube: Excel on Fire | Professional Raconteur | Video Editor
This week, users are challenged to create dropdown lists in order to simplify and control inputs. This is all about data integrity, easy analysis and staying out of trouble. In today's example, one acceptable entry is A/V. By implementing dropdown lists we prevent 3 types of errant inputs:
- Legitimate variations (audio-visual or A-V)
- Personally made-up entries (AV Tech)
- Errors (V/V or, putting the person's availability in the specialty column)
In this image--the example without dropdown lists--it would be hard to write an Excel formula to get a tally of, for example:
How many Editing people are available?
There are 3 people who do editing but Hailey M. Meyer's specialty is listed as Film Editing instead of just Editing; and Rosalee Hobbs' status is still checking. ??
We can't do anything with that! We can't even sort or filter this data and get the right things grouped together.
In this situation we want to restrict entries to:
With the entries restricted and consistent, it would be easy to write formulas, sort and filter, as shown below. The dataset is sorted and shows that Hailey and Enora are the 2 Editors who are also available.
That's the challenge: add dropdown lists to help avoid a mess.
But there's more! Dynamic Dropdown Lists
Can you make dynamic dropdown lists so that entries can be added or removed?
If you don't, this video will show you how. Check it out!
- For training and consulting, contact me via my website
- Watch my courses on LinkedIn Learning
- Follow me on LinkedIn
Contracts Officer/Accounts Payable- Major Projects & Engineering- Ok Tedi Mining Limited
4 年Hi, Could you please kindly send me a video for the EXCEL WEEKLY CHALLENGE?
Statistical Analyst, Entrepreneur.
4 年Hi OZ, thanks for these tips - they are very helpful! I have a question on aside, totally different from this.?After modelling different data tables in Power Q (created connection & add to data model). Is there a way to export one or each of these tables from Power Query to Excel tables (without totally removing them from the model/connection)? Also, is this possible for a large dataset > 1,048,576 rows, since traditional excel allows only that range? Reason I need this, I modelled (combined and edited) data from an XML file in folders, now I want to use the combined data in an Audit software that by importing from excel. Is there a way to export the combined datatable? I hope this question makes sense. Thanks a million!
Instructional Designer | Trainer | MCT
4 年Hi Oz du Soleil Thank you for the challenge. I noticed that if you use a named range as a source of your list you can just have Y and N as valid values and still enter the data in lowercase or capital. Y, y, N and N will be accepted values.
Business Consultant and Project Manager with Specialisation in Excel Data Automation with Related Software Design and Controls
4 年I’ve just had to implement a searchable dynamic drop-down for Excel 2013, where a user has over 400 drop down choices in separate sheet but as they type into cell, the drop down list shrinks to just matching values. This is then repeated across 4 sheets on specific areas only. Combination of worksheet events and single function to update the main formula for the drop-down Damn 365 and UNIQUE, SORT and FILTER not being available in older Excel versions!
A&M Travel Hospitality & Leisure Associate
4 年Nice one Oz! This example shows once more how efficient tables are.?Another way to set the dropdown list without creating a table would be to play with the "offset" and "counta" formulas.?Considering that the list starts in A1 with "Specialties" as a title, the data validation formula could be: =OFFSET(A2,,,COUNTA(A2:A100))?or?=OFFSET(A2,,,COUNTA(A:A)-1)