Fix Excel Data Validation Errors for List Items with Tildes
Question details
The user needs to enter or validate a list item containing a tilde without triggering an Excel data validation error.
- Product
- Microsoft Excel data validation
- Device & OS
- not provided
- Scenario
- A data validation list contains an item with a tilde, such as shr~test, and the user types the value directly into the cell instead of selecting it from the drop-down.
- Observed behavior
- Excel may interpret a single tilde as an escape character during manual entry, while selecting the same item from the validation drop-down works correctly.
Check the exact list value and confirm whether the error occurs only when typing it manually. Also verify whether the validation source is a manually entered list or a worksheet range.
Enter two tildes when typing the validated value manually
Use this workaround when Excel rejects a list item containing a tilde during direct cell entry.
Excel treats the tilde as an escape character in some manually entered validation values. Typing two consecutive tildes tells Excel to store one literal tilde in the cell.
Click the cell that has the Data Validation list and place the cursor in the cell or formula bar.
Type the list value with two tildes in place of the single tilde. For example, enter shr~~test instead of shr~test.
Press Enter and check the cell value. Excel should interpret shr~~test as the literal value shr~test.
Select the item from the validation drop-down
Use this option when the list already displays the correct tilde-containing value and manual typing is unnecessary.
Store validation items in a worksheet range
Use a worksheet range for a more reliable list source, especially when the list contains special characters or many entries.
Use WPS Spreadsheets for Compatible Data Validation Lists
WPS Office provides a free, lightweight spreadsheet experience with a familiar interface for creating data validation lists and editing Excel-compatible workbooks. Move the validation source to a worksheet range, test tilde-containing entries, and migrate your XLSX files without changing your everyday workflow.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the Excel XLSX workbook containing the validation list.
- 2. Review the list source: Select the validated cells and open the Data tab to inspect the Data Validation settings and source range.
- 3. Use a worksheet range: Place the allowed values in a worksheet range, then configure the validation list to use that range instead of manually typed list text.
- 4. Test the tilde value: Open the cell drop-down and select the item containing the tilde, then save the workbook in XLSX format if you need to share it with Excel users.

Frequently Asked Questions
Why does Excel reject a list item with a single tilde when I type it manually?
Excel may parse the tilde as an escape character during direct entry. Type two tildes, such as shr~~test, so Excel stores one literal tilde.
Can I avoid the tilde error by using the validation drop-down?
Yes. If the item is already in the validation list, select it from the cell's drop-down arrow. Excel generally handles the stored value correctly this way.
Is a worksheet range better than typing validation items directly?
Yes. A worksheet range is easier to maintain and avoids parsing issues associated with manually entered validation list text, especially when values contain special characters.
Will the double-tilde workaround store two tildes in the cell?
No. In this Excel entry scenario, typing two tildes represents one literal tilde in the resulting cell value.
Can WPS Office open Excel files with data validation lists?
Yes. WPS Spreadsheets supports opening and editing Microsoft Excel XLSX files, including common data validation workflows. Test special-character entries after migration to confirm the expected behavior.




