Function Problems

Fill an Excel Formula Down While Referencing Values Across a Row

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 868 views

Question details

The user wants a vertical list that references successive cells across a horizontal row.

Fill an Excel Formula Down While Referencing Values Across a Row
Product
Microsoft Excel
Device & OS
not provided
Scenario
Values in a row need to appear down a column without manually editing each cell reference.
Observed behavior
A normal relative reference moves down the source column when its formula is filled downward, rather than moving across the source row.
Before you start

Identify the source row and choose an empty destination column with enough space for the results. The examples below use I3:K3 as the source range.

Solution 1Recommended

Transpose the source row into a vertical list

Use TRANSPOSE when you want one formula to return the entire row vertically without dragging a fill handle.

1
Select the destination

Click the first empty cell where the vertical list should appear, such as A5.

2
Enter the formula

Type =TRANSPOSE(I3:K3) in the formula bar and press Enter. Change I3:K3 to your source range.

3
Check the result

Confirm that the values from I3, J3, and K3 appear vertically. Keep the cells below the formula empty so the results can spill.

Transpose the source row into a vertical list
Older Excel versions: If your Excel version does not support dynamic arrays, select a vertical destination range with the same number of cells as the source, enter the formula, and press Ctrl+Shift+Enter.
Work with formulas in WPS Spreadsheets

Reference a Row While Filling Down in WPS Spreadsheets

Use INDEX and ROWS in WPS Spreadsheets to return successive values from a horizontal range as you fill a formula down.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your spreadsheet file.
  2. 2. Choose a destination: Click an empty cell where the vertical results should begin.
  3. 3. Enter the formula: Type =INDEX($I$3:$K$3,1,ROWS($A$1:A1)) and press Enter. Replace $I$3:$K$3 with your source row range.
  4. 4. Fill the formula down: Drag the fill handle down for as many rows as there are cells in the source range.
Fill a column while referencing values across a rowCompatible with Microsoft Excel XLS and XLSX formatsUse familiar spreadsheet formulas and fill-handle controlsFree and easy to use
microsoft office alternative - wps office

Frequently Asked Questions

Why does a normal cell reference move down instead of across?

When you drag a relative reference down, Excel adjusts its row number. Use =INDEX($I$3:$K$3,1,ROWS($A$1:A1)) and fill down to advance across the source range instead.

Will the vertical results update if the source values change?

Yes. Both TRANSPOSE and INDEX reference the source cells, so the results update when those values change. Expand the source range in the formula if you add cells beyond it.

Why does my TRANSPOSE formula show a spill error?

Check the cells below the formula and clear any content blocking the intended output range. Then confirm that the formula cell has enough empty space for every source value.

Can I use COUNT to determine the width of a dynamic source range?

COUNT counts numeric cells only, so it can produce an incorrect width if the row contains text or gaps. Use a known source range, or choose a method that accounts for the actual types and positions of values in your row.