Excel FILTER Function for Dependent Drop-Down Lists

A drop-down list can show the vegetables related to a selected category, but the current layout makes the task harder than it needs to be. The most reliable fix is to separate the data from its visual presentation, remove merged cells from the working range, and then use either a PivotTable or a Microsoft 365 dynamic array formula.

The problem

This problem came up in the r/excel community discussion. The worksheet uses a yellow category heading and a veggie category heading merged across several columns. The goal is to choose a vegetable from a drop-down menu and show only the corresponding data while hiding unrelated values.

The difficulty is not mainly the data validation drop-down. Data validation controls what a user can select. It does not automatically reshape a report or hide unrelated columns.

Merged cells are the larger issue. A merged heading may look clean, but it does not behave like a normal value repeated across each record. That creates problems when Excel tries to sort, filter, convert the range into a table, or use formulas that expect one row per item. Microsoft documents that Excel cannot sort a column containing merged cells.

The best structure is a normal data table with one record per row. Store the category in its own column, store the vegetable in another column, and keep the related measurements or attributes in additional columns. Use formatting on a separate report area if you want the worksheet to retain its visual design.

The solution

For a working file, the most robust approach is:

• Unmerge the category and vegetable headings in the source data.
• Repeat the category value on every applicable row.
• Convert the cleaned range into an Excel table.
• Use a drop-down cell for the selected category or vegetable.
• Display the matching records in a separate report area.

If the workbook uses Microsoft 365, a dynamic array formula can produce the filtered report automatically. A dynamic array is a formula that returns multiple cells from one formula.

Assume:

• B8:B19 contains a field such as product, code, or item.
• C8:J19 contains the data to display.
• Row 8 contains category labels that may be visually grouped.
• N6 contains the selected category.
• Blank cells in row 8 mean that the previous category continues across the columns.

A formula based on the community answer is:

=LET(_Output,HSTACK(B8:B19,FILTER(C8:J19,SCAN("",C8:J8,LAMBDA(x,y,IF(y="",x,y)))=N6,"")),_Header,TAKE(_Output,2),_Answer,VSTACK(IF(_Header=0,"",_Header),DROP(_Output,2)),_Answer)

LET (LET) assigns names to parts of the formula. _Output combines the item column with the filtered data. HSTACK places ranges side by side. SCAN carries the last nonblank category across the header row. LAMBDA defines the calculation used by SCAN. FILTER returns only the columns whose carried category matches N6. TAKE keeps the first two rows, and DROP removes them from the remaining data. VSTACK puts the cleaned header and filtered data back together.

This formula preserves the visual logic of the original layout without depending on merged cells. It also avoids OFFSET, a volatile function. Volatile functions recalculate whenever Excel recalculates the workbook, even when their direct inputs did not change. In a large file, excessive use can make the workbook slower.

A simpler alternative is to normalize the data first. Add a real Category column, then use a formula such as:

=FILTER(Table1,Table1[Category]=N6,"No matching records")

Table1[Category] refers to the Category column in the Excel table. The formula returns every row whose category matches the selection in N6. This approach is easier to maintain than a formula that reconstructs categories from merged headings.

A PivotTable is another strong option, especially when the user mainly needs to browse or summarize the information. PivotTables can filter fields, and slicers provide visible buttons for selecting categories. Microsoft explains that slicers can filter a PivotTable while keeping the available filter choices visible.

How to apply it

1. Make a backup copy of the workbook before changing the layout.

2. Identify the actual data range. Include the item name, category, vegetable, and every value that belongs to that record.

3. Unmerge the cells in the working range. Select the merged area, open the Merge menu, and choose to unmerge the cells.

4. Create a proper category column. If one yellow category covers several columns, enter that category in every relevant row or column record.

5. Avoid leaving category information only in formatting or merged headings. A formula cannot reliably use a color or visual grouping as business data.

6. Put each vegetable or product on its own row. Use columns such as Category, Vegetable, Unit, Cost, Stock, and Price.

7. Select the cleaned range and choose Insert, then Table. Confirm that the table has headers.

8. Create a selection cell, such as N6. Use Data Validation, choose List, and point it to a clean list of categories or vegetables.

9. If the table has a real Category column, place the simple FILTER formula in a separate report area. Replace Table1 and Category with the actual table and column names.

10. If the worksheet must retain its existing cross-column layout, use the LET, HSTACK, SCAN, FILTER, and VSTACK formula instead. Test it with several category selections.

11. If the report is mainly for review, select the normalized table and choose Insert, then PivotTable. Add the category or vegetable field as a filter, row field, or slicer.

12. Test blank categories, new vegetables, spelling differences, and empty results. Use a consistent naming convention, such as Tomato everywhere instead of mixing Tomato, tomatoes, and Tomate.

Center Across Selection can preserve a heading that visually spans multiple cells without merging them. It is useful for presentation, but it does not replace a real category column. Keep the source data normalized and apply presentation formatting only in the report area.

Implementation angle

For a small business, this structure can become a controlled inventory or quotation workflow. An automation partner could move the normalized vegetable data into Zoho Creator, connect stock and price updates through Make or n8n, and keep Excel as an export or reporting layer. Claude could help generate and check formulas, while validation rules would prevent inconsistent category and product names from entering the workbook.

Book an AI implementation engagement with Creator Scripts at creatorscripts.com.

Frequently asked questions

How do I make an Excel drop-down show only matching vegetables?

Keep the source data in a normal table with a category column and a vegetable column. Use a drop-down for the selected category, then use a FILTER formula in a separate report area to show matching rows.

Can I filter Excel data that has merged cells?

Merged cells can cause problems with sorting, filtering, and formulas. Unmerge them in the working data, repeat the category value on each applicable row, and use formatting in a separate report area.

What can I use instead of a FILTER formula in Excel?

A PivotTable can filter records by category or vegetable. Add a slicer if you want visible buttons for choosing a category.