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

Read More: [Fixed!] Merge Cells Button Is Greyed Out in Excel


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.

Read More: [Solved!]: Excel Page Layout Being Greyed Out (4 Quick Fixes)


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 as .xlsx format which is the latest format of Excel and support all commands.

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

Read More: [Fixed!] Unshare Workbook Greyed Out in Excel


Similar Readings


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.

Read More: [Solved]: If Macro Settings Is Greyed out in Excel


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


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. You can visit our website, ExcelDemy, to get more Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Tags:

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo