Excel Lesson 6A of 29: Excel Drop-Down Lists and Data Validation

View more Business & Education Ebooks here.

Downloads

Downloads

Techniques on Excel

Excel VBA On the internet Consulting

Techniques on VBA for Excel

Excel Index and Search Tool (Press the buttons below)

Excel Lesson 6A of 29: Excel Drop-Down Lists and Data Validation

Making a drop-down list in one or a lot cells of an Excel worksheet is very simple and very helpful. With drop-down lists theres no must enter no different values manually time and time again and you're certain that the spelling is right.

Note: The Excel drop-down lists presented below can be created by anybody on regular Excel worksheets. If you are searching for drop-down lists for programmers see the VBA (macros) section

Excel drop-down lists are used extensively when there's a need for users to enter data in an Excel database. It insures that the values are valid and that the spelling is right. With DDLists ypu protect the integrity of your databases.

Excel drop-down lists are also used to develop questionnaires that users complete. By utilizing drop-down lists you make certain that they supply you with valid answers for the reason that the drop-down list limits them to a choice of preset answers.

Finally they're used in dynamic reports (automated with the SUMPRODUCT function) where users can select a few weeks, a store, a branch in the header and the report then shows the proper numbers based on the choice of the user.

First technique:

Open a new workbook and select cell C1 in the 1st worksheet. Go to the menu bar and select "Data/Validation" the following window appears:

In the "Enable" text box select "List" and the window changes to this:

In the "Source:" text box write YES,NO separated by a comma and press on "OK". You immediately have a drop-down list from which you are able to select either "YES" or "NO. This is very helpful when you create a questionnaire in Excel and you desire to user to reply to exclusively by "YES" or "NO".

Second technique:

In the "Source" text box you are able to also submit a range of cells where you maintain your list. ex(=$A$1:$A$23). DON'T FORGET the $ for the reason that when you copy/paste this cell into others it is certain to look at another list.

Leave the first cell of this range which you're submitting empty specially if you've more than 8 values. The great reason for doing so is that when you attempt to use your drop-down list the selection made by Excel is the 1st empty cell in the range where you maintain your list. By leaving the first cell empty it becomes the selected value and all the other values follow below.

Leave cell A1 empty and in cells A2 to A6 enter five names (Peter, John, Mary, Luke, Ann). Select cell B1 go to the menu bar "Data/Validation". In the text box "Enable:" select "List". In the "Source:" box write =$A$1:$A$65 then press on "OK". Attempt your drop-down list. Select cell B2 and do the process again however this time in the "Source" box write =$A$1:$A$11. Attempt your drop-down list. In cell A7 enter Magdalene. Go back to cell B2 and gaze at the choice that you've in the drop-down list. Notice that "Magdalene" is there. When I create drop-down lists I consistantly include several empty cells in the range that I use as reference to add new values.

Third technique:

Select cell B3 and do the process again (Data/Validation). To submit the values that shall be used in your list press the small red arrow ("Source:" box) then press on cell A1 hold and select all the way to A11. Press again on the little red arrow, press "OK" and there you're.

For the second and third techniques if you fail to desire the list that feeds you drop-down to show only hide the column where they're.

Fourth technique:

When you use the third technique if you attempt to go to one more sheet after clicking on the little red arrow Excel does not let you do so. As I love to maintain all my lists on a single sheet, I discovered a way around this. Learn it in: excel-chap6A-ddlists.xls.

Cascading Drop-Down Lists

When you select a country in the 1st drop-down list you desire just the cities from this country to show in the second drop-down list not all the cities of all the countries. We call it cascading drop-down lists and you will be able to see how it's done in excel-chap6A-ddlists.xls.

Extending drop-down list

You are able to create drop-down lists in an individual cell or in a lot cells at an identical time. If you should extend the range showing the drop-down list you can achieve it 2 ways.

1- Set the cost of one cell that has the drop-down list to not a thing and copy/paste it.
2- Select one cell that has the drop-down list and any number of other cells. Go to "Data/Validation" and Excel will let you know that the selection contains cells with no the data validation setting. Press the "Yes" button.

Deleting the drop-down lists

If you'd like to clear the data validation setting in a number of cells select them go to "Data/Validation" and press on "Clear All". You are able to also select a lot cells that contains the setting or not and go to "Data/Validation". You'll observe the dialog box asking you if you'd like to extend the settings. Press "Yes" and then press on "Clear all".

Learn MORE on Excel Click The Link Below!

Excel Index and Search Tool (Press the buttons below)

Comments: Click The Link Below

Excel VBA On the internet Consulting

Techniques on Excel

Downloads

Techniques on VBA for Excel