Cara membuat dropdown di excel macbook

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.

Table of Contents Show

• Add a drop-down list to a cell
• Add a drop-down list to a column or row
• How do I edit Data Validation in Excel for Mac?
• Where can I see Data Validation in Excel?
• Why can't I find Data Validation in Excel?
• Where to find Excel Add

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.

Tomato-Holland
Tomato-Spain
Strawberry Holland
Strawberry Spain
Paprika-Holland
Paprika-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.

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)

Paul says:
October 11, 2018 at 1:14 am

Hello,
we have a spreadsheet where the first 2 columns use data validation via 2 separate drop down lists. On the first row (under the Headers) the data validation works as expected - ie a user clicks in the first row cell and can select the from the list, ditto for the second cell in the same row.
The problem we have: after entering free form text / data into the remaining row cells and pressing enter / return the new row does not show the data validation in the first two columns when the user clicks in either cell unless some free form text / data is entered in one of the remaining row cells.
Is there a way to have the Data Validation selection working for the first 2 columns on a new row without having to enter text / data in one of the other row cells?
We are using Excel 2013 with "Include new rows and columns in table automatically as you work" selected under AutoCorrect options. We do not VBA.
Thanks for any help / advice.

Using custom lists in Microsoft Excel on your Mac makes tedious data entry relatively quick and easy. Drop-down lists are no different; you can create one in just a few clicks.

Drop-down lists in Excel are ideal for limiting the options for cell entries. They come in handy for selecting items like colors, sizes, products, people, days, and so much more.

Here’s how to quickly create a drop-down list in Excel on Mac.

Add a drop-down list to a cell

Open Microsoft Excel and the document where you want to add the drop-down list. Then, follow these steps.

1) Enter your list items onto a spreadsheet. You can use a new sheet in your workbook or existing cells where you have the items entered.

2) Click the cell where you want to insert the drop-down list.

3) Open the Data tab and click Data Validation from your ribbon.

4) In the pop-up window, click the Settings button.

5) Under Validation criteria > Allow, select List.

6) Click inside the Source box and then drag through the cells that should appear in the drop-down list. (You can also type in the cell range yourself if you’re comfortable with the format.)

Note that the pop-up window will minimize as you perform this action. When you release the cursor at the end of the cells you’re selecting, the window will maximize again.

7) Hit the Enter key.

8) Click OK.

The pop-up window will disappear, and you should see that the cell for your drop-down list contains an arrow for you to select an item.

Add a drop-down list to a column or row

If you want to use the same drop-down list options across a whole column or row, that’s simple enough. Select the entire column by clicking the letter at the top or the entire row by clicking the number on the left.

Then, follow the steps we listed above, beginning with Step 2 for the Data tab and Data Validation button.

Adding a drop-down list to your spreadsheet is convenient for selecting from several pre-determined items. And you can create multiple drop-down lists for different items all on one sheet. Is this an Excel feature that you find helpful? Or, maybe you haven’t created drop-down lists in Excel yet, but will now? Let us know!

How do I edit Data Validation in Excel for Mac?

Select a cell or cells that reference your Excel Data Validation list, i.e. cells containing a drop-down box that you want to edit. Click Data Validation (Excel ribbon > Data tab). Delete or type new items in the Source box. Click OK to save the changes and close the Excel Data Validation window.

Where can I see Data Validation in Excel?

Find cells that have data validation rules.

On the Edit menu, point to Find, and then click Go To..

Click Special..

Select Data Validation..

To find all cells with data validation, select All, and then click OK. To find cells that have the same validation rules as another cell, select Same, and then click OK..

Why can't I find Data Validation in Excel?

You might currently be entering data - The Data Validation command is not available while you are entering data in a cell. To finish entering data, press Enter or ESC to quit. The worksheet might be protected or shared - You cannot change data validation settings if your workbook is shared or protected.

Where to find Excel Add

On the Tools menu, select Excel Add-ins. In the Add-Ins available box, select the add-in you want, and then click OK.

Bagaimana cara membuat dropdown list di excel?

Bagaimana Cara Membuat Dropdown?

Apa itu Drop Down List Excel?