e

Like what you see? Let's talk about how we can help your business. Contact Us -->

Excel Drop Downs, From Simple to Swanky [Video]

Excel Drop Downs, From Simple to Swanky [Video]

Entering data into Excel can be tedious and confusing to users. Fortunately, you can simplify the process. You can use drop down lists to limit data entry options to just one list of items. (Not to mention they just make you look that much cooler.)

In just a few steps, you can create three different types of drop down lists in less than eight minutes:

  • A simple drop down menu
  • A combo box (Stay with me, it’s not as scary as it sounds.)
  • A more advanced drop down list

Drop Down List with Data Validation

This technique is really best used when you need to enter in the data from a preset list of entries. For example, it’s possible to write a month in several ways: January, Jan, 1/2013, etc.

Before making your drop down, you need a reference list. I like to use a second sheet, then just hide that second tab later. I’ve seen other people put the reference list on the same tab, but I find that if you insert any columns or rows near that list, it can mess up the referencing.

After making a separate list of months (A4, A15), name the range. To do this, simply select your list and type a name in the dialogue box next to the formula bar.

name the range

After naming the range of cells, go back to the first sheet and select the list of values you want in the drop down list. Under the Data tab, find Data Validation. Select the option List, and then enter the source. This would be the range named months.

data validation steps

Annnd there you have it! All the months in a drop down list.

Form Control Combo Box Drop Down List

Skip to this in the video? (2:35)

Form Control drop down lists are more customizable than simple data validation. These boxes give you the option of overlaying a drop down box anywhere in the document. This form control will output the option selected in the box to any particular cell. You can also change the size of the box and the number of options viewable when selecting from the dropdown. (A quality not found in data validation.)

Before being able to work with Form Controls, you must activate the Developer tab. (We fancy, huh?) To do this, right-click on the Excel toolbar—also called the Ribbon—and select Customize the Ribbon. Locate the Developer option on the right hand list of Main Tabs. Select OK, and you’re good to go.

how to view developer tab

Next, you can move on to adding a Form Control Box. You do this by going to the Developer tab and selecting Insert > Form Controls > Combo Box. Make sure you don’t select the ActiveX Control Combo Box. We’ll get to that craziness later.

Add Form Controls

When you select the Combo Box, you will be given the option to drag out a box on your spreadsheet. I recommend making your Combo Box slightly larger than the cell you want to place it over. After placing the Combo Box, right-click on the box and select Format Control.

Pro Tip: Turning the Gridlines on before placing your box will help keep it looking proportionate to the other cells. Just remember to turn them back off!

Now we get to the really fun part. First, navigate to the Control tab in the Format Object box. Then, select your input range. Mosey on over to the cell link and select the cell that you placed your Combo Box over. This will allow you to output the source behind your Combo Box. So let’s say I were to select Google as the option in my box. Then the Combo Box would output the word, Google, in whatever cell I chose. Pretty cool.

Form Combo Box Insert

Lastly, select the option Drop down lines, and change it to ten instead of the default, eight. This will allow you to see all the data validation options in one drop down without having to scroll. Alright! Now you are officially ready to move on the biggest of dogs, ActiveX controls.

ActiveX Control Combo Box

Skip to this in the video? (4:25)

ActiveX combo boxes are the most advanced drop down option in Excel. You not only have all the other advantages of data validation and Form Controls (custom size and change the number of options viewable). But you also have the option to change the color of the combo box and format the text. This can really add some pizazz to your spreadsheet.

You’re going to start this combo box similarly to how you started the simple data validation. This time you are going to name the Source range on Sheet 2. To do this, select the range (A19,A28) and then go into the dialogue box next to the formula bar and type in a name. (Keep in mind you can’t use any spaces or special characters when naming a range.)

Under the Developer tab, select Insert > ActiveX Controls > Combo Box. Selecting Design Mode allows you to edit ActiveX controls. Without it selected, you cannot right click on or edit your combo box.

Adding ActiveX Controls

With Design Mode selected, right click on the combo box you just drew and select Properties. A very intimidating dialog box will appear. But stay with me on this one—it’s easier than it looks. First, locate the option ListFillRange and type in the name of the range. Then go to LinkedCell and type in the cell you want the output to go to.

ActiveX Properties

You also have the ability to change the number of options viewed. Under ListRows, input the number of options you would like to see. I love ActiveX Controls because of all the formatting options. You can change the text color, typeface, and size for the drop down options. You can’t use these options with form controls or data validation.

When in Design Mode, right click on the ActiveX Combo box and select Properties. Then select BackColor to change the background color of the combo box. Then select ForeColor to change the color of the text.

Change Background And Text Color

Next, you can change the typeface and the size of your combo box’s text. Select the FontColor box and click on the ellipsis to open the text dialogue box. Once you’re finished formatting, select OK.

Format ActiveX Text

Now turn off Design Mode, and there you have it! You have now mastered three different types of Data Validation. I hope you find this helpful. Feel free to experiment with these options and figure out which one works best for you.

Bonus: Download this template and feel free to duplicate. Have questions? Leave them in the comments below!

Want to Get Inside?

Become a BlueGlass Insider Today!

  • Be the first to know about BlueGlass events, meetups, and surprise releases. Before they’re made public…
  • Exclusive access to the latest tools, tips and must-read posts.From people who have been doing this for years…
  • Insider perspective on the latest trends in digital marketing. Info that you won’t get anywhere else…

Enter your email below to join for free!




Join the Discussion

Comments

  1. Elise says:

    Great job on the video tutorial. Really straightforward and helpful. (Also appreciated the MemeGenerator image. Classic.)

  2. Spot on with this write-up, I truly believe this web page needs a lot more consideration. I’ll in all probability be once again to read a lot more, thanks for that info.

    [url=http://buysredbottomshoesa.web1337.net]red bottom heels louboutin[/url]

    http://christianlouboutincoolsa.0fees.net

  3. Timothy Gallagher says:

    Tutorial was helpful. Well done and gave me the results I needed.

  4. venkat says:

    very good job done by you. This Tutorial was very helpful. I am expecting best results for you

    Thanks