Chart & Visualization Issues

Fix an Excel Glucose Chart Range That Expands When Rows Are Inserted

Jason MillerJason Miller Nov 18, 2026 869 views

Question details

Keep an Excel glucose chart linked to the intended rows when rows are inserted.

Fix an Excel Glucose Chart Range That Expands When Rows Are Inserted
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user inserts rows in a worksheet containing glucose and Carbs charts and wants each chart to use the correct data for its quarter.
Observed behavior
The glucose chart's data range grows incorrectly, causing overlapping chart data.
Before you start

Identify the glucose values column, its corresponding date or category column, and the intended worksheet and quarter. The example below assumes dates are in column A, numeric glucose values are contiguous in column B, and the intended data window is rows 3 through 50.

Solution 1Recommended

Use bounded dynamic named ranges for the glucose chart

Use workbook-level named ranges to include populated glucose rows within a fixed row window and keep the chart labels aligned.

The example formulas require at least one numeric glucose value and no blank or nonnumeric cells between glucose values. They use row numbers rather than a fixed cell endpoint, so the window remains rows 3 through 50 after row insertions. Data pushed below row 50 is excluded.

1
Inspect the chart's current references

Select the glucose chart, then open Chart Design > Select Data. Check the glucose series and horizontal axis labels, and note the worksheet and quarter they should reference.

2
Define the glucose values range

Open Formulas > Name Manager > New. Set the name to GlucoseValues and the scope to Workbook. In Refers to, enter =INDEX(Sheet1!$B:$B,3):INDEX(Sheet1!$B:$B,2+COUNT(INDEX(Sheet1!$B:$B,3):INDEX(Sheet1!$B:$B,50))). Replace Sheet1 and column B with the correct worksheet and glucose column.

3
Define the matching label range

In Name Manager, select New again and create the workbook-level name GlucoseLabels. Enter =INDEX(Sheet1!$A:$A,3):INDEX(Sheet1!$A:$A,2+COUNT(INDEX(Sheet1!$B:$B,3):INDEX(Sheet1!$B:$B,50))). Adjust the worksheet and columns to match your labels and glucose values.

4
Assign the named ranges

Return to Chart Design > Select Data. Select the glucose series and choose Edit; replace Series values with ='Book1.xlsx'!GlucoseValues, using your workbook's actual file name. Under Horizontal (Category) Axis Labels, choose Edit and enter ='Book1.xlsx'!GlucoseLabels. Confirm both dialogs.

5
Check the chart

Review the glucose chart and the Carbs chart in Select Data to confirm they use the correct worksheet and quarter. Insert a test row and check that the glucose chart includes only the intended populated rows within rows 3 through 50.

Use bounded dynamic named ranges for the glucose chart
Adjust the window if needed: If the intended last row is not 50, change 50 in both formulas. If glucose values contain gaps or text, the COUNT-based formulas may not match the last populated row and need a different definition.
Edit spreadsheet charts in WPS Office

Adjust the glucose chart range in WPS Spreadsheets

Open the workbook in WPS Spreadsheets and edit the glucose chart's series and labels to point to the intended worksheet cells. Recheck the range after inserting rows.

  1. 1. Open the workbook: Launch WPS Spreadsheets and use Open to select the Excel workbook containing the glucose chart.
  2. 2. Open the chart data settings: Select the glucose chart, then use Select Data on the chart tools ribbon or in the chart's right-click menu.
  3. 3. Correct the series and labels: Edit the glucose series values and category labels to select the intended worksheet and row range, such as rows 3 through 50.
  4. 4. Verify and save: Confirm the chart displays the expected data, then use File > Save As to save an XLSX copy. Recheck the chart range after inserting rows.
Edit glucose chart values and labels in one spreadsheetOpen and save Microsoft Excel XLSX workbooksUse a familiar spreadsheet interfaceAccess WPS Office's free editing tools
microsoft office alternative - wps office

Frequently Asked Questions

Why does my glucose chart include extra rows after I insert data?

Excel can adjust chart references when rows are inserted. Select the chart and open Chart Design > Select Data to see which cells the glucose series and axis labels currently reference.

How can I prevent the dynamic range from including rows below row 50?

Define the range with INDEX formulas that use row 50 as a numeric limit, as shown above. Values moved below that row will not appear in the chart.

What if the glucose column contains blank cells?

The example uses COUNT and assumes contiguous numeric values. If there are gaps or text, it may end the range too early; use a range definition suited to the data layout or remove the gaps before applying it.

How do I check that the chart uses the correct quarter?

Open Chart Design > Select Data and inspect the series and axis references. In Formulas > Name Manager, also check that GlucoseValues and GlucoseLabels point to the worksheet and columns for the intended quarter.