Calculation Issues

How to Fix Excel Formula Not Changing When Dragged Horizontally

Huda QurayshiHuda Qurayshi Nov 11, 2026 869 views

Question details

The user is experiencing an issue where an Excel formula produces the exact same numerical result in every column when dragged horizontally.

How to Fix Excel Formula Not Changing When Dragged Horizontally
Product
Excel
Device & OS
not provided
Scenario
Dragging a formula horizontally to auto-fill adjacent cells with calculations.
Observed behavior
The formula returns identical values in all cells because it relies on fixed hardcoded numbers instead of relative cell references.
Before you start

Select the affected cell and check the formula bar to verify if the formula contains hardcoded numbers (such as =1491578/4.71) instead of standard cell references.

Solution 1Recommended

Replace Fixed Numbers with Relative Cell References

Modify your formula to use relative cell references so it dynamically updates when dragged horizontally.

When a formula contains only fixed numbers, Excel will calculate the exact same value regardless of where the formula is copied. To allow the spreadsheet to adjust the formula automatically based on its new column position, you must use relative cell references (e.g., B2/C2).

1
Select the affected formula

Click the first cell containing the formula that fails to update when dragged.

2
Edit the formula

Click into the Formula Bar at the top or press F2 on your keyboard to edit the cell's contents directly.

3
Input cell references

Delete the fixed numbers and replace them by clicking the corresponding cells that contain the dynamic values you want to calculate (for example, change =100/5 to =B2/C2).

4
Drag the fill handle

Press Enter to save your new formula. Click the small green square at the bottom-right corner of the cell and drag it horizontally to the right. The formula will now adjust its references automatically.

Replace Fixed Numbers with Relative Cell References
Understanding Absolute References: Relative references update when copied to a new column. If you need a specific cell reference to remain constant while dragging, lock it by adding dollar signs to create an absolute reference (e.g., =$B$2).
Smart Data Calculations

Use WPS Spreadsheet to Easily Manage Cell References

WPS Spreadsheet provides intuitive formula editing and smart auto-fill capabilities to help you handle cell calculations effortlessly, with full compatibility for Excel workbooks.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office, open your workbook, and locate the data you need to calculate.
  2. 2. Edit the formula references: Select the cell, press F2, and replace hardcoded numbers with relative cell references (e.g., =A1/B1).
  3. 3. Use the smart fill handle: Click and hold the small square at the bottom right corner of the active cell, then drag it horizontally to auto-fill the formula across columns.
100% compatible with Microsoft Excel formulas (.xlsx)Smart fill handle for quick horizontal and vertical formula draggingIntuitive formula bar with color-coded reference highlightingFree and lightweight suite for everyday data calculations
microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between relative and absolute cell references?

Relative references (like A1) automatically change based on their position when copied to another cell. Absolute references (like $A$1) are locked to the specific row and column and will not change when the formula is dragged horizontally or vertically.

Why does my formula still show the same result even with correct cell references?

Your workbook calculation options might be set to 'Manual'. To fix this, navigate to the Formulas tab, click 'Calculation Options', and select 'Automatic' so your formulas update immediately when dragged.

How do I lock only the row or the column when dragging a formula?

You can use a mixed reference by placing the dollar sign before only the specific part you want to lock. For example, $A1 locks the column but allows the row to change, while A$1 locks the row but allows the column to change.