Chart & Visualization Issues

How to Create a Dynamic Excel Chart from Multiple Subcategories Without VBA

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to generate an interactive chart that dynamically displays up to 13 months of data for multiple subcategories based on a parent category selected from a drop-down list, without relying on VBA macros.

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Building a dynamic dashboard or report where selecting a main category automatically updates a chart to show all related subcategories over a 13-month period.
Observed behavior: Previous attempts using dynamic ranges, Pivot Tables, helper columns, and functions like FILTER, UNIQUE, SWITCH, OFFSET, and MATCH either resulted in incomplete visualizations or left blank spaces in the chart series.

Before you start

Ensure your raw 13-month dataset is formatted as an official Excel Table (Ctrl+T) so that any new categories or subcategories added later will automatically flow into your dynamic formulas.

Solution 1Recommended

Use FILTER Function Combined with Dynamic Named Ranges

By pairing Excel's modern FILTER dynamic array function to extract relevant data with Named Ranges to feed the chart, you can create a fully dynamic visual that automatically expands and ignores blanks.

Charts cannot directly accept spill range formulas (like A1#) in their series data fields. To bypass this limitation, you must first create Dynamic Named Ranges in the Name Manager that refer to your spilled array, and then link those Names to your chart.

Set up the drop-down selector

Click the cell where you want the selector, go to Data > Data Validation, choose 'List' from the Allow menu, and select a range containing your unique main categories.

Extract dynamic subcategories

In a helper area (e.g., cell F2), use the FILTER function to pull subcategories based on the drop-down cell: =FILTER(SubcategoriesRange, CategoriesRange=DropdownCell).

Extract the 13-month data

In the adjacent cell (e.g., G2), use the FILTER function again to extract the monthly data corresponding to those filtered subcategories: =FILTER(DataRange, CategoriesRange=DropdownCell).

Create Dynamic Named Ranges

Navigate to Formulas > Name Manager and click 'New'. Create a name called 'ChartLabels' and set 'Refers to' as your subcategory spill range (e.g., =Sheet1!$F$2#). Create another name called 'ChartData' pointing to the data spill range (e.g., =Sheet1!$G$2#).

Link Named Ranges to the Chart

Insert a blank chart, right-click it, and click 'Select Data'. Add a new series, and for the Series Values, type the sheet name followed by the defined name (e.g., =Sheet1!ChartData). Edit the Horizontal Axis Labels to use =Sheet1!ChartLabels.

Auto-scaling Chart: Using the # operator in your Named Ranges ensures the chart automatically expands or shrinks based on the number of subcategories, completely eliminating blank spaces.

Solution 2

Build a PivotChart Controlled by a Slicer

If managing complex array formulas and Named Ranges is causing blanks, a PivotChart connected to a Slicer offers a robust, no-code alternative to dynamically filter subcategories.

Smart Data Visualization

Create Dynamic Charts Easily with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like FILTER and UNIQUE, as well as robust PivotCharts. You can seamlessly build interactive dashboards and link drop-downs to dynamic charts without writing a single line of VBA.

1. Prepare your data: Open your dataset in WPS Spreadsheet and format it as a standard Table for dynamic expansion.
2. Add a Drop-down: Use Data > Validation to create your primary category drop-down selector.
3. Filter the Data: Apply the =FILTER() function to an empty grid area to extract relevant subcategories and data dynamically.
4. Define Name Ranges: Use the Name Manager (Formulas tab) to create named ranges targeting your filtered output with the # spill operator.
5. Insert and Link Chart: Insert your preferred Chart type, click 'Select Data', and assign your newly created Named Ranges as the chart's data source.

Fully compatible with Microsoft Excel formulas, Named Ranges, and chart formats.Natively supports dynamic array formulas, automatically managing spill behaviors.Intuitive Name Manager interface makes setting up dynamic chart series effortless.Free, lightweight, and handles large 13-month datasets without lag.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my dynamic chart show blank spaces when categories change?

Blanks occur when a chart's data range is statically fixed to a larger number of rows than the currently selected category requires. By using Dynamic Named Ranges combined with the spill operator (#), the chart is forced to read only the cells that actually contain data.

Can I use the FILTER function directly inside the chart data source?

No, Excel and WPS Spreadsheet charts do not currently accept dynamic array formulas (like FILTER) or spill operators directly in the Select Data series formula. You must wrap the spilled reference in a Named Range first, then insert that Name into the chart source.

How do I make a drop-down list update automatically when new categories are added?

Use the =UNIQUE(CategoryColumn) function in a blank cell to generate a distinct list of your categories. Then, point your Data Validation list source to that specific cell and add a hashtag at the end (e.g., =$Z$2#) so the drop-down list expands automatically as new categories appear.

Excel & Data Problems