Use Cell Values as Dynamic Row Numbers in Excel Formulas and Charts
Question details
Use row numbers stored in cells to define dynamic ranges for formulas and charts across sections of different sizes.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains sections with different numbers of rows. Each section has start and end row numbers in columns X and Y, and clerks need to copy and paste data without rewriting every formula and chart.
- Observed behavior
- Formulas and charts should use each section’s start and end row cells to select the relevant data range.
Identify the cells containing each section’s starting and ending row numbers, such as X14 and Y14, and the column containing the data. Make sure the row-number cells contain valid row numbers for the intended range.
Build a dynamic formula range with INDIRECT
Use INDIRECT to turn the row numbers in each section into a range that functions such as AVERAGE and AVERAGEIF can calculate.
In the section’s reference cells, enter the first data row in X14 and the last data row in Y14. For example, enter 15 and 30 to select rows 15 through 30.
Select the result cell and enter =AVERAGE(INDIRECT("F"&X14&":F"&Y14)) to average the corresponding rows in column F.
To average nonzero values in column N over the same row boundaries, enter =AVERAGEIF(INDIRECT("N"&X14&":N"&Y14),"<>0") in a result cell.
Copy and paste the formula into the same relative position in another section. Check the formula bar to confirm that its relative references point to that section’s X and Y cells, such as X57 and Y57.

Use a named dynamic range for an Excel chart
Create a named range based on a section’s row-number cells, then use that name as a chart series source.
Build dynamic row-based formulas in WPS Spreadsheets
Use INDIRECT in WPS Spreadsheets to construct a range from start and end row numbers, then copy the formula to other sections and check the adjusted references.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Enter the row numbers: Enter the first and last data row numbers in the section’s reference cells, such as X14 and Y14.
- 3. Enter the formula: Select a result cell and enter =AVERAGE(INDIRECT("F"&X14&":F"&Y14)) to average the selected rows in column F.
- 4. Copy and verify: Copy the formula to the same relative position in another section, then check the formula bar to ensure its X and Y references match that section.

Frequently Asked Questions
How do I exclude zeros from a dynamic average?
Use AVERAGEIF with INDIRECT. For example, =AVERAGEIF(INDIRECT("N"&X14&":N"&Y14),"<>0") averages nonzero values in column N between the rows specified in X14 and Y14.
Will a copied formula automatically use the next section’s row cells?
Excel adjusts relative references according to the distance between the source and destination cells. Paste the formula in the same relative position within the next section, then check the formula bar for the correct X and Y references.
Can an Excel chart use row numbers from cells?
Yes. Create a workbook-scope name under Formulas > Name Manager with an INDIRECT formula, then use that name in the chart’s Series values through Chart Design > Select Data.
Why might INDIRECT return a reference error?
Check that the row-number cells contain valid row numbers and that the constructed column and sheet references exist. Select the formula cell and inspect the references in the formula bar.




