How to create a Drop Down List in Excel
Автор: Anders Jensen
Загружено: 2020-12-14
Просмотров: 525
This video tutorial shows, how to create drop down lists in Microsoft Excel (also reffered to as Data Validation lists). It is also explained, how to do dependent/dynamic drop down lists in Excel.
You could also watch:
🔵 Power Automate Beginners Tutorial (HOW TO AUTOMATE MICROSOFT OFFICE IN AN EASY WAY) - • Microsoft Power Automate Tutorial for Begi...
0:00 Intro and presentation
We want to create a drop down list in an Excel cell. The data for the list is coming from a data sheet. The drop down list is also called a data validation list, as we can force the user (or ourselves) to input only valid data.
0:41 Data for the drop down
We can entire new sheet with the data for our drop list. You can chose to hide that sheet, if you don't want the user to see it and/or give it a more clean look.
1:02 Create a drop down list in a cell
Click Data, then Data Validation and then in the Allow, you chose a List. Remember to specify Source (our newly created list in the Data sheet). Drag down the cell for having the list in more cells.
1:58 Ignore Blank
If you have blank cells in your data sheet, Excel will show it. Tick this setting and you will have these blanks removed in the drop-down.
2:15 In-cell dropdown
You can use the list as a data validation process only. Meaning that if we untick the "In-cell dropdown", you will not see the dropdown list, but we or our users will still have their data validated (only data, from the list in the data sheeet, will get accepted).
2:45 Input Message
Whenever the user moves to the cell with the data validation/dropdown cell, he will see this message.
3:10 Error Alert
We specify an error message for the user and chose the style (Stop, warning and information). The stop will not allow the user to move on, whenever invalid data is typed in. Warning will warn and let the user move on, while information is just a simple information box.
4:36 Alphabetical order and auto add new list items
We format our input data as a table to make it easy to have the drop down list in alphabetical order. Now re-specify the source as =INDIRECT("YourTableName"). Remember to apply it to all cells with the same settings. This also give us the nice feature, that whenever we add new data, it will automatically gets added to our dropdown list.
6:28 Dependent Drop Down Lists
Wouldn't it be nice, if we could create a drop down list, that were dependent of the content of a another list? We can do this very easily in Excel. Create new table headers for each category and specify the content of these categories in the rows below. Remember to make it easy for yourself and name the tables. We also create a named range for the headers, so we can refer to them in our source drop down.
8:33 Create the dependee drop-down list
The first list is the dependee, meaning that whatever we specify here, will change the content of the drop-down of the other cell. Here we refer to the category headers.
9:02 Create the dependent drop-down list
We refer to the dependee column. Remember to use the INDIRECT function.
Connect with me:
🔔 Subscribe - http://www.youtube.com/user/klogeande...
💼 LinkedIn - / andersjensens
👥 Facebook - / andersjensenorg
💌 Email Newsletter - https://andersjensen.org/email-newsle...
#microsoft #excel #office
Доступные форматы для скачивания:
Скачать видео mp4
-
Информация по загрузке:
