Copy Formulas with the Right References in WPS Spreadsheet

Quick answer

Use $A2 to lock a price column, B$1 to lock a quantity row and $F$2 to lock a single rate. Copy the formula cell, then inspect the far-corner formula and result.

English WPS Spreadsheets for Windows desktop; documentation-qualified basic local-workbook workflow, reviewed 20 October 2026. Installed build, region and free-edition execution are not verified; menu labels may differ. No web, mobile, macOS or Linux parity is claimed.

A copied formula should change only the references you intend. Build a price-by-quantity grid and a separate line-total calculation, then inspect the formulas at the far corners rather than assuming a plausible-looking result is correct.

Before you begin

Create WPS-References-Final.xlsx with a sheet named Model. This is a basic local formula workflow. The WPS-specific Academy lesson explains relative, absolute and mixed references and fill behavior. No premium feature, named range, dynamic array or external-workbook link is needed. Exact build and regional free-edition availability have not been tested; type the dollar signs directly rather than relying on a keyboard shortcut that may belong to the operating system.

1. Enter the inputs

Use these exact cells on Model:

B1:D1 are quantities, A2:A4 are prices, and F2 is a ten-percent surcharge. Enter F2 as the number 0.10, not the text 10% surcharge. You may format it as Percentage afterward. Save the input workbook.

2. See a relative reference move down

In J2 type:

=H2*I2

Press Enter; the result is 20. Copy J2 to J3:J4 using copy/paste or the fill handle. Inspect the formula bar for each cell: J3 must contain =H3*I3 and J4 =H4*I4, with values 48 and 100. Both column references remain the same because the copy moved vertically; row numbers advanced by one and two.

Copying a cell is different from copying its literal formula text while editing. Paste the cell, not a string from the formula bar, when you want references to adjust. Moving/cutting cells is also a different operation; this exercise uses copies.

3. Build a two-dimensional grid

In B2 enter:

=$A2*B$1*(1+$F$2)

The components have separate jobs:

• $A2 always takes the price from column A, but follows the destination row.
• B$1 always takes the quantity from row 1, but follows the destination column.
• $F$2 keeps both row and column fixed on the surcharge rate.

B2 should show 11. Copy B2 across to C2:D2. Check C2’s formula is =$A2*C$1*(1+$F$2) and D2’s formula is =$A2*D$1*(1+$F$2). The top result row should be 11, 33, 55.

Now select B2:D2 and copy the entire three-cell row down to B3:D4. This repeats the model for the other prices. Format B2:D4 with two decimal places so the grid is easy to compare:

        Qty 1   Qty 3   Qty 5
Price10 11.00   33.00   55.00
Price16 17.60   52.80   88.00
Price25 27.50   82.50  137.50

Inspect D4 specifically. It must contain =$A4*D$1*(1+$F$2): price 25, quantity 5, multiplier 1.10. Inspect B4 too: =$A4*B$1*(1+$F$2), expecting 27.50. Matching both corners is stronger evidence than inspecting only the seed cell.

4. Add totals and preserve the final model

In F5 enter Grid total and in F6 enter =SUM(B2:D4). Expect 504.90. In J6 enter =SUM(J2:J4), expecting 168. These totals describe different calculations; J is the unsurcharged line example, not another sum of the grid.

Save WPS-References-Final.xlsx, close it and reopen it. Confirm F2 is still 0.10, F6 is 504.90, J6 is 168, and D4 retains the exact mixed/absolute formula. Your main model is now complete. Do not clear the grid or reset its references before this saved-state check.

5. Diagnose mistakes in a named experiment copy

Save As WPS-References-Experiments.xlsx and make sure this filename is active before editing Model.

First change F2 to 0.20. All nine grid outputs should update; D4 becomes 150.00 and F6 becomes 550.80. J2:J4 remain 20, 48, 100 because their formulas do not refer to F2. If only one grid cell changes, some copied cells may contain values instead of formulas.

Next change B2 to the intentionally wrong formula =$A$2*B$1*(1+$F$2) and copy across/down again. All grid rows will use the price 10; this is why locking the price row was wrong. Restore the correct seed formula and refill B2:D4. Do not overwrite the final workbook with the broken experiment.

For a single-cell diagnostic, compare D4’s formula against its required inputs instead of adding dollar signs everywhere. An absolute reference prevents a coordinate from shifting during copying; it does not prevent someone editing F2 or guarantee that inserted/deleted rows leave every model meaningful.

Troubleshooting

• Formula displays as text: check for a leading apostrophe and a Text cell format. Change to General and re-enter the formula; merely changing the display format may not evaluate existing text.
• All prices identical: $A$2 was used instead of $A2.
• Quantities change down the rows: the row lock is missing from B$1.
• Surcharge disappears or becomes zero: a relative F2 shifted to a blank cell after copying.
• Numbers look rounded: widen columns and display two decimals. Formatting changes appearance, not the formula logic.
• An expected result differs: inspect the formula bar and the three referenced inputs. Do not replace the formula with the expected answer to make the check pass.

Official references

• WPS References — source snapshot retrieved 2026-10-19.

These are documentation-based instructions. The fixture expectations were checked independently; no native WPS run, application screenshot, installed-edition entitlement test or cross-program round trip is claimed. Academy page dates do not identify an application build.

Related guides

• Paste Values and Transpose Data in WPS Spreadsheet
• Build Worksheet Navigation Links in WPS Spreadsheet