How to Use Absolute and Relative References in Excel Formulas
Question details
The user needs to keep a specific column (like date-of-birth) fixed while allowing row references to adjust automatically when copying or autofilling formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Autofilling a formula horizontally or vertically while ensuring a referenced column does not shift.
- Observed behavior
- By default, copying a formula changes both column and row references. The user requires a method to lock the column dimension while keeping the row dimension dynamic.
Identify the exact cell, row, or column you need to remain constant before applying your formula. Remember that inserting a dollar sign ($) before a column letter or row number is how you lock that specific dimension.
Apply a Mixed Cell Reference to Lock the Column
Use a mixed cell reference with a dollar sign before the column letter to lock the column while allowing the row to change during autofill.
A mixed cell reference combines absolute and relative dimensions. By placing a dollar sign ($) in front of the column letter (e.g., $B2), you instruct the spreadsheet to permanently fix column B. When you drag the formula to the right, it will still point to column B. However, because there is no dollar sign in front of the row number (2), the row reference will automatically update as you drag the formula downward.
Click the cell where you want to enter your calculation or formula.
Type your formula. When referencing the target data cell (e.g., the date of birth in cell B2), format it as $B2 to lock only the column.
Press the Enter key to calculate the initial result.
Click on the cell with your new formula, hover over the small square at the bottom right corner (the fill handle), and drag it horizontally or vertically to apply the formula across your data range.
Use the F4 Keyboard Shortcut to Toggle References
Quickly cycle through relative, absolute, and mixed references without manually typing the dollar sign.
Master Cell References with WPS Spreadsheet
Easily manage absolute, relative, and mixed references in WPS Spreadsheet to make data analysis and formula autofilling seamless. WPS Office offers an intuitive interface tailored for complex data tasks.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your dataset.
- 2. Enter your formula: Select the target cell, start typing your formula, and click the cell you wish to reference.
- 3. Toggle the reference type: Press the F4 key on your keyboard until you reach the desired mixed reference format (e.g., $B2).
- 4. Autofill the data: Click and drag the bottom-right corner of the cell to autofill the locked references horizontally or vertically.
Frequently Asked Questions
What is the difference between relative and absolute references in Excel?
A relative reference (like A1) changes automatically based on its new position when copied to another cell. An absolute reference (like $A$1) is completely locked and will always point to that exact cell, regardless of where the formula is copied.
When should I use a mixed reference?
You should use a mixed reference when you want to copy a formula across both rows and columns but need to lock only one dimension. For example, use $A1 to lock the column when dragging horizontally, or A$1 to lock the row when dragging vertically.
Why is my formula changing references when I drag it down?
By default, spreadsheet applications use relative references. When you drag a formula down a row, the row numbers in the formula increase automatically. To stop this, you must add a dollar sign ($) before the row number in your formula (e.g., A$1).
Is there a shortcut key for applying absolute references?
Yes. While editing a formula, you can place your text cursor on a cell reference and press the F4 key (on Windows) or Command+T (on Mac) to instantly toggle between relative, absolute, and mixed reference types.
