Fix Excel Removing Data Validation When Saving an XLSM File
Question details
User needs to stop Excel from reporting corrupted content and removing drop-down data validation rules after converting an XLSX workbook to an XLSM file, especially rules that use merged cells or source ranges on another worksheet.
- Product
- Microsoft Excel (macro-enabled .xlsm workbooks)
- Device & OS
- not provided
- Scenario
- Converting an XLSX workbook to XLSM, saving the macro-enabled file, and keeping drop-down data validation rules intact.
- Observed behavior
- Excel reports corrupted content and removes some drop-down data validation rules, particularly rules involving merged cells or list sources on another worksheet.
Create a copy of the workbook and open it in Excel with the Developer tab enabled. Identify the drop-down cells that disappear and note whether they use merged cells, List validation, or source ranges on another worksheet.
Check VBA code that modifies or deletes data validation rules
Use this first when macros exist in the converted XLSM workbook; VBA may remove or rebuild validation when the file opens or saves.
Open the workbook in Excel, press Alt+F11 to open the Microsoft Visual Basic for Applications editor, and expand the VBAProject for the workbook.
Press Ctrl+F, search for Validation, Delete, Add, or Modify, and review each macro in ThisWorkbook and modules that could change drop-down lists.
Comment out or delete the suspicious macro, save the workbook as .xlsm, close and reopen it, then confirm whether the data validation rules remain.
Recreate data validation using unmerged cells and a named range or table source
Use this to prevent Excel from dropping rules that reference merged cells or other worksheets; a stable named range or table column is safer for XLSM files.
Test in a current Excel version and run Office Repair
Use this if the issue occurs even in a clean workbook or after rebuilding validation; it checks whether the Excel installation itself is causing the corruption.
Try WPS Office as a Free Excel Alternative for XLSM Workbooks
If Excel keeps repairing or removing data validation when saving .xlsm files, WPS Office is a free, lightweight alternative that opens, edits, and saves macro-enabled workbooks with a familiar ribbon interface, Excel format compatibility, and seamless migration.
- 1. Download WPS Office: Visit the official WPS Office website, download the free installer for your Windows or macOS device, and complete the installation.
- 2. Open the .xlsm workbook: Launch WPS Office, click Menu > Open, and select the macro-enabled Excel workbook you need to test or edit.
- 3. Verify and save data validation: Click Data > Validation to review drop-down list sources, then save the workbook from WPS Office to see if the validation remains intact.
Frequently Asked Questions
Why does Excel remove data validation when I save as an XLSM file?
It usually happens when Excel's repair process flags the workbook as corrupted. Macros that modify validation, merged input cells, or direct cross-sheet list sources can trigger this after an XLSX-to-XLSM conversion.
Can WPS Office open and save an XLSM file without losing drop-down lists?
Yes, WPS Office can open and save .xlsm workbooks. However, if the file is already corrupted, you should still rebuild the validation using an unmerged cell and a named range or table source.
How do I create a named range for a data validation list?
Select the source cells, click Formulas > Define Name, enter a name, and click OK. Then in Data Validation, choose List and enter =YourName as the source.
Should I avoid merged cells for data validation drop-downs?
Yes, apply data validation to a single unmerged input cell. Use merged cells only for labels or headers so Excel is less likely to remove the rule during repair or conversion.
How do I run Office Repair if the issue affects clean workbooks?
Close Excel, go to Windows Settings > Apps > Installed apps, select Microsoft Office, choose Modify, and run Quick Repair first. If the problem continues, run Online Repair.
