Copy Excel Values into Every Sixth Row in Column B
Question details
Copy consecutive values from column C into every sixth row of column B, starting at B2.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants values from C1, C2, C3, and subsequent cells to appear in B2, B8, B14, and so on.
- Observed behavior
- The desired result is a value every six rows in column B, with the intervening rows displaying blank.
Check that the first source value is in C1 and that the destination range in column B is available. Filling the formula will replace any existing content in the selected destination cells.
Use an Excel formula to copy values at six-row intervals
Use this formula when consecutive source values start in C1 and the first destination is B2.
The MOD function identifies every sixth destination row, while INDEX retrieves the next value from column C. Intervening cells contain formulas that return an empty string rather than being truly empty.
Select B2 and enter =IF(MOD(ROW()-2,6)=0,INDEX($C:$C,1+INT((ROW()-2)/6)),""). Press Enter.
Select B2 and drag its fill handle down column B far enough to include all required destination rows.
Verify that B2 shows C1, B8 shows C2, and B14 shows C3. Extend the fill range if more source values need to be displayed.

Copy values into every sixth row with WPS Spreadsheets
Use the same IF, MOD, ROW, and INDEX formula in WPS Spreadsheets to display consecutive column C values at six-row intervals in column B.
- 1. Open your workbook: Open the spreadsheet in WPS Spreadsheets and locate the source values in column C.
- 2. Enter the formula: Select B2, enter =IF(MOD(ROW()-2,6)=0,INDEX($C:$C,1+INT((ROW()-2)/6)),""), and press Enter.
- 3. Fill down: Select B2 and drag the fill handle down column B through the last required destination row.
- 4. Verify and save: Check B2, B8, and B14 against C1, C2, and C3, then use File > Save to save the workbook.

Frequently Asked Questions
How do I change the spacing between copied values?
In the formula in B2, replace both occurrences of 6 with the desired interval, then fill the revised formula down column B.
What if my source values start in C2 instead of C1?
In B2, use =IF(MOD(ROW()-2,6)=0,INDEX($C:$C,2+INT((ROW()-2)/6)),"") and fill it down. B2 will show C2 and B8 will show C3.
Are the rows between the results truly blank?
No. The filled cells contain formulas that return an empty string, so they look blank but are not empty cells.
How can I keep the displayed results from changing?
Select the filled range in column B and copy it, then use Paste Special > Values to replace the formulas with their current results.




