To make data entry easier, create a drop down list in an Excel cell, using data validation.
This saves you time, and prevents errors, but has some limitations.
-The font size is small, and can’t be changed.
-You can only see 8 items at a time, and have to scroll to see the other items.
-There’s no autocomplete – pick from the list, or type the full entry.
This video shows you a workaround for those limitations. –Put one hidden combo box on the worksheet
–Double-click a data validation cell, and the combo box appears
In the combo box:
-you can adjust the font size, and the number of rows. -entries autocomplete as you type
Go to my website, and get the completed workbook from this video
For the example that works when you single-click on a cell, go to this page:
Instructor: Debra Dalgleish, Contextures Inc.
Get Debra’s weekly Excel tips: http://www.contextures.com/signup01
More Excel Tips and Tutorials: http://www.contextures.com/tiptech.html
Subscribe to Contextures YouTube: https://www.youtube.com/user/contextures?sub_confirmation=1
VIDEO TRANSCRIPT – Abridged
To make it easy for people to enter data in Excel, you can create drop down lists of items.
In this cell, we have a list of weekdays. I click on that Arrow, click a weekday, and it fills in the cell.
The same thing for months. Here’s a list. It only shows 8 items at a time, but I can scroll down and click December.
Those lists are on another worksheet, and then use data validation, on the Data tab, to set that up.
There are a few limitations though. The font is very small and you can’t change it.
And you can only see eight items at a time.
This doesn’t auto complete. Data validation doesn’t help you fill in the word.
Excel might help you, if it can copy something that you’ve already entered above, but the data validation itself won’t help me type January.
If I start typing, I have to fill in the whole word and move on to the next cell.
So data validation is helpful, but I’ve come up with a work around that you can download on my website and it uses a combo box over cells that have a data validation drop down.
In this corner, hidden away, is a little combo box, and it pops up over a cell, when you double click on it.
So if I double click here, the Combo box comes out of hiding, goes over this cell, and picks up the same data validation list that the cell uses.
So when I click the arrow, I’ll see the list of weekdays. I can click on one, press Enter to go to the next cell.
I’ll double click again, and for this one I could start typing T, and it fills in Tuesday, but if I wanted Thursday, I’ll type in h, and then press Enter.
So it helps with the typing, and if we look at the months, I’ve changed the number of rows that show, so we can see 12.
You can adjust that setting, as well as the font size, and select. And again here, we can type.
So if I want to type August, just have to type two letters and it’s filled in for me.
To see the Combo box, I’m going to double click on a cell and then go to the Developer tab on the ribbon, and there’s a Design Mode button.
When I click that, I’m able to make changes to that Combo box. I can click on the Properties button and here are all the things you could change about that Combo box.
So you get a lot more control over it than you have over data validation.
So you could come down here, and go to the font. Instead of Arial font, you could change the font, and the font size, to whatever suits you.
And here’s where I changed the list rows. This is 12 you could make that a larger number. You’d want to pick a number that’s going to fit on the screen. You don’t want anything that’s too huge, but you can adjust that setting.
And then when you’re done making changes, turn off Design Mode, and you’re ready to go again.
So if you download the sample file from my website, you’ll be able to copy it all into your workbook.
Copy the Combo box and the code onto the worksheet. So if I right click View Code, you’ll see that there’s code that runs, when you double click the cell.
You don’t have to understand all this code, you would just have to get it into your workbook.
For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website, at www.contextures.com