How to Copy Excel Formulas Across Rows and Columns with Mixed References in WPS Office
Question details
The user needs to fill an IF formula across rows and columns in a spreadsheet by using mixed cell references so that only the intended row or column parts adjust automatically.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a worksheet with an IF formula that must be copied across multiple rows and columns without manual editing of each cell reference.
- Observed behavior
- The user wants the formula to evaluate correctly across the filled range by locking the required row or column references.
Before writing the formula, identify which row and which column must stay fixed and confirm that the source data range is ready. Open the workbook in WPS Office Spreadsheets and make sure the formula starts in the correct top-left cell.
Use Mixed Cell References in an IF Formula
Use this solution when you need to fill one IF formula across rows and columns and want only the intended part of each reference to change.
Mixed references lock either the row or the column in a cell reference by placing a dollar sign before the part that must stay fixed. In the formula =IF(AND(E$3>=$C4,E$3<=$D4),1,0), E$3 always refers to row 3 while the column changes, and $C4/$D4 always refer to columns C and D while the row changes.
Click the cell where the formula should begin, such as E4 or F4.
Type or paste =IF(AND(E$3>=$C4,E$3<=$D4),1,0) in the Formula Bar and press Enter.
Position the mouse over the fill handle at the bottom-right corner of the cell, then drag right across the required columns and down through the required rows.
Return 1 or 0 with the --AND Formula Alternative
Use this shorter alternative when you only need a numeric 1 or 0 result and want a compact formula.
Build Mixed-Reference Formulas in WPS Office Spreadsheets
WPS Spreadsheets supports the same mixed, absolute, and relative cell references as Excel. You can enter the IF formula, drag the fill handle to fill it across rows and columns, and save the workbook as an .xlsx file without losing formula behavior.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the workbook, and select the cell where the formula should begin.
- 2. Enter the mixed-reference formula: Type =IF(AND(E$3>=$C4,E$3<=$D4),1,0) in the Formula Bar and press Enter.
- 3. Fill the formula across rows and columns: Drag the fill handle from the bottom-right corner of the cell across the required columns and down the required rows.
- 4. Verify the filled results: Check the filled cells to confirm that the mixed references update correctly and return the expected 1 or 0 values.
Frequently Asked Questions
What is the difference between absolute, relative, and mixed references in Excel?
Relative references change when copied, absolute references stay fixed, and mixed references lock only the row or only the column. For example, E$3 locks row 3 but allows the column to change, while $C4 locks column C but allows the row to change.
Why does my formula change incorrectly when copying down or across?
The formula likely uses relative references where mixed references are needed. Check which row or column must stay fixed and add a dollar sign before that part, such as E$3 for a fixed row or $C4 for a fixed column.
Can I use the same mixed-reference formula in WPS Spreadsheets?
Yes. WPS Spreadsheets supports the same formula syntax. Open the workbook, enter =IF(AND(E$3>=$C4,E$3<=$D4),1,0), and use the fill handle to copy it across rows and columns.
How do I fill a formula across a large range without dragging?
Enter the formula in the first cell, copy the cell, select the target range, and paste. You can also select the range first, type the formula in the active cell, and press Ctrl+Enter to fill the selected cells.
