How to Autofill Excel Formulas with Merged and Unmerged Cells in WPS Spreadsheets
Question details
The user needs to fill a multiplication formula down a column in WPS Spreadsheets when one referenced column contains a repeating 3-row merged-cell pattern and the other column contains individual cells.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a table where column C contains merged cells grouped in a 3-row pattern and column D has one value per row. They want to multiply columns C and D in column E for each row.
- Observed behavior
- A standard formula dragged down does not correctly reference the merged cell values because merged cells only store a value in the upper-left cell, and regular fill references shift row by row.
Confirm that column C uses a consistent 3-row merged-cell pattern and that your first output cell is E4. If the merge pattern differs, adjust the formula’s MOD divisor and IF limits accordingly.
Use OFFSET with MOD and ROWS to Autofill a Formula That Respects Merged Cells
Use this formula when column C has a consistent 3-row merged-cell pattern and column D has one row per record. It returns the correct merged value for every row without unmerging the sheet.
The OFFSET function looks one column to the left and adjusts the row upward based on the current row’s position inside each 3-row group. Because merged cells store values only in the top-left cell, this formula points each row within a group to the correct merged C value.
Click cell E4 in WPS Spreadsheets, where the first multiplication result should appear.
Type =D4*OFFSET(D4,-IF(MOD(ROWS(C$3:C3),3),MOD(ROWS(C$3:C3),3)-1,2),-1) and press Enter.
Select E4, then drag the fill handle down through all rows that need calculation. WPS updates row references while the OFFSET logic keeps the correct merged C value.
Unmerge Column C and Repeat the Value Before Using a Simple Formula
Use this alternative if you prefer a simpler formula, if the merge pattern is irregular, or if you can restructure the sheet.
Autofill Formulas with Merged and Unmerged Cells in WPS Spreadsheets
Use WPS Spreadsheets to fix formula fill behavior when a source column has merged cells. Enter an OFFSET formula with MOD and ROWS, or unmerge and repeat values, then use the fill handle to calculate the entire column accurately.
- 1. Open your workbook in WPS Spreadsheets: Open the workbook and select cell E4, where the first calculation result should appear.
- 2. Enter the formula: Type =D4*OFFSET(D4,-IF(MOD(ROWS(C$3:C3),3),MOD(ROWS(C$3:C3),3)-1,2),-1) and press Enter.
- 3. Fill down the result column: Drag the fill handle from E4 down through the remaining rows to apply the formula to the whole column.
Frequently Asked Questions
Why does a normal formula return 0 or the wrong value when I reference merged cells?
In WPS Spreadsheets and Excel, a merged cell keeps its value only in the upper-left cell; the other cells in the merged range are blank. A standard row-by-row fill can therefore reference blank cells, so you need a row-offset formula such as OFFSET with MOD and ROWS.
Does this OFFSET formula work if the merged cells are not in groups of 3?
No. The sample formula is built for a repeating 3-row pattern. If your merge pattern is different, adjust the MOD divisor and the IF limits to match the number of rows per group.
Can I avoid the complex formula by unmerging the cells?
Yes. Unmerge the cells in column C, use Home > Find & Select > Go To Special > Blanks to fill empty cells with the value from above, and then use the simple formula =C4*D4 and fill down.
Will this formula work in Microsoft Excel as well?
Yes. The OFFSET, MOD, and ROWS functions are supported in WPS Spreadsheets and Microsoft Excel, so the same formula works when you open the workbook in either application.
How do I fill the formula down without dragging in WPS Spreadsheets?
After entering the formula in E4, select the range you want to fill and press Ctrl+D, or double-click the fill handle when adjacent cells in column D have data.
