Others

How to Show Used and Unused Groups in Excel Dropdown Lists

Maira Mehtab Sep 20, 2026 870 views

Question details

The user wants to create a dropdown list containing a static set of groups (e.g., 001 to 100) that visually or textually identifies which groups have already been used in the table, without removing them from the available options.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing a tracking sheet or group assignment table where it is necessary to see all available options while clearly distinguishing which groups are already assigned.
Observed behavior: Standard dropdown lists do not natively label items as used or unused dynamically. Using a FILTER formula removes used items entirely, which does not meet the requirement of keeping all items visible.

Before you start

Ensure you have a complete master list of your group numbers (e.g., 001 to 100) stored in a separate worksheet or hidden column to serve as the foundation for your formulas.

Solution 1Recommended

Use a Helper Column with COUNTIFS to Label Used Groups

Create a dynamic list next to your master list that appends a 'Used' label based on current data entries, then use this new list for your dropdown validation.

Excel's Data Validation dropdowns cannot natively change font colors for individual items inside the menu. To keep all items visible while showing their status, we must combine the group name with a text status label using a formula.

Set up the Master List

In a new column (e.g., Column X), list all your group numbers sequentially from 001 to 100.

Create the Helper Formula

In the adjacent column (e.g., Column Y), enter the formula: =X2 & IF(COUNTIFS($A$2:$A$100, X2)>0, " (Used)", ""). Replace $A$2:$A$100 with the actual column range where users select groups. Drag the formula down to the bottom of the list.

Apply Data Validation

Select the cells in your main table where you want the dropdowns to appear. Go to the Data tab, click 'Data Validation', choose 'List', and select your new helper column (Column Y) as the Source.

Dynamic Updates: As soon as a user selects a group in the main table, the dropdown source automatically updates to append '(Used)' next to that group number.

Solution 2

Highlight Used Items Using Conditional Formatting

If changing the dropdown text is not ideal, use conditional formatting to highlight duplicate selections in your main table.

Advanced Data Validation with WPS Spreadsheet

Manage Dynamic Dropdown Lists Easily in WPS Office

WPS Spreadsheet provides powerful data validation and formula capabilities, making it easy to set up dynamic dropdowns and track used items effortlessly using helper columns and COUNTIFS.

1. Open your workbook: Launch WPS Spreadsheet and open the file where you need to track group assignments.
2. Add helper columns: Create your list of items and use the COUNTIFS formula to append '(Used)' status labels dynamically.
3. Set up Data Validation: Navigate to Data > Validation and select your dynamically updating helper column as the list source.

Fully compatible with Microsoft Excel formulas like COUNTIFS and FILTERIntuitive Data Validation menu for creating custom dropdownsLightweight application with fast performance for large data setsFree to use for everyday office tasks and data management

microsoft office alternative - wps office

Frequently Asked Questions

Can I make used items disappear from the dropdown instead of labeling them?

Yes. You can use the FILTER function combined with COUNTIFS to create a dynamic array of only unused items, and use that array as your Data Validation source. However, this removes the used items entirely from the list.

Can I color-code used and unused items directly inside the dropdown menu?

No, Excel and WPS Spreadsheet do not support applying cell colors or conditional formatting to the text inside the dropdown list menu itself. You can only format the cell once the item has been selected.

Can VBA macros achieve color-coded dropdown items?

VBA macros cannot natively change colors inside the standard Data Validation dropdown list. A macro could be used to create a custom UserForm functioning as a specialized dropdown, but this requires advanced programming.

Office Quick Solutions