How to use data validation in Excel on Mac: step by step for Microsoft 365

What data validation does in Excel

Data validation is a rule you attach to cells. Excel checks each entry against that rule. You decide what counts as valid. You also decide whether Excel blocks a bad entry or only warns about it.

Most people use it for a drop-down list, a limit on numbers or dates, or a custom rule. This guide starts with the drop-down list. The other rule types follow the same steps, with different settings.

Before you start: version, sheet, and workbook checks

The steps here match Excel for Microsoft 365 for Mac. Microsoft's article also covers Excel 2024 and Excel 2021 for Mac, and it says the steps do not change between those versions. If a label on your screen looks different, search the Data tab for Data Validation or Validate.

The Data Validation command can be greyed out when the sheet is protected or the workbook is shared. Unprotect the sheet or stop sharing, then try again. The sheet protection guide covers the password side.

Typing list items or pointing at a range

The Source box takes typed items or a range. Type items separated by commas, such as Not started,In progress,Done. Or select a range on the sheet, and the dialog shrinks so you can see the cells.

Typed items stay fixed inside the rule. A range works better when the list will grow or change. Select only the items under the header, because a selected header shows up as a choice.

Input message and error alert settings

The Input Message tab shows a note when someone selects the cell. Add a short title and a message that says what to enter. Microsoft's drop-down article allows up to 225 characters for the title and message. That figure comes from the Windows section, so check it on your Mac build.

The Error Alert tab controls what happens with a bad entry. Stop blocks the value. Warning and Information show a message but still accept the value. Microsoft's articles name the third style differently, calling it Information in one and Important in the other.

A blank alert title is easy to miss. Excel then shows Microsoft Excel as the title and a default message. Write your own title so people know which rule fired.

Worked example: a status column in a project tracker

Say your column holds project status. You want only Not started, In progress, or Done. Select the status cells, open Data Validation, set Allow to List, and type Not started,In progress,Done in Source.

On the Input Message tab, enter the title Status and the message Pick a status from the list. On the Error Alert tab, choose Stop. Write a message such as Only these statuses are allowed.

Click OK, then click a status cell and type Finished. Excel rejects it because Finished is not on the list. Type Done instead, and the entry is accepted. The test takes less time than the setup, and it proves the rule works.

Other rule types: numbers, dates, and custom formulas

Number and date rules use the same dialog. Pick the type, then fill in the limits that appear. The fields change to match the type you choose.

Custom takes a formula, so it needs more care. For unique IDs, write a formula that counts how many times the value already appears in the column. Accept the entry only when it appears once. Use absolute references for the column, or the rule shifts as you apply it to more cells.

Keep a drop-down list up to date

Microsoft recommends putting list entries in an Excel table. Select a cell in the range, then create the table. Microsoft's article gives Ctrl+T for this. On a Mac, you can also use the Table button on the Insert tab. Then point the Source box at the table column.

Microsoft says tables update drop-downs automatically when you add or remove items. The October 2026 What's New post also says named tables at creation are now available to Mac users, so you can name the table as you build it.

A Microsoft Q&A moderator adds a caveat. A list rule does not create a live link to its source, so chosen values stay as plain text. The workaround in that thread is a VBA macro. Test a changed item before you depend on the list.

Removing data validation from cells

Select the cells that have the rule. Open Data > Tools > Data Validation and click Clear All. Then click OK. The rule goes away, and the values already in the cells stay where they are.

To change a rule instead, open the same dialog and edit the settings. You do not need to clear it first.

Excel for the web: same feature, different labels

Excel for the web uses the same feature with the same steps. The labels differ, and the Tools group on the Data tab becomes the Data Tools group. Read the labels again before you click if you switch between the Mac app and the browser.

Shortcuts and what Microsoft documents

Microsoft's keyboard shortcut page does not list a Mac shortcut for the Data Validation dialog. It writes key names like Cmd, Option, and Ctrl. This guide uses the symbols ⌘, ⌥, ⌃, and ⇧ instead, so the keys match your keyboard.

The shortcut worth knowing is ⌥ Down Arrow. On a cell, it opens the AutoComplete list. Microsoft documents the same key for filter and PivotTable pop-up menus. It does not document it for the validation drop-down, so test it on your build before you rely on it.

Drop-down problems on Mac and how to fix them

The Data Validation command is greyed out

Protection or sharing is the usual cause. Unprotect the sheet from the Review tab or stop sharing the workbook, then open the dialog again.

The drop-down arrow does not appear

Open the Settings tab and confirm that In-cell dropdown is ticked. Then resize the cell, since a narrow cell can hide the arrow. The generic checks in a Microsoft Q&A thread include these same steps, so they are a good place to start.

The list opens outside the Excel window

A Microsoft Q&A poster reported this on an M1 Pro MacBook with an external display. The version was 16.71 on macOS 13.2.1. The list drew above the Excel window and could not be clicked in Mission Control. The thread has no accepted fix. The poster said In-cell dropdown was already ticked and resizing did not help.

The list does not update when the source changes

A list rule does not link live to its source. Use an Excel table, as described above, and test a change before you rely on it. If you need more than that, the Q&A workaround is a VBA macro, which needs more setup than this guide covers.

Mistakes people make with drop-down lists

• Including the header row in the Source range, so the header shows up as a choice.
• Choosing Warning for a value that must match the list exactly. Warning lets the value through.
• Leaving the alert title blank, so Excel shows Microsoft Excel as the title.
• Typing items by hand, then editing the same list in several places that drift apart.
• Trying to add a rule to a protected sheet without unprotecting it first.
• Skipping the test. Type a bad value once before you share the file.

Check your Mac build before you troubleshoot

Microsoft's release notes list the latest Mac build as 16.114.3 (Build 26102720), released October 27, 2026. That entry says only quality and performance improvements. The notes for August 11 to October 27, 2026 mention no data validation changes.

To find your build, open Excel > About Excel. If your screen differs from these steps, your build may be newer than the notes this guide used.

Where to go next with lists and rules

A named range makes a long Source address easier to read and reuse. The named range guide walks through the steps.

If the cells you validate hold numbers stored as text, fix that first with the text to number guide. Then your number rules check real numbers.

Quick check before you share the file

1. Select a cell and confirm that the arrow appears.
2. Type a value that is not on the list and check the alert.
3. Type an allowed value and confirm it stays in the cell.
4. Confirm that the header row is not in the Source range.