Function Problems

How to Combine Excel Data Validation Categories into One Selection

Ayan Masood Oct 1, 2026 869 views

Question details

The user needs to create a dynamic data validation dropdown list that aggregates multiple categories into one selection.

How to Combine Excel Data Validation Categories into One Selection

Product: Excel
Device & OS: not provided
Scenario: Creating a unified data validation list to display aggregated categories like 'Total Deals' using dynamic arrays without affecting original data.
Observed behavior: The user wants to generate a combined source list with the FILTER function and apply it as a data validation source using named ranges.

Before you start

Ensure you are using a spreadsheet software version that supports dynamic arrays (such as Excel 2021, Microsoft 365, or the latest WPS Office). Identify a clear, unused section in your worksheet where the combined array formula can safely spill its results without overwriting existing data.

Solution 1Recommended

Use the FILTER Function and a Named Range

Create a combined list using the FILTER function to aggregate categories, define a named range for the spilled results, and apply it to your data validation dropdown.

Dynamic arrays allow you to extract and list specific categories automatically. By utilizing the FILTER function along with the addition (+) operator, you can simulate an 'OR' logic to pull multiple categories (like 'live' and 'completed') into a single list.

Generate the combined list

Select an unused cell in your worksheet (e.g., Z1) and enter a formula like =FILTER(A2:A100,(B2:B100="live")+(B2:B100="completed")). Press Enter to allow the results to spill down the column.

Define a named range for the spilled array

Navigate to Formulas > Define Name. Enter a recognizable name (e.g., CombinedDeals) and set the 'Refers to' field to your spilled formula cell followed by a hash mark (e.g., =Sheet1!$Z$1#). This ensures the named range expands or contracts dynamically.

Apply the data validation list

Select the cell where you want your dropdown menu. Go to Data > Data Validation, choose 'List' under the Allow dropdown, and type =CombinedDeals in the Source field. Click OK.

Set up independent total calculations

Use separate COUNTIFS or SUMIFS formulas referencing your original data to calculate totals for live, completed, and aggregated deals. This ensures that selecting 'Total Deals' from your new dropdown does not disrupt your original category calculations.

Use the FILTER Function and a Named Range

Dynamic Updates: Whenever new data matching the criteria is added to your source range (A2:A100), the spilled array and your dropdown list will update automatically.

Advanced Data Management

Create Dynamic Dropdowns Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic arrays and the FILTER function, allowing you to combine multiple data categories into a single data validation list seamlessly. It offers high performance and an intuitive interface for managing complex data operations.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the categories you want to combine.
2. Apply the FILTER formula: In a blank column, type your FILTER formula to extract the desired categories and press Enter.
3. Configure Data Validation: Navigate to the Data tab, select Validation, choose 'List', and reference your new array using the # operator (e.g., =$Z$1#).

Fully supports dynamic array formulas including FILTER and UNIQUE for data validation.100% compatible with Microsoft Excel .xlsx format, ensuring formulas work perfectly.Intuitive Name Manager to easily track and adjust your dynamic spilled ranges.Lightweight architecture for fast calculation of complex arrays.

microsoft office alternative - wps office

Frequently Asked Questions

How do I reference a dynamic array in Data Validation directly?

You can reference a dynamic array directly in the Data Validation source box by typing the cell address of the top-left cell of the array followed by the spill operator (#), for example, =$Z$1#. Alternatively, you can point to a Named Range that uses the spill operator.

Can I combine more than two categories with the FILTER function?

Yes, you can add more conditions by using the plus (+) operator within the include argument. For example: =FILTER(A2:A100, (B2:B100="live") + (B2:B100="completed") + (B2:B100="pending")).

Why is my FILTER function returning a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds no results matching your criteria. You can fix this by adding the optional [if_empty] argument, like =FILTER(A2:A100, (B2:B100="live"), "No Data"), to handle empty results gracefully.

How can I remove duplicates from my combined validation list?

You can wrap your FILTER function inside the UNIQUE function. The formula would look like =UNIQUE(FILTER(A2:A100, (B2:B100="live") + (B2:B100="completed"))), which removes any duplicate entries before passing them to the validation list.

Excel & Data Problems