How to Create a Dynamic Excel Chart Based on Start and End Ages in WPS Office
Question details
The user needs a WPS Office chart that displays every age between two selected start and end ages and updates automatically when those values change.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has an age-based data series and wants to set two parameter cells for the start age and end age so the chart range changes automatically.
- Observed behavior
- The chart keeps the original fixed range and stops at the original number of rows instead of expanding or shrinking after the start and end ages change.
Make sure the age data is in one continuous column and the value data is in an adjacent column. Identify the two parameter cells that will hold the start age and end age before creating the named ranges.
Use Dynamic Named Ranges with OFFSET and MATCH
Use this method when you want the chart to automatically expand or shrink based on the Start Age and End Age cells without changing your data layout.
Named ranges created with OFFSET and MATCH calculate the exact row span between the start and end age cells. When you point the chart series to these names, WPS Office re-reads the range every time the chart recalculates.
Click the Formulas tab, then click Name Manager and select New to create the first dynamic range.
Enter the name XAxis and in the Refers to box type =OFFSET(Sheet1!$A$2,MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)-2,0,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)-MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)+1,1), then click OK.
Repeat the previous step and enter the name YAxis with the formula =OFFSET(Sheet1!$B$2,MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)-2,0,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)-MATCH(Sheet1!$E$1,Sheet1!$A:$A,0)+1,1), then click OK.
Select the chart, right-click it, choose Select Data, edit the series values to Sheet1!YAxis, and edit the axis label range to Sheet1!XAxis.
Use an Excel Table for an Automatically Updating Chart Source
Use this when your chart should automatically include newly added rows and you do not need to filter between two age parameters.
Use a PivotChart to Filter by Age Range
Use this when you want an interactive chart that can show any age segment without manual formulas.
Create a Dynamic Age Range Chart in WPS Office Spreadsheet
Define dynamic named ranges with OFFSET and MATCH, then assign them to your chart series so the chart expands or shrinks automatically whenever the Start Age or End Age cells change.
- 1. Open your workbook in WPS Office Spreadsheet: Open the workbook that contains the age data, value data, and the two parameter cells for start age and end age.
- 2. Create the dynamic XAxis and YAxis names: Go to the Formulas tab, open Name Manager, and create XAxis and YAxis using OFFSET and MATCH formulas that reference the start and end age cells.
- 3. Assign the names to the chart series: Select the chart, right-click, choose Select Data, and replace the series values with Sheet1!YAxis and the axis labels with Sheet1!XAxis.
- 4. Test the dynamic range: Change the Start Age or End Age cell and verify that the chart automatically updates to display only the selected age span.
Frequently Asked Questions
Why does my chart stop at the original number of rows when I change the start and end ages?
Your chart series is still referencing a fixed range. Replace that fixed range with dynamic named ranges using OFFSET and MATCH, or use a PivotChart, so the chart recalculates the selected age span automatically.
How do I tell the chart to use E1 and E2 as start and end parameters?
The parameter cells do not appear directly in the Select Data dialog. They are used inside the named range formulas. After creating XAxis and YAxis with MATCH and OFFSET references to E1 and E2, assign those names to the chart series values and axis labels.
Can I use a PivotChart instead of writing OFFSET and MATCH formulas?
Yes. Select the data range, go to the Insert tab, and choose PivotChart. Put Age in the Axis area and the value field in the Values area, then filter or use a slicer to show only the ages between your start and end values.
Do these dynamic named ranges work if the start and end age cells are on another sheet?
Yes. Update the sheet name in the OFFSET and MATCH formulas to point to the worksheet that contains the age data and the worksheet that contains the start and end age cells, keeping the workbook references consistent.
