Filter Excel Chart Series with Sliders, Checkboxes, and Formulas in WPS Office
Question details
The user wants to show or hide stacked-bar chart series and value ranges using interactive controls such as sliders and checkboxes, while preventing source-sheet filters from changing the displayed chart results.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating a dynamic stacked-bar chart where series visibility is controlled by sliders or checkboxes while keeping the chart independent from source data filters.
- Observed behavior
- The chart should show or hide series and value ranges based on the controls without being affected by filtered source rows.
Prepare a separate chart-data area that references your original source range. Keep the slider cell, such as T4, and the checkbox value cells available because the formulas in this area use their values to return either data or NA().
Use a Separate Chart-Data Area with IF and NA() Formulas
Use this method when you want slider or checkbox changes to show or hide chart series reliably without changing the original source range.
Keep the chart pointed to a dedicated chart-data area rather than directly to the filtered source rows. Formulas returning NA() prevent unwanted chart points from appearing, while the original data remains intact.
Open WPS Office Spreadsheets, select a blank area next to your source data, and create the same column headers for the chart series you want to control.
In the first chart-data cell for a series, enter a formula such as =IF($T$4>=2,G6,NA()) so the series appears only when the slider value meets the condition.
Assign each checkbox a power-of-two value, sum the selected values in a cell, and use =IF(BITAND($B$4,1),G6,NA()) to test that series bit.
Select the chart, click Chart Tools, choose Select Data, and set the series values to the chart-data area columns instead of the original source range.
Prevent Source Filters from Altering Chart Results by Using an Independent Data Range
Use this method when a chart currently references filtered source rows and changes unexpectedly.
Use WPS Office Spreadsheets to Build a Dynamic Chart-Data Area
In WPS Office Spreadsheets, create a dedicated chart-data range that uses IF, NA(), and BITAND formulas to show or hide chart series from sliders and checkboxes. This keeps your original data untouched and makes the chart independent from source filters.
- 1. Prepare the control cells: In WPS Office Spreadsheets, enter a slider-linked value in a cell such as T4 and assign a power-of-two value to each checkbox control.
- 2. Build the chart-data area: Open a blank range and enter formulas like =IF($T$4>=2,G6,NA()) or =IF(BITAND($B$4,1),G6,NA()) to control each series.
- 3. Update the chart series: Select the chart, choose Chart Tools > Select Data, and set each series to the new chart-data area.
- 4. Test the controls: Move the slider or toggle the checkboxes and confirm that the chart shows or hides the intended series while source filters remain unchanged.
Frequently Asked Questions
Why does my Excel chart still change when I filter the source sheet?
Because the chart is likely reading the original filtered range. Point the chart to a separate chart-data area whose formulas return NA() instead of reading the filtered source rows.
How can I use NA() in a chart formula?
Use a formula such as =IF($T$4>=2,G6,NA()). When the condition is false, the cell contains NA(), and the chart omits that point instead of plotting zero.
How do checkboxes control chart series with BITAND?
Assign each checkbox a power-of-two value such as 1, 2, 4, 8, sum the selected values, then test each series bit with a formula like =IF(BITAND($B$4,1),G6,NA()).
Can I hide a value range on a stacked-bar chart without deleting data?
Yes. Create chart-data columns that return values only when the selected range condition is true, and otherwise return NA().
Does this method work when I open the file in Microsoft Excel?
Yes. The formulas IF, NA(), and BITAND and the chart data range approach are compatible with XLSX files in WPS Office and Microsoft Excel.
