How to Create a Mixed Reference Formula in Excel
Question details
The user needs to construct a formula that multiplies and adds cell values while keeping one specific row reference fixed as the formula is copied across multiple rows and columns.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a scalable formula for a large worksheet where a multiplier value in a specific row must remain constant while base values change dynamically per row and column.
- Observed behavior
- The correct result state requires the formula to successfully calculate the values using a mixed cell reference, locking row 6 but allowing columns to update automatically.
Ensure you are familiar with the difference between relative, absolute, and mixed cell references, as proper placement of the dollar sign ($) is crucial for formulas that will be dragged across multiple cells.
Use a Mixed Cell Reference to Lock a Specific Row
Apply a mixed reference by adding a dollar sign ($) before the row number to keep it constant while allowing the column to change dynamically as the formula is copied.
A mixed reference in a spreadsheet combines a relative and an absolute reference. By placing a dollar sign before the row number (e.g., B$6), you instruct the application to lock the row while keeping the column relative. This is highly effective when you need a multiplier to stay consistent for calculations across a structured table.
Click on cell B8, where you want the calculated result to appear.
Type the formula =B7*B$6+B7 into the formula bar. The $ symbol before the 6 ensures that row 6 remains fixed as you copy the formula down.
Press the Enter key to calculate and view the result in cell B8.
Click and drag the fill handle at the bottom-right corner of cell B8 down to B11. Then, with the range B8:B11 selected, drag the fill handle horizontally across to range C8:H11 to populate the entire table.

Effortlessly Manage Mixed Reference Formulas in WPS Spreadsheet
WPS Spreadsheet provides robust support for complex data calculations, including intuitive tools for managing absolute, mixed, and relative cell references to streamline your workflow.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document containing the data table.
- 2. Input the formula with mixed references: Select the starting cell and type your formula, inserting the $ symbol to lock the necessary row or column (e.g., =B7*B$6+B7).
- 3. Use the Fill Handle to expand: Drag the green square at the bottom right of the active cell to quickly copy the locked formula across all required rows and columns.

Frequently Asked Questions
What is the difference between an absolute and a mixed cell reference?
An absolute reference (e.g., $B$6) locks both the specific row and column, meaning it will never change when copied. A mixed reference (e.g., B$6 or $B6) locks only the row or only the column, allowing the unrestricted part to adapt dynamically based on where it is pasted.
Why does my copied formula return incorrect results or #VALUE! errors?
This usually happens because you are using relative cell references (e.g., B6) instead of mixed or absolute references. When you copy the formula down or across, the cell references shift, pointing to empty or incorrect cells. Adding a $ to lock the correct row or column will resolve this issue.
Can I lock a column instead of a row?
Yes. To lock a column instead of a row, place the dollar sign before the column letter (e.g., $B6). This ensures that as you drag the formula horizontally, the formula will always reference column B, but dragging it vertically will still update the row numbers.




