Formula Errors

How to Fix an Excel Formula That Does Not Copy Correctly

Phi Hung Vo Sep 28, 2026 869 views

Question details

The user needs to lock specific starting cells in a formula so they remain fixed, while allowing the ending row reference to extend dynamically when the formula is copied downward.

How to Fix an Excel Formula That Does Not Copy Correctly

Product: Spreadsheets
Device & OS: not provided
Scenario: Copying or filling a formula down a column where starting cells must remain constant but the range needs to expand.
Observed behavior: Without proper referencing, all cell references in the formula shift automatically when copied down, resulting in incorrect calculations.

Before you start

Identify exactly which cells in your calculation need to remain constant and which ones should change dynamically before adjusting your formula.

Solution 1Recommended

Apply Absolute References to Lock Cells

Use dollar signs ($) in your formula to lock the starting cells so they don't shift when you copy the formula down.

By default, spreadsheets use relative references, meaning cell addresses adjust automatically as you drag the formula to a new row or column. To fix a formula that isn't copying correctly, you must apply absolute references to the cells you want to remain unchanged.

Enter the formula with absolute references

Select the cell where you want to enter the first formula. Type your formula using dollar signs for the fixed cells. For example, enter =$H$1-SUM($C$2:$C3).

Apply the formula

Press Enter to apply the formula to the current cell.

Copy the formula downward

Click on the cell again, hover over the bottom-right corner until the cursor turns into a cross (the Fill Handle), and drag it downward to copy the formula to subsequent rows.

Understanding the $ Symbol: The $ symbol before the column letter and row number (e.g., $H$1) locks both completely. A reference without $ (e.g., C3) remains relative and will automatically adjust to C4, C5, etc., as you drag down.

Smart Formula Management

Easily Manage Complex Formulas in WPS Spreadsheet

WPS Spreadsheet provides intuitive tools for managing complex calculations, including an easy shortcut (F4) to toggle between absolute and relative references effortlessly.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your data.
2. Toggle references with F4: Select the cell with your formula, highlight the cell reference you want to lock (e.g., H1) in the formula bar, and press the F4 key to instantly add dollar signs ($H$1).
3. Fill down the column: Press Enter, then double-click the fill handle in the bottom-right corner of the cell to apply the corrected formula down the entire column.

Press F4 to quickly lock cells with absolute referencesFully compatible with Microsoft Excel formulas and formattingBuilt-in formula error checking and tracing toolsLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

What is the keyboard shortcut to add dollar signs to an Excel formula?

You can select or place your text cursor inside the cell reference in the formula bar and press the F4 key. Pressing F4 multiple times will toggle between locking both column and row ($A$1), just the row (A$1), just the column ($A1), or neither (A1).

Why do my formulas change when I copy them to another cell?

By default, spreadsheet programs use relative references. This means cell references adjust based on their relative position when copied to a new cell. Using absolute references (by adding a $ sign) prevents this automatic adjustment.

Can I lock just the row but not the column when copying a formula?

Yes, this is called a mixed reference. By placing a dollar sign only in front of the row number (e.g., C$2), the row stays fixed when copying down, but the column can still change if the formula is copied sideways.

Excel & Data Problems