Function Problems

Use Cell Values as Dynamic Row Numbers in Excel Formulas and Charts

Elise WilliamsElise Williams Nov 20, 2026 869 views

Question details

Use row numbers stored in cells to define dynamic ranges for formulas and charts across sections of different sizes.

Use Cell Values as Dynamic Row Numbers in Excel Formulas and Charts
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the row boundaries

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.

2
Create the formula

Select the result cell and enter =AVERAGE(INDIRECT("F"&X14&":F"&Y14)) to average the corresponding rows in column F.

3
Add a condition if needed

To average nonzero values in column N over the same row boundaries, enter =AVERAGEIF(INDIRECT("N"&X14&":N"&Y14),"<>0") in a result cell.

4
Copy the formula to other sections

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.

Build a dynamic formula range with INDIRECT
Check copied references: Excel adjusts relative references by the distance between the copied and pasted cells. If a formula is placed in a different relative position, edit its X and Y references. INDIRECT recalculates frequently and may slow large workbooks.
Dynamic spreadsheet ranges

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 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. 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. 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.
Calculate ranges using row numbers stored in cellsUse AVERAGE and AVERAGEIF with dynamic rangesOpen and edit Microsoft Excel XLSX filesWork with a familiar spreadsheet interface
microsoft office alternative - wps office

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.