Set a Default Cell Value with Data Validation in Excel
Question details
Set a default value in a cell while allowing users to replace it with a value permitted by data validation.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A validated cell should display a default value but remain editable by users.
- Observed behavior
- A formula in the cell is replaced when a user manually enters another value.
Choose a default that meets the cell's data validation rules. Use a desktop version of Excel that supports VBA and confirm that macros can be enabled for the workbook.
Fill a Blank Validated Cell Using a Worksheet Event
Use VBA to insert a default when the cell is blank, while keeping its data validation in place.
The event below checks A1 whenever the worksheet selection changes. If A1 is blank, it inserts the default; otherwise, it leaves the existing value unchanged.
A formula alone cannot remain in the same cell after a user manually replaces its result.
Select A1, open Data > Data Validation, and configure the allowed values. Make sure the default you plan to use is permitted.
Press Alt+F11 to open the VBA editor. In Project Explorer, double-click the worksheet containing A1.
Paste this code into the worksheet module, replacing "default value" with your permitted default: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("A1").Value = "" Then Range("A1").Value = "default value" End If End Sub
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), enable macros when prompted, and change the selection on the worksheet. Replace the default in A1 with an allowed value to verify that it remains in place.

Manage Validated Spreadsheets with WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface and Microsoft Office format compatibility for an easy transition. Use WPS Spreadsheets to work with validated cells; the Excel VBA event above is specific to Excel and may not work the same way in WPS.

Frequently Asked Questions
Can a formula provide a default and still be edited in the same cell?
No. Typing a replacement directly into the cell overwrites its formula. A worksheet event can instead insert a static default when the cell is blank.
Will the default overwrite a value the user entered?
No. The example checks whether A1 is blank before inserting the default, so it leaves a nonblank user entry unchanged.
Why is the default not appearing immediately?
The example runs when the worksheet selection changes. Click another cell to trigger it, and check that macros are enabled in the workbook.
What happens if the default is not allowed by data validation?
Choose a default that meets the rules under Data > Data Validation. VBA can write values without triggering the same validation checks as manual entry.
Where should I put the VBA code?
Open the VBA editor with Alt+F11 and paste the code into the module for the worksheet containing the cell, not into a standard module.




