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.

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.

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

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

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.

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

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.

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

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

Conclusion

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

• How to Perform Data Validation for Alphanumeric Only in Excel
• How to Use Data Validation in Excel with Color
• Excel Data Validation for Date Format
• How to Circle Invalid Data in Excel
• How to Create Data Validation with Checkbox Control in Excel

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