Function Problems

Fix Duplicate Month Names in a WPS Excel Drop-Down List

Muhammad TalhaMuhammad Talha Nov 13, 2026 868 views

Question details

The user wants to remove duplicate month names from an Excel drop-down list after noticing that two displayed months appear identical but are treated as separate entries.

Fix Duplicate Month Names in a WPS Excel Drop-Down List
Product
WPS Office Spreadsheets
Device & OS
not provided
Scenario
The user has a data validation drop-down list of months in an Excel workbook and needs the list to show each month only once.
Observed behavior
Duplicate month names remain in the list because the source cells contain trailing spaces, nonbreaking spaces, hidden characters, inconsistent formatting, or mixed text and date values.
Before you start

Select the source range that feeds the drop-down list and check for extra spaces, nonbreaking spaces, hidden characters, and mixed text/date formatting before rebuilding the validation list.

Solution 1Recommended

Clean the Source Month Values with a Helper Formula

Use this recommended method when duplicate month names are caused by trailing spaces, nonbreaking spaces, or other invisible characters in the source cells.

The formula removes common space characters, nonprinting characters, and nonbreaking spaces from the month source data. After cleaning, create the drop-down from the cleaned unique range so WPS Spreadsheets treats matching month names as the same value.

1
Insert a helper column

Click the header of the column next to your month source list, right-click, and choose Insert to add a blank helper column.

2
Enter the cleaning formula

In the first helper cell next to the first month value, type =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) and press Enter. Replace A2 with the first cell of your source month list.

3
Fill the formula down

Drag the fill handle from the first helper cell down through all source month rows to clean the entire list.

4
Create a unique cleaned list

Select the cleaned helper values, click Data > Remove Duplicates, confirm the column, and click OK so only unique month names remain.

5
Create the drop-down from cleaned values

Select the cell where the drop-down is needed, click Data > Validation, choose List, and set the Source to the cleaned unique range.

Clean the Source Month Values with a Helper Formula
Duplicate month names removed: The drop-down list now shows each month once because the hidden characters and spaces were removed from the source data.

Create a Clean Month Drop-Down List in WPS Spreadsheets

WPS Office Spreadsheets lets you clean month source data, remove duplicates, and create a drop-down list with unique values using the Data Validation feature.

  1. 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the Excel workbook that contains the month source list and the drop-down cell.
  2. 2. Clean the month source values: Use a helper column with =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) to remove trailing spaces and hidden characters.
  3. 3. Remove duplicate month names: Select the cleaned month range, click Data > Remove Duplicates, confirm the column, and click OK.
  4. 4. Create the drop-down validation: Select the target cell, click Data > Validation, choose List, and set Source to the cleaned unique month range.
  5. 5. Test the drop-down list: Click the validation cell and open the drop-down arrow to confirm each month appears only once.
Cleans hidden spaces and nonbreaking characters from month listsRemoves duplicate entries so each month appears once in the drop-downWorks with Excel .xlsx files and maintains Microsoft Excel format compatibilityFree and lightweight alternative to Microsoft Excel for everyday list tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel drop-down list show the same month twice?

Duplicate-looking months are often caused by trailing spaces, nonbreaking spaces, hidden characters, or a mix of text and date values in the source range.

How do I clean nonbreaking spaces from month names in WPS Spreadsheets?

Use the formula =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in a helper column and fill it down to remove regular spaces, nonprinting characters, and nonbreaking spaces.

Can I make the drop-down list use only unique month names?

Yes. Create a unique source range by selecting the cleaned values and clicking Data > Remove Duplicates, or use the UNIQUE function if your WPS version supports it, then set the validation Source to that range.

Where is Data Validation in WPS Spreadsheets?

Select the cell where you want the drop-down, click the Data tab on the ribbon, and then click Validation. In the dialog, choose List and set the Source range.

What if May appears twice because one value is a date and the other is text?

Select all source month cells, open Format Cells from the Home tab, and apply the same format to all cells. Then create the drop-down from the corrected list.