Excel Data Validation Greyed Out (4 Reasons with Solutions)

You have come to the right place if you are looking for the answer or some unique tips to fix the problem when data validation is greyed out in Excel. There are some ways to fix this problem. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.

4 Reasons with Solutions If Data Validation Is Greyed Out in Excel

In this section, I will show you the possible reasons and how to fix the above problem on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

Excel Data Validation Greyed Out

Reason 1: Excel File Is Protected or Shared

If the Excel file is in protected view or has been shared by others then you may see that the Data Validation window is greyed out and is not allowing you to use it.

🧲 Solution:

  After opening an Excel file, you have to click on 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.
  • Then, click on the Manage Access option.

Go to Share option in Excel

  • In the Manage Access window, click on the Stop Sharing option to stop the access of the file to others.

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

Reason 2: More Than One Worksheet Is Selected

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

More Than One Worksheet is Selected

🧲 Solution:

In this case, you have to unselect the worksheets and try to use the Data Validation tools. There is the possibility that it will work.

Reason 3: File Format Is Not in XLSX

Sometimes you may save the Excel in older formats like .xls or others. In older versions of Excel, Data Validation may not work.

🧲 Solution:

For this, you have to 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

Reason 4: Macros Are Enabled on Particular Cells

Sometimes, when you apply any Macro or VBA code in the workbook, the Data Validation option may not work. Most of the time, when you want to apply Data Validation on the cells where the macro is working, Data Validation may not work.

🧲 Solution:

So, you have to disable the VBA code before using the Data Validation feature. Or try to apply Data Validation on cells where no Macro code is there.

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

Sometimes while working with the Data Validation feature, you may find that the drop-down list is not working or not appearing in the cells. There can be various reasons and of them, I am showing the most important ones below:

1. Turn On Show All Objects Option

Excel data validation dropdown list may disappear if objects are set hidden in the Excel Options. As a result, the Data Validation drop-down menu does not appear.

🧲 Solution:

To unhide objects in Excel:

  • Go to the File tab >> Option.
  • Then, you’ll see the following dialog box namely Excel Options, here, go to the Advanced option.
  • As you see the Nothing (hide objects) option is On, so the drop-down list was not visible.
  • So you have to unmark the option and select the circle of the All option like the following figure.

Show all objects in Excel

  • Now, you may get the result and see that the data validation drop-down menu is visible.

2. Mark the ‘In-cell dropdown’ Option

There can be another reason behind the disappearance of the drop-down menu in Excel. And that leaving unchecked the option named “In-cell dropdown”. That’s why the drop-down arrow is not visible.

🧲 Solution:

If you want to fix the problem, check the box before the In-cell dropdown option. And you’ll see that the drop-down list shows where applied.

mark In-cell dropdown option in Data validation window

Read More: [Fixed] Data Validation Not Working for Copy Paste in Excel


In this article, you have found how to fix the problem when data validation is greyed out in Excel. I hope you found this article helpful. Please leave comments, suggestions, or queries if you have any in the comment section below.

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