ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Insert a Drop Down Box in Excel

Updated on March 28, 2014

Inserting a drop down box in Excel is a straightforward and simple process that is a great way to improve the efficiency of your workbook. In my job as an accountant, I use them on a daily basis and have found them to be a very powerful and indispensable tool. Drop down boxes are great for limiting what data can be put into a particular cell, controlling what data is displayed, and controlling how data is summarized. Below is an example of a drop down box in a Baseball Scorecard that I created in Excel.

Source

Creating a Drop Down Box in Excel

As an example, I will demonstrate how to create a drop down box that allows the user to choose the current month. I use this particular drop down box on a regular basis and it is a huge time saver. You can make any list into a drop down box using these instructions.

Have you ever created a drop down box in Excel before?

See results

Define the Contents of the Drop Down List

Open a new workbook in Excel to get started. The first thing that I normally do when working with drop down boxes is to rename one of the tabs and call it the "Control" tab. It is a much cleaner way to keep this type of data separate and in one place. If you ever have to change anything you can easily click on this tab and make your adjustments. Before we can define the drop down list, the following needs to be entered into the "Control" tab in the corresponding cells:

  • Cell B4 - enter "Month".
  • Cell B5 - enter "January".
  • Cell B6 - enter "February".
  • Cell B7 - enter "March".
  • Cell B8 - enter "April".
  • Cell B9 - enter "May".
  • Cell B10 - enter "June".
  • Cell B11 - enter "July".
  • Cell B12 - enter "August".
  • Cell B13 - enter "September".
  • Cell B14 - enter "October".
  • Cell B15 - enter "November".
  • Cell B16 - enter "December".

Next, highlight cells B5 through B16. In the "Name Box", which is to the left of the formula bar, enter "Months" as the name of the range. Below is a picture of what it should look like.

Source

Tip: Once you set the name, the list will not pick up any value outside of the range. It will pickup anything that is changed inside of the list. If you wanted to add "Yearend" for instance, I would right-click on "December" and select "Insert". When the insert box pops up, select "Shift Cells Down" and click "OK". Then I enter "December" into B16 and enter "Yearend" in cell B17.

Tip: If you want to sort or order the list in a certain way, this is the place to do that.

Tip: The column width in the drop down box is driven by column B in the example.

Setting up the Drop Down Box

Click on a second tab and rename it as the "Detail" tab. In cell A3, enter "Current Month:". On the "Data" menu in Excel, click on the "Data Validation" arrow and select "Data Validation..."

Source

A "Data Validation" window will pop up. Under the "Allow:" drop down box select "List". In the "Source:" box enter "=Months". It is critical that the name that you type here is the exact same as what you typed in the "Name Box" on the other tab. If they are different, your drop down box will be empty. Also, be sure to leave the "Ignore blank" and "In-cell dropdown" boxes checked. The "Ignore blank" box is a toggle switch that ignores blanks if they appear in your list. Why would you want to leave blanks in there you may be thinking? It is one way to allow for future additions to the list.

Source

Next click on the "Input Message" tab and it is up to you as to whether or not that you put anything. In my experience, I always leave it blank. If you do enter an input message, every time that you click into the cell a box will pop up showing your input message. I find this feature very annoying to say the least, but some people may find it useful.

Source

Finally, click on the "Error Alert" tab and select the warning style that you would like under the "Style" drop down menu. It does not matter which one you pick. Enter in an error message to display if someone tries to enter something different than what is contained in the list that we defined on the previous tab. I always do this step, especially if I am not the end user. Once you are done, click "OK".

Source

Now you should see an arrow on the right-hand side of cell B3. Click on it and you will a drop down box that lists each month that we included on the list on the previous tab. Select whatever month that you want and notice that it will show up as you entered it. Excel will also allow you to enter a month name as long as it matches what is in the drop down list. Below is a picture of it.

Source

Select a month and it will populate the cell with that month. One of the nice features of this is that you can copy cell B3 and paste it anywhere else you want and it will still contain the drop down box.

Ways to Use Drop Down Boxes in Excel

Drop down boxes can be used for an endless amount of applications. I normally use what we did above to control what data is shown and to control how it is summarized. For example, I am responsible for consolidating our global financial results each month at work. I use a drop down box to select the current month, which then controls what shows up in the current month and year to date columns. Here is an example of what that looks like:

Source

Another typical application that I have seen is when drop down boxes are used as part of a register to allow the user to select an appropriate value. A great example of this would be in a checkbook register with an expense category drop down box. Below is a picture of what that looks like:

Source

Another way that I have seen drop down boxes used in Excel is in NCAA basketball brackets. The drop downs allow the user to select the team that they think is going to win each game using formulas behind the scene to drive what each drop down box has in it. This would be more difficult to setup because you have to move only the winning teams forward in it round.

Creating drop down lists in Google Docs

Comparing Combo Boxes and Drop Down Boxes

Combo boxes and drop down boxes are similar, but yet, very different. Both are able to control how data is summarized and are able to control what data is shown by using formulas. However, to drive a formula off of a combo box, you have to find where the box is linked to a specified cell. Drop down boxes allow you to run formulas directly against the cells that they appear in. Also, only drop down boxes built off a single list can have different values. Combo boxes, in order to have different values, must be each set up individually.

Inserting drop down boxes in Excel is a great way to save time and dramatically improve the efficiency of your spreadsheets. They have made my life so much easier when I can control the what data appears on a report. Feel free to contact me with any questions or comments.

Great resource for learning Excel

© 2014 Eric Cramer

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)