How to clear restricted values in cells in Excel?
Have you ever tried to enter data into a cell in Excel, only to be stopped by a prompt box like the one shown in the screenshot below? This happens because the cell is set up with a data validation rule that restricts what you can enter. Data validation is often used by workbook creators to ensure information is entered consistently, but sometimes, you may want to remove these restrictions to freely edit or repurpose your worksheet. This tutorial provides practical solutions for clearing restricted values and removing the associated validation settings in Excel.
Clear restricted values in cells in Excel
Quickly clear restricted values in cells with Kutools for Excel
Use VBA to remove data validation restrictions
Clear restricted values in cells in Excel
To manually remove data validation restrictions from cells in Excel, you can follow these steps. This method is recommended when you only need to clear validation from a few cells, and it works well with Excel’s built-in features without requiring add-ins or extra configuration.
1. Select the cell or cells you want to remove the data validation from, then click Data > Data Validation on the Ribbon. This opens the Data Validation dialog, allowing access to existing rules and restrictions for the selected range. See screenshot:
2. In the Data Validation dialog box, make sure you are on the Settings tab. Click the Clear All button, which removes the validation rule from the selected cell or range, and then click OK. Note that if you select multiple cells, this will clear all validation rules across the selection, making it possible to enter any value. See screenshot:
After these steps, the selected cell(s) will no longer reject or limit the values you enter, and you can work with the contents as needed. Be aware that removing validation also removes any input messages or error alerts associated with those cells.
Tip: If you need to clear validation from non-adjacent cells, hold down Ctrl while selecting the cells before accessing Data Validation.
Quickly clear restricted values in cells with Kutools for Excel
For users who frequently work with large data sets or need to remove data validation restrictions from multiple ranges efficiently, the Clear Data Validation Restrictions utility of Kutools for Excel is a practical choice. Kutools enables batch clearing of all data validation rules from any selection or even several selections at once, helping save time and reduce manual effort.
Before applying Kutools for Excel, please download and install it firstly.
1. Select a range or multiple ranges where you want to delete all data validation restrictions. You can select single or multiple non-adjacent cells, entire columns, or custom ranges according to your needs. Next, click Kutools > Prevent Typing > Clear Data Validation Restrictions on the Ribbon.
2. A Kutools for Excel dialog box will appear, prompting you to confirm the action. Click OK to begin clearing data validation restrictions from your selected ranges. The process runs instantly for any amount of data.
All data validation restrictions are now removed from the selected cells or ranges. This approach is especially efficient if you have complex selections or want to process an entire worksheet.
Pros: Kutools batch clearing saves time with large data sets, makes it easy to clear multiple validations at once, and does not require manual navigation between selection dialogs.
Cons: Requires Kutools add-in to be installed and activated. Not available in Excel without this third-party extension.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Use VBA to remove data validation restrictions
If you need to automate the removal of data validation restrictions, especially across multiple ranges or sheets, using VBA (Visual Basic for Applications) is an effective approach. This is best suited for users comfortable with Excel macros, and offers flexibility for batch processing beyond manual or add-in methods. With VBA code, you can programmatically target any cell or range in your workbook and instantly clear its data validation rules.
1. Open Excel and go to Developer Tools > Visual Basic. In the new Microsoft Visual Basic for Applications window, click Insert > Module, and copy the following VBA code into the module:
Sub ClearDataValidation()
Dim Rng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set Rng = Application.Selection
Set Rng = Application.InputBox("Select the range to clear data validation", xTitleId, Rng.Address, Type:=8)
If Not Rng Is Nothing Then
Rng.Validation.Delete
End If
End Sub2. Click the 
button to execute the macro. A dialog box will prompt you to select the range where you want to clear data validation. After confirming, all information validation rules for the selected cell(s) will be deleted.
Precautions: Make sure to save your workbook before running VBA code, as this action cannot be undone. Best practice is to use this solution when managing multiple ranges or automating repeated tasks. If you get an error, ensure you have enabled macros and have the correct Developer Tools settings.
Summary and troubleshooting suggestions:
When working with restricted values or data validation rules in Excel, choose the removal method that best fits your scenario. Use Excel’s built-in Data Validation for smaller or simple data sets. Choose Kutools for batch processing and enhanced control over multiple ranges. For advanced tasks or automation, VBA is the best approach. Always be cautious before batch-clearing validations, and consider saving a backup of your workbook.
If you encounter issues such as validation rules not clearing, or VBA errors: double-check your selected ranges, worksheet protection settings, and macro permissions. Restart Excel or review the targeted cell addresses, and always save your work before performing bulk actions.
Related articles:
- How to clear contents of combo box with VBA code in Excel?
- How to clear contents and formatting at the same time in cells in Excel?
- How to clear contents of named range in Excel?
- How to clear specified cell contents if value of another cell changes in Excel?
- How to clear specified cell contents on open and exit in Excel workbook?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in