Excel Combo Box KO’s Data Validation

Data Validation or Drop Down Lists are one of Excel’s greatest features, but I have one gripe with them, and that is the down arrow is only visible when the cell containing the data validation is selected.

This is a pain when you use them in interactive Dashboard Reports because they need to stand out and shout ‘over here…click me’ so the report recipient knows they can use them.

That’s why an Excel Combo Box KO's Data Validation, with one small downside which I’ll come to later.

How to Insert Excel Combo Boxes

You’ll find Combo Boxes on the Developer tab of the Ribbon in the Controls group under the Insert icon.

If you don’t have the Developer tab visible in your Ribbon you can add it:

Developer Tab in Excel 2007

Click the Windows button > Excel Options > Popular > check the ‘Show Developer tab in the Ribobn’ box.

Developer Tab in Excel 2010 and 2013

Click the File tab of the Ribbon > Options > Customize Ribbon > on the right hand side under Customize the Ribbon > Main Tabs > check the ‘Developer’ box:

Ok, now we’re ready to insert a Combo Box:

Go to the Developer tab of the Ribbon > in the Controls group click Insert > choose the Combo Box icon:

Your mouse will change to a plus symbol ready for you to left click and draw the Combo Box on your worksheet where you want it.

Tip: If you want it to fit perfectly in a cell or range of cells then while your Combo Box is still selected choose Snap to Grid in the Drawing Tools Format tab of the Ribbon.

You can now resize it using the pull handles and it will automatically jump to the edge of the cell or range of cells as you left click and drag it into place with your mouse.

If you accidentally clicked off your Combo Box and the pull handles have disappeared simply hold down CTRL and left click on it to edit it.

Right, pressing on; now we can tell our Combo Box where our list of values are. Right click the Combo Box and select Format Control:

This opens the Format Control dialog box. You want to choose the ‘Control’ tab:

Input range: This is your list. Mine is a list of months in cells H3:H14.

Tip: you can also make this a dynamic named range.

Cell link: This is the reference number Excel gives each value in your list. The first value is number 1, the second is number 2 and so on. You can see the value in cell F3 (my cell link) is 1 because I have selected Jan-13 in my Combo Box.

Drop down lines: this is the number of lines you want visible when you click on the down arrow. For example; if you don’t want to scroll you could increase this to 12 to show all of the months in the list when the drop down arrow is clicked.

3-D Shading: this makes your Combo Box look a bit fancier. We can all do with a bit of fancy in our lives, so why not 🙂

Using Combo Boxes

So now that you’ve inserted your Combo Box it’s time to put it to use.  There are loads of things you can do with them but here are some ideas for inspiration:

1. Convert the output back to the selected item using the INDEX function like this:



2. Use the output in formulas that dynamically update as the user makes their selection.  For example; let’s say we have some data on Sheet2 and we want to summarise it by month based on the month selected in the Combo Box like this in cell E3.

   

   Here’s our data on Sheet2:

And I've used this SUMIFS formula in cell E3:

=SUMIFS(Sheet2!$C$2:$C$20,Sheet2!$B$2:$B$20,">="&Sheet1!$G$3,Sheet2!$B$2:$B$20,"<="&EOMONTH(Sheet1!$G$3,0))

Note: you don’t need to convert the output in cell G3 first (as I've done in the above formula), you could put the INDEX formula in the SUMIFS formula if you were pushed for space in your workbook!

Cons for Combo Boxes

1. You need to convert the output before using it in other tools or formulas in Excel like I’ve done in cell G3 using the INDEX function.
2. Since the Combo Box isn’t attached to a cell (it’s actually an Object that hovers above your worksheet) it’s not as easy to copy and paste as Data Validation.
3. You can’t easily tab from one Combo Box to the next like you can from one cell to another that contains data validation lists.

Ok, so that was 3 small downsides to them not just one, but they still beat Data Validation lists when it comes to displaying the drop down arrow.

The Bottom Line

Use Combo Boxes sparingly.

If you’ve got a lot of cells that use data validation then you’re probably better off sticking with good ol’ DV, but if you want/need it to be obvious that the user has a choice then give Combo Boxes a try.

An Aside

The images used in this tutorial are from Excel 2013. Yes, I finally bit the bullet and after several failed attempts to install it I’ve officially migrated to 2013. It’s very similar to 2010 with a few changes and improvements.

The main difference I notice is how minimalist the use of colour is in the Ribbon. Most of the icons have been toned down and it has a very white feel to it.

BTW, if you’re already using Office 2013 keep an eye out for our new training that will be released over the coming months.