AutoComplete in Data Validation list in Mac Excel

Data Validation is very useful way to choose an item from a list of items in Excel, there is only one problem that AutoComplete is not working in Data Validation and that is very handy if you have a long list in the Data Validation dropdown. In Excel for Windows we have an option to use a ActiveX Combobox and set the MatchEntry property, but it is not possible to use ActiveX controls in Mac Office so this is no option.

Workaround for AutoComplete in Mac Excel

What if we want to use AutoComplete in Mac Excel, handy if you have a long list in the Data Validation dropdown. The way it will work is to paste the list of items that you have in the Data Validation dropdown directly above the Data Validation cell. Hide the rows so you do not have to see them, only be aware if you create the sheet with Data Validation cells, that you have empty rows between the cells.

In the screenshot below I have in C30 a Data Validation cell with the data below as list, and the same data also pasted above cell C30 in C24:C29. In the screenshot below you see that if I type the letter p all items that start with p are selectable.

Strawberry Holland
Strawberry Spain


Note: if you try this in Win Excel you will notice that this nice Mac feature is not working the same in Win Excel. In Win Excel the best you can do is to type one of 2 letters and press Alt+DownArrow, the pick list opens with the first matching value selected.

Note: with the Data Validation cell selected on your Mac you can use Option+DownArrow on your keyboard to open the list instead of clicking on the DownArrow in the cell.

TIP: Create Dynamic range name for Data Validation list

Note: I love dynamic ranges so I add this tip to this page because a lot of Excel users not know this.

Example Data
: in A1 off the sheet named Dropdownlist the Header named "Products" and in A2:A? The product names. Then use CMD F3 to create the name Products, and paste the formula below in the "Select the Range of Cells" part to set the dynamic range name. =OFFSET(DropdownList!$A$1,1,0,COUNTA(DropdownList!$A:$A)-1,1)

You can now use Data> Data Validation and in the Allow dropdown choose List and enter this formula =Products
If you use the formula you can add or remove items to/from the list in Column A and the list will update.

More Information

If you want to know more about Data Validation and more visit Debra Dalgleish her site, you will find great content on this site:
Web design by Will Woodgate