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.


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

Enable Editing option to fix Excel Data Validation Greyed Out problem

To stop sharing with others:

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

Go to Share option in Excel

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

Stop Sharing Excel File to fix Excel Data Validation Greyed Out problem

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.

More Than One Worksheet is Selected


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.


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

Set File Format as .xlsx to fix Excel Data Validation Greyed Out problem

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.


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.


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.

Show all objects in Excel

  • 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.


To fix the problem, check the box before the In-cell dropdown option. The dropdown list will show where the change was applied.

mark In-cell dropdown option in Data validation window

Related Articles

<< Go Back to Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF