Data Validation is very useful way to choose an item from a list of items in Mac Excel, you can find it under the Data tab in the Data Tools group. There is only one problem that AutoComplete is not working in Data Validation in the Mac Excel versions before 16.77 (on this moment Beta Channel). AutoComplete is very handy if you have a long list in your Data Validation dropdown. If you run Mac Excel 16.77 or higher it is working now (Thanks Excel team), and if you run a older version you can use the workaround below if you want.

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: Use a dynamic range name that point to your data for the data validation dropdown, read the info about it on the bottom of this page.

What if we want to use AutoComplete in Mac Excel versions before 16.77, 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 want in the Data Validation dropdown directly above the Data Validation cell. Hide the rows so you not see them, only be aware if you create a sheet with Data Validation cells that you have empty rows between the cells that you can hide after you have enter the dropdown items.

This is the example data in C24:C29

Tomato-Holland
Tomato-Spain
Strawberry Holland
Strawberry Spain
Paprika-Holland
Paprika-Spain


In the screenshot below I have in C30 a Data Validation cell with the data in C24:C29 as list. Use Data> Data Validation and in the Allow dropdown choose List and select the range C24:C29. In the screenshot below you see that if I type the letter p all items that start with p are selectable.

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.

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 Define Name in the Formulas Tab to create the name Products, and paste the formula below in the "Refers to:" 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.

Note: it is possible that you must use another Separator for in the formula on your system, for example ; instead of ,

If you want to know more about Data Validation and more visit Debra Dalgleish her site, you will find great content on this site: https://www.contextures.com/datavalidation/index.html