Show
Validation > Drop Downs > Dependent Make Excel Drop Down List in Worksheet CellCreate a drop down list of items in a cell, with Excel's Data Validation feature. This will make data entry easier, and reduce input errors and typing mistakes. Video tutorials and step by step instructions for data validation dropdown lists are below
What is Data Validation?In Microsoft Excel, the data validation feature helps you control what can be entered in your worksheet. For example, you can:
In this tutorial, you'll see how to create a drop down list of choices in a cell, like the list of employee names shown below. Create a Drop Down List in a CellTo create a drop down list in Excel, you can name a list of items, based on a named Excel table. Then, use that list as the source for the Data Validation drop down list. If you don't want to create a named table, you can follow the instructions in the named range section below. Watch the steps in this short video, and the written instructions are below the video How to Create a Drop Down ListWith Data Validation, you can create a dropdown list of options in a cell. There are 3 easy steps: 1. Create a Table of Items OR Create a List 2. Name the List 3. Create the Drop Down Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Clear > Clear All, on the Ribbon's Home tab. 1. Create a Table of ItemsThe easiest way to create and maintain the list of options, is to type them on a worksheet. You can do this on the sheet that will contain the drop down lists, or on a different sheet. In this example, the list will be stored on a sheet named Lists. NOTE: A data validation list can show up to show 32,767 items from a list on the worksheet.
The table is now a Named Excel Table. 2. Name the ListNext, you will create a named range that does not include the heading cell in the table. This named range will be dynamic -- it will adjust automatically if items are added to or deleted from the list. You can use the Name Manager, as shown in the video, or use the Name Box. Both methods are described below. The Name Box method is quicker, but you can't add a comment. Use the Name ManagerThis is the method shown in the video. You can set a name, scope and comments.
Use the Name BoxThis is a quick way to name a range of cells.
3. Create the Drop Down ListNow that you have created a named range, you can use that to create a drop down list in one or more cells
List on Different SheetIf you prefer not to create a named Excel table, you can create a named range, and use that as the source for a drop down list. The drop down lists can be on the same sheet as the source list, or on a different sheet. Watch this video to see the steps, and see the written instructions here: List from Another Workbook Use a Delimited ListInstead of referring to a list of items on the worksheet, you can type the list in the Source box, separated by commas. For example: Yes,No,Maybe Notes:
Allow Entries Not in Drop Down ListWhen you add data validation to a cell, the Error Alert feature is automatically turned on. It stops the users from entering invalid data in the cell. In some worksheets, where there is a drop-down list of valid items, you might want to allow other items to be entered too. For example:
Turn Off Error AlertThe quickest way to allow users to type items that are not in the list, is to change the data validation error alert setting. To change that setting, follow these steps:
Other Data Validation Error Alert OptionsThere are other Error Alert options too, if you don't want to turn that setting off completely. For details on setting up the error alerts, and other data validation messages, go to the Show Data Validation Messages page. For example, you could show a warning message, and ask if they want to continue with the entry. Or, just show an information message, that reminds them the item was not in the drop-down list of valid entries. Get the Sample FileYou can download the sample Drop Down Lists file here: Drop Down List Workbook. The zipped file is in xlsx format, and does not contain any macros. More TutorialsCreate Dependent Drop Down Lists Data Validation Index Page Change Product Name to Code Dependent Drop Down Lists Video Hide Used Items in Drop Down List from Another Workbook Data Validation Criteria Examples Data Validation Date Rules Data Validation Tips Data Validation With Combo Box Apa itu Data Validation Excel?Data validation pada Excel adalah fitur untuk melakukan validasi data yaitu memastikan data yang dimasukkan pada suatu sel atau range telah memenuhi kriteria tertentu. Data validation secara umum berguna untuk mengurangi tingkat kesalahan input data dalam jumlah besar.
Data Validation di Excel dimana?Pilih satu atau beberapa sel untuk divalidasi. Pada tab Data, di grup Alat Data, klik Validasi Data. Di tab Pengaturan, dalam kotak Perbolehkan, pilih Daftar. Di kotak Sumber, ketikkan nilai daftar Anda, yang dipisahkan dengan koma.
Bagaimana cara melakukan validasi data?Cara Menggunakan Data Validation Excel. Pilih(seleksi) Sel/Range yang akan Anda atur data validation-nya.. Pilih Menu Data Validation Pada Tab Data -- Group Data Tools . ... . Setelah muncul kotak opsi Data Validation , setting pengaturan validasi data atau pembatasan isi sel yang diinginkan.. Apa yang dimaksud dengan validasi data dalam Microsoft?Aturan validasi adalah satu cara untuk membatasi input dalam bidang tabel atau kontrol (seperti kotak teks) pada formulir. Teks validasi memungkinkan Anda menyediakan pesan untuk membantu pengguna yang memasukkan data yang tidak valid.
|