Excel Users Love Drop-Down Lists
aka Validation Controls
Excel Users Love Using Drop-Down List because they make data entry quicker and more accurate.
When it comes to working with Microsoft Excel, users like it when Excel is easy to use. The easier you can make it, the more they like working in it. Excel Users Love Using Drop-Down Lists, here we tell you all about them.
Hence, much of our company’s Excel programming services business is based around ease of use, combined with power, and an advanced graphical user interface, taking the user experience to a whole another level. A Smarter Microsoft Solution if you will.
Validation Controls are an easy-to-use Drop-down list in Excel, users love them.
The Unique, Sort and Filters Functions are one of the new Dynamic Array Functions, these Spill Functions simplify Excel programming and are available in Office 365.
When programmed right, Excel should be easy to use. If it is not, you are programming it wrong, but wait, reporting in Excel just got easier!
Validation Controls are one of the most used, easy-to-use features of Excel. They are quite powerful and enhance data entry. With the release of the Unique Array Function, Excel’s dropdown lists are now easier to program than ever before. Gone are the days of using the Offset and Counta functions to create a Dynamic Named Range.
Possibly Excel’s Most Used Feature – Users Love Them!
Users like to use AutoFilter, Sort, Macro buttons and Validation Controls when they work in Excel. These are user-friendly features that allow the user to get the most out of Excel. Of these we see more users using Dropdown lists than the other two popular features.
Validation Controls were initially created as a way to restrict the user to a predefined list, to Control the data they entered into a cell, to Validate that data. They just happen to be very cool and very useful to the user.
How they are often used is to select say a doctor from a list of doctors. So instead of trying to type the name of the doctor, if you can remember it, it is easier to select it from a sorted list. This is “why” people use it.
But it does prevent the user from typing a misspelled name into a cell, as originally intended. So, it does enhance data integrity. It does give the user confidence that they are entering the right data, and honestly, it is a whole lot faster than typing.
- This is especially important if the data being entered feeds a Pivot Table, where you want to make sure your KPIs are based on valid data. You do not want to see three choices for a State for example, CA, Calif or California. Restrict the user, maintain data integrity.
Example of why to use a Validation Control to maintain data Integrity:
- AIRMAR TECH. CORP
- AIRMAR TECHNOLOGY
- AIRMAR TECHNOLOGY CORP
It is easy to see how this could lead to confusion and incorrect results in reporting and analysis. These are the same company, but in a Pivot Table, this would be three different companies. If you use the Validation Control, you can avoid such issues, which are increasingly common.
In the old days Excel programmers would use ListBoxes and ComboBoxes, either ActiveX or Forms Controls. Since then Excel’s simple to use, no VBA needed, Validation Controls have taken over. With the addition of the Unique Function, they become even easier to program.
Excel Users Love Using Drop-Down Lists, the Unique Function makes them easy to program.
How to Populate an Easy-to-Use Validation Control
In the past, when you wanted to populate a Validation Control with a Dynamic Named List, you would create a list, and then you would apply a dynamic named range to the list using the Offset/Counta functions. It is a complex formula and not for the inexperienced programmer.
Using a dynamic list would allow the list to grow and shrink, with the data which is very important if the number of items in the list will change. While not terribly hard to do, they did have their issues, and they do require an experienced Excel developer to write the function. Well, those days are over.
Basing Validation Controls on Dynamic Lists just made programming these controls a whole lot Easier.
Microsoft’s Latest Changes
Microsoft recently released a series of new Dynamic Array Functions, often referred to as Spill Array Functions, as they spill their data into the adjacent cells. One of the most useful and powerful new functions is the Unique Dynamic Array Function.
This function is a quick and easy way to get a unique list of records/items from an Excel Table, and then to use that list in the same file. An example would be Regions.
Using the Unique Function against the Region column of the Excel Table allows you to populate an Excel Validation Control quickly and easily, listing all Regions.
• Note the Unique Function can return multiple columns, as well as just one. If you are not yet using this function, it is time to start.
Historically this is how you would populate an Excel Validation Control with a Dynamic Named Range. Set it to list, put the = sign, and then enter the name of the Dynamic Named Range. This was the DeFacto standard before the Unique Array Function was added.
Excel Users appreciate using Drop-Down Lists, leverage them whenever possible.
Editor’s Note: Using the Unique Function is a new powerful way to quickly eliminate duplicates from a dataset.
Excel Users May Love Validation Controls, but they must be programmed right to work.
Traditional use of Excel’s Validation Control’s Drop-Down list had several options for populating the list:
As with most things Excel, there are many ways to skin the cat. Here we go over the most common ways of programming Validation Controls.
- Enter the list directly in the Validation Control.
- Enter a Named Range or Range in the Validation Control.
- Stagnant List.
- Use the Offset/Counta Function Method to create a Dynamic Named Range to feed the list (See Image below).
- Still viable, but a newer easier way is now available.
Before the Unique Array Function was released, if you want to give your list a Dynamic Named Range, you would typically do so using the Offset and Counta Functions. Not anymore. It just got a whole lot easier.
=OFFSET(Lists!$D$11,0,0,COUNTA(Lists!$D:$D)-1,1)
The world of Excel is changing and now populating Validation Controls just got easier:
If you want to populate an Excel Validation Control with a Dynamic List, we suggest combining one to four of the new Excel Spill Array Functions for maximum effect. The base Function is the Unique Function. This is the function that builds the base list. If needed use the other three functions below, to manipulate the result set.
Integrate the Unique, Sort, Drop, & Filter Functions into one powerful Excel formula.
Microsoft is making so many amazing changes to Excel, these new Spill Array Functions are just the beginning. Here we show why the Excel users love them.
In this example, we will create a Validation Control to list the Regions that can be selected. We will combine two or four Dynamic Array Functions into one formula in order to do so. Super easy, super quick.
- Unique Function – To create a unique list of items to feed the Validation Control, based on the data in the dataset.
- Sort Function (Optional) – To sort the order of items in the drop-down list.
- Drop Function (Optional) – To remove the header row from the result set.
- Filter Function (Optional) – If you have undesired entries such as blanks or zeros.
- Transpose Function (Optional) – To Pivot horizontal data to vertical, or horizontal to vertical.
- If your table has headers, use the Drop Function to remove row 1 from the list. If it does not, then omit this step.
- If your list has blank records, zeros, or other undesired characters, use the Filter function. If it does not, then omit this step.
How Spill Functions Work
When you use these new Array Spill Functions, you write the function in one cell, and the results spill into the necessary rows and columns, based on the size of the result set.
In this example, our result set will be one column of data. Only the first cell in the result set has an editable array formula. That is the cell of interest, as that cell will feed the Validation Control. For this we used two of the Spill Functions, Unique and Sort.
Combining the Sort and Unique Functions gives us a sorted, clean, list of Regions that the user can select via the dropdown Validation Control.
=SORT(UNIQUE(tblSampleData[Region]),1,1)
Important Note on how to Feed a Unique Array to a Validation Control – #
Name the first cell of the Dynamic Spill Array mentioned above, and add the # symbol to make it a Dynamic List. You can reference this Dynamic list anywhere in the file by simply using “RangeName#”. You must enter the #, else you will return one-row worth of data.
Note: You are only naming the first cell of the range. You are placing the # character after the name of the range. It is now dynamic.
As the data grows or shrinks, the range feeding the Validation Control adjusts automatically. This is the simplest way to feed a Validation Control with a Dynamic Range / List.
No need to use the Offset/COUNTA method.
Microsoft is actively changing Microsoft Excel
Microsoft has been making many needed changes and additions to Microsoft Excel over the past few years. It is now easier than ever to remove duplicates, to manipulate data, and to create amazingly powerful, interactive, dynamic, and super easy to use Dashboards.
The new Spill Array Functions will change the way Excel is programmed. Fewer formulas, less VBA, much more dynamic. These changes make Excel easier to use, and if you hire the right Excel developer, your Excel files will be easy to use.
Name the first cell where the Unique Function is in Excel. Just name the first cell, where the Spill Function is. Then when you reference this cell, put a # behind the name, and the full dynamic array results will feed the Validation Control.
=Regions#
In the Validation Control, first select “List”, then put the name of the Named Cell (Range) and put the # sign character after it. Now as the list grows and shrinks, it does the same in the Validation Control.
Microsoft Excel Users Love Using Drop-Down Lists and so do we. We add them to 90% of the Excel files we build for our clients.
Conclusion – Why Excel Users Love Using Drop-Down Lists
Excel should be easy to use. We find ourselves saying that a lot. But it is true, and if you are a professional Excel programmer, or if you need to hire one, find one that leverages the best-practices of Excel programming to make sure your user experience is a positive one.
Drop-Down Lists, aka Validation Controls, are just one way to make Excel easy to use. Users literally love using them. And these new Spill Array Functions, like Unique, make programming Excel Validation Controls easier than ever.
If you want to be an intermediate Excel programmer you must know how to use these new Dynamic Array Spill Functions, period. They are literally changing the way Microsoft Excel programming is done. Just as the XLookup changed how you lookup values in tables, these new Spill Arrays will change how you do programming in Microsoft Excel. The time to learn them is now. More is on the way.
Other Posts on how to use the new Excel Spill Array Functions for Business.
Microsoft Excel Spill Array Functions for Business – what to do with them.
- Related Post: Using ChooseCols Function in Excel Reports – Excel Reporting Just Got Easier.
2. Related Post: Using VStack Function to Consolidate Excel Data
3. Related Post: Using Unique Function in Validation Controls – Users Love Drop Down Lists
4. Related Post: Using Transpose Function in Excel – Transposing Excel Then and Now
So you are using two or more of the newer array functions, combined, to get a unique list, for the dropdown list, and to sort it? So you could also use Filter? How would you use Filter with this?
Does this work for ComboBoxes as well as for Validation Controls?
I am new to these, and I am trying to get my staff up to date as we just purchased office 365.
What about Transpose, is that useful here? Our data is horizontal. Just read that one last night.
Jennifer.
Yes, depending on the results from the Unique Function, you may need to add the Drop Function to remove a header, or the Filter Function to remove “0”, or the Sort Function, or SortBy Function, to sort data for say Regions.
Name these lists and use them in UserForms (ComboBox, ListBox) and Validation Controls. Also, use them in your Formulas and VBA.
Transpose Function is powerful and super easy to use, just not used so much, because how often do you find yourself Transposing data? (See our Blog Post on “Excel Transpose Methods Then and Now”).
Of all of the new Dynamic Array Spill Functions, we like the XLookup the most. See our pending Blog Post on the XLookup and “Xlookup Built-In Offset Functions Use with Messy PDF Data”.
Lots of good stuff to come. Check ’em out.
Want to see something, send us a request.