Create A Drop Down List in Excel 13
Posted by Smart Office - philippospan
We can make a worksheet more efficient by creating drop down lists. What is the use for drop down lists? If someone uses the worksheet that we created, they can click on an arrow and then click on one of the entries in the list in order for the cell to be filled. Just follow the steps below in order to learn how to create a Drop Down List in Excel 13.
First, we must type in the information that we want to be included in the Drop Down List. These entries should be in a single column or row with no blank cells between them. As we can see in the image below, I have typed in a few countries to be included in the Drop Down List.
This step is optional:
Once finished, we must select the area of cells that contains the information, and then do a right click with our mouse in order for the shortcut menu to appear as shown below. From the shortcut menu that appears we select the command Define Name.
Once the Define Name command is selected the New Name dialog box appears, where at the right of the command Name we type in the name that we want for the selected cells. In my example I have typed the name Countries for the selected cells. Once finished we press the OK button in order to return to our worksheet. From now on, the area of the selected cells will be referred with the name Countries.
The next step is to select the tab Data and from the area of the ribbon named Data Tools to select the command Data Validation. Once selected the drop down menu appears with the available commands where we select the command Data Validation as shown below.
Then the Data Validation dialog box appears as shown below. On the first tab of the dialog box named Settings, we select under the Validation Criteria what we want to Allow. In our case, from the drop down menu under the command Allow we select List.
In the Source box, we can either select the range of cells that we have typed in the information or we can type an equal sign (=) and then the name that we gave to our list. In this example =Countries
Then we move to the next tab, which is named Input Message. On this tab, we can create a message to pop up when the cell is clicked. All we have to do is to activate the check box next to the command Show Input Message When Cell Is Selected. Then in the area under the command Title, we type in a title for our message and then in the area under the command Input Message, we type our message that we want to appear. If we do not want a message to appear we deactivate the check box next to the command Show Input Message When Cell Is Selected.
Finally, we move on to the next tab named Error Alert. On this tab we can activate the check box next to the command Show Error Alert After Invalid Data Is Entered and then pick an option from the Style box drop down list. Then when someone enters invalid data in the drop down list message will show up every time. Once we have done all our settings we just press the OK button to return to Excel.
In the image below, we can see the drop down list that I just created. All we have to do is to select a country from the drop down list in order for it to be inserted in the cell.
Below I have inserted wrong valid in the cell that contains the drop down list at straight away a Microsoft Excel Dialog Box appears informing us the following:
The Value You Entered Is Not Valid
A User Has Restricted Values That Can Be Entered Into This Cell
Below you can check out the video describing how to create a Drop Down List in Excel 13.
About Smart Office - philippospanMVP: Honored with the MVP (Most Valuable Professional) for OFFICE SYSTEM title for the years 2011, 2012, 2013, 2014 and 2015 by Microsoft, for my contribution and commitment to the technical communities worldwide. Microsoft Master Specialist: This certification provides skill-verification tools that not only help assess a person's skills in using Microsoft Office programs but also the ability to quickly complete on-the-job tasks across multiple programs in the Microsoft Office system
Posted on December 12, 2014, in Excel English 2013, Microsoft Office 2013 English and tagged Data Tools, Data Validation, Define Name, Drop Down List, Error Alert, Input Message, Microsoft Excel 13, Microsoft Office 13, New Name, Office Smart, Office System, Smart Office, Validation Criteria. Bookmark the permalink. Comments Off on Create A Drop Down List in Excel 13.
Comments are closed.