How to Return the Previous Cell Value When Dragging an Excel Formula Right
Question details
The user wants to keep a formula's source column fixed so it continues to reference the previous cell's value when dragged horizontally across multiple columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Dragging a formula to the right across monthly data columns without the reference shifting to the next column.
- Observed behavior
- By default, Excel uses relative cell references, causing the column letter to shift as the formula is dragged to the right. The goal is to prevent this shift.
Identify the specific column letter in your formula that you want to remain fixed, as you will need to manually insert a dollar sign ($) to lock it.
Use an Absolute Column Reference
Lock the column reference with a dollar sign to prevent it from shifting when you drag the formula horizontally.
In spreadsheet applications, cell references are relative by default. This means that if you reference cell A1 and drag the formula to the right, the reference automatically changes to B1, C1, and so on.
To stop the column from changing while still allowing the row to adjust if dragged downward, you must use a mixed reference. Placing a dollar sign ($) directly before the column letter locks the column in place.
Click on the cell where you want to enter your initial formula.
Type your formula, placing a dollar sign ($) immediately before the column letter of the cell reference you want to fix. For example, type =$A1+10 to lock column A.
Press the Enter key to calculate the result in the current cell.
Click the cell containing the formula, grab the fill handle (the small square at the bottom-right corner of the cell selection), and drag it to the right across your target columns. The formula will now continually reference column A.
Handle Complex Formulas Easily with WPS Spreadsheet
WPS Spreadsheet offers a powerful, intuitive environment for data analysis. You can seamlessly use absolute and mixed cell references, exactly as you would in Excel, to manage large datasets and prevent formula errors when dragging across columns.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Enter the locked formula: Type your formula using the $ symbol to lock the column (e.g., =$A1) and press Enter.
- 3. Drag the fill handle: Click and drag the small square at the bottom-right of the cell across your desired columns to apply the locked formula.
Frequently Asked Questions
What is the difference between $A$1, $A1, and A$1?
$A$1 is a fully absolute reference where both the column and row are locked. $A1 locks only column A, allowing the row number to change when dragged vertically. A$1 locks only row 1, allowing the column letter to change when dragged horizontally.
Why does my formula return an error when I drag it?
If your formula returns an error like #REF! or #VALUE! when dragged, it is likely because relative references shifted to empty cells, text cells, or cells outside the worksheet boundary. Using absolute references ($) prevents this unintended shifting.
Can I lock multiple cell references in a single formula?
Yes, you can apply absolute or mixed references to as many cell addresses as needed within a single formula. Simply add the dollar sign ($) before the respective column letters or row numbers for each cell reference you want to fix.
