Office Settings & Configuration

Fix Excel Data Validation Errors for List Items with Tildes

Amos GikundaAmos Gikunda Nov 12, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the validated cell

Click the cell that has the Data Validation list and place the cursor in the cell or formula bar.

2
Replace the single tilde

Type the list value with two tildes in place of the single tilde. For example, enter shr~~test instead of shr~test.

3
Confirm the entry

Press Enter and check the cell value. Excel should interpret shr~~test as the literal value shr~test.

Expected result: The cell stores one tilde even though two tildes were entered manually.
Free Microsoft Office alternative

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. 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the Excel XLSX workbook containing the validation list.
  2. 2. Review the list source: Select the validated cells and open the Data tab to inspect the Data Validation settings and source range.
  3. 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. 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.
Create and manage spreadsheet data validation lists in a familiar interface.Use worksheet ranges as reliable sources for lists containing special characters.Open, edit, and save Microsoft Excel XLSX files with broad format compatibility.Enjoy a lightweight Office alternative for everyday spreadsheet work.
microsoft office alternative - wps office

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.