How to Fix Excel Formula Not Changing When Dragged Horizontally
Question details
The user is experiencing an issue where an Excel formula produces the exact same numerical result in every column 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.
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.
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).
Click the first cell containing the formula that fails to update when dragged.
Click into the Formula Bar at the top or press F2 on your keyboard to edit the cell's contents directly.
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).
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.

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. Open your file in WPS Spreadsheet: Launch WPS Office, open your workbook, and locate the data you need to calculate.
- 2. Edit the formula references: Select the cell, press F2, and replace hardcoded numbers with relative cell references (e.g., =A1/B1).
- 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.

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.




