Excel Data Validation Greyed Out: 4 Reasons with Solutions

Method 1 – Excel File Is Protected or Shared

If the Excel file is in protected view or has been shared by others, the Data Validation window may be grayed out and unable to be used.

Solution:

After opening an Excel file, click the Enable Editing option to stop the protected view.

To stop sharing with others:

Click on the Share button at the right-top corner of the workbook.
Click on the Manage Access option.

Click the Stop Sharing option in the Manage Access window to prevent others from accessing the file.

Method 2 – More Than One Worksheet Is Selected

You may select multiple worksheets at once by holding the Ctrl key. If you select more than one worksheet, the Data Validation menu will not work and will become greyed out.

Solution:

You have to unselect the worksheets and try using the Data Validation tools. It may work.

Method 3 – File Format Is Not in XLSX

You may save the Excel in older formats like .xls or others. Data Validation may not work in older versions of Excel.

Solution:

You must save the workbook in .xlsx format, which is the latest format of Excel and supports all commands.

Method 4 – Macros Are Enabled on Particular Cells

If you apply any Macro or VBA code in the workbook, the Data Validation option may not work. Similarly, when you want to apply Data Validation to the cells where the macro works, It may not work.

Solution:

You must disable the VBA code before using the Data Validation feature. You can also try applying Data Validation to cells without a Macro code.

How to Fix When Excel Data Validation Drop-Down List Is Not Showing

Method 1 – Turn On the Show All Objects Option

The Excel data validation dropdown list may disappear if objects are set to hide in the Excel options, resulting in the Data Validation dropdown menu not appearing.

Solution:

To unhide objects in Excel:

Go to the File tab >> Option.
You’ll see the following dialog box, Excel Options; go to the Advanced option.
The Nothing (hide objects) option is On, so the drop-down list was not visible.
Unmark the option and select the All option, like the following figure.

You may get the result and see that the data validation drop-down menu is visible.

Method 2 – Mark the ‘In-cell dropdown’ Option

Another reason the drop-down menu disappears in Excel is because the option “In-cell dropdown” should be left unchecked, so the drop-down arrow is not visible.