How to Reference A1, B1, C1 When Dragging a Formula Down in WPS Spreadsheets
Question details
The user needs to fill a formula down so it returns values from a horizontal source range such as A1, B1, C1 in sequence instead of locking onto one cell.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is dragging a formula down in a spreadsheet and wants the references to move across columns in the source row.
- Observed behavior
- A reference such as A$1 keeps row 1 locked and does not move across columns when the formula is filled down, so it does not return A1, B1, C1 sequentially.
Open the worksheet and identify the horizontal source row and the first cell where the vertical results should begin. Check whether your WPS Spreadsheets version supports dynamic array functions such as TRANSPOSE and TOCOL before choosing a formula.
Use INDEX with ROW(A1) to Return Row Values When Filling Down
Best for all WPS Spreadsheets versions; enter one formula and drag it down to retrieve A1, B1, C1 in order.
A reference like A$1 locks only row 1, so it cannot move from A1 to B1 to C1 when dragged down. INDEX provides a column counter that increases with the ROW function, making the formula repeatable.
Click the cell where you want the first returned value to appear, for example A2 or D1.
Type =INDEX($1:$1,,ROW(A1)) and press Enter. This returns the first value from row 1.
Drag the fill handle down the column. The ROW(A1) counter changes to ROW(A2), ROW(A3), and so on, returning B1, C1, and the following row 1 values.
Use TRANSPOSE to Convert a Horizontal Row to a Vertical Spill Range
Use this when your WPS Spreadsheets build supports dynamic arrays and you want one formula to return the full row automatically.
Use TOCOL to Place Row 1 Values into a Single Column
Use this if your version supports the TOCOL function and you need to ignore blanks or preserve the row order automatically.
Return A1, B1, C1 in Order When Dragging Down in WPS Spreadsheets
Use WPS Spreadsheets to replace fixed row references with INDEX, TRANSPOSE, or TOCOL so your formula returns A1, B1, C1 in order as you fill down.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the .xlsx or .xls file that contains the row values you want to reference.
- 2. Enter the recommended formula: Click the first output cell and type =INDEX($1:$1,,ROW(A1)) to return the first row value.
- 3. Fill the formula down: Drag the fill handle down so WPS Spreadsheets updates the ROW counter and returns A1, B1, C1 sequentially.
Frequently Asked Questions
Why does A$1 not change to B1 when I drag the formula down?
In WPS Spreadsheets and Excel, the $ before the row number locks row 1, but the column is still relative. Dragging down changes the row position, not the column, so A$1 stays in column A. Use =INDEX($1:$1,,ROW(A1)) or a dynamic array function to advance across columns.
Can I use =INDEX($1:$1,,ROW(A1)) in WPS Spreadsheets?
Yes. Enter the formula in the first output cell and drag it down. The ROW(A1) part returns 1, then 2, then 3, so INDEX returns A1, B1, C1 in sequence.
What is the TRANSPOSE formula for converting A1:C1 to a vertical list?
In a supported dynamic-array version, enter =TRANSPOSE(A1:C1). If your version requires legacy array entry, select the destination range first and press Ctrl+Shift+Enter.
What is the difference between TRANSPOSE and TOCOL?
TRANSPOSE swaps the orientation of an entire array, while TOCOL returns all values from a range into one column and can optionally skip blanks or scan by row.
Does WPS Spreadsheets support TOCOL?
TOCOL is available in newer WPS Spreadsheets versions that support dynamic arrays. If your version does not support it, use the INDEX formula =INDEX($1:$1,,ROW(A1)) instead.
