WPS Spreadsheet: Copy a Formula Horizontally to Reference Every Other Cell in Column A
Question details
Create a formula that can be copied across row 1 to reference successive cells in column A while leaving the intervening cells blank.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Values are listed vertically in column A, and the user wants to display them horizontally in every other column starting at D1.
- Observed behavior
- The target cells need to show A1 in D1, A2 in F1, A3 in H1, and subsequent values in alternating columns, with blank cells between them.
Enter the source values consecutively in column A, starting at A1. Make sure D1 and the cells to its right are empty so the copied formula can populate the alternating columns.
Use an alternating-column formula and copy it across row 1
Use this solution when you need a traditional formula that places successive column A values in D1, F1, H1, and later even-numbered columns.
The relative column reference in COLUMN(B$2) changes as the formula is copied horizontally, while the row reference remains fixed. ISEVEN identifies the columns that should display values, and INDEX retrieves the corresponding item from column A.
Open the worksheet in WPS Spreadsheet and select cell D1.
Type =IF(ISEVEN(COLUMN()),INDEX($A:$A,QUOTIENT(COLUMN(B$2),2)),"") in the formula bar or directly in D1, then press Enter.
Select D1, use the fill handle at the lower-right corner of the cell, and drag it to the right across the required range.
Confirm that D1 shows the value from A1, F1 shows the value from A2, H1 shows the value from A3, and the cells between them remain blank.
Use a COLUMNS-based formula for a clearer alternating pattern
Use this alternative when you prefer the formula to calculate the source row from the number of columns copied from D1.
Build alternating-column references in WPS Spreadsheet
WPS Spreadsheet lets you enter an INDEX-based formula once and fill it across the row to display successive values from column A in every other column. It also preserves familiar formulas and supports Microsoft Excel workbook formats for easy file sharing.
- 1. Prepare the source list: Open WPS Spreadsheet and place the source values in A1, A2, A3, and the following cells in column A.
- 2. Enter the formula in D1: Select D1 and enter =IF(ISEVEN(COLUMN()),INDEX($A:$A,QUOTIENT(COLUMN(B$2),2)),"").
- 3. Fill the formula right: Select D1 and drag its fill handle across row 1 to the required ending column.
- 4. Review the alternating output: Verify that values appear in D1, F1, H1, and later alternating columns, while the cells between them remain blank.
Frequently Asked Questions
Why does the formula begin in D1 instead of C1?
D1 is the first target column in the requested pattern. Because D is an even-numbered column, ISEVEN(COLUMN()) returns TRUE there, while the intervening odd-numbered columns return blank.
What does COLUMN(B$2) do when the formula is copied across?
The row number 2 is fixed, but the column reference is relative. As the formula moves right, B changes to C, D, E, and so on, allowing QUOTIENT to advance the INDEX row from A1 to A2, A3, and later values.
Can I start the pattern in another column?
Yes. Change the starting cell and adjust the formula's relative counter reference so the first target column produces row 1 from column A. Test the first two populated positions before filling across the entire range.
How can I keep blank source cells from displaying zeros?
Wrap the INDEX result in an additional check, such as =IF(ISEVEN(COLUMN()),IF(INDEX($A:$A,QUOTIENT(COLUMN(B$2),2))="","",INDEX($A:$A,QUOTIENT(COLUMN(B$2),2))),""). This returns an empty string when the referenced source cell is blank.
