How to Use Absolute References for Excel Depreciation Formulas
Question details
The user needs to prevent cell references from shifting when copying a depreciation calculation across multiple financial-year columns.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating financial depreciation across multiple tax years using a single conditional formula that needs to be dragged across both columns and rows.
- Observed behavior
- When copying the formula across columns, relative references change unexpectedly, causing inaccurate calculations or formula errors for subsequent financial years.
Identify which cells contain fixed data (such as start/end years and asset cost) and which cells contain dynamic data (such as the current tax year header) before constructing your formula.
Apply Absolute and Mixed References Using the F4 Key
Use absolute and mixed references to lock specific rows and columns so they remain constant when copying your depreciation formula across the spreadsheet.
When calculating depreciation across tax years, you typically need to lock the source data columns (like asset cost, start year, and end year) while allowing the row numbers to change. Conversely, for the tax year headers, you need to lock the row but allow the columns to shift. This technique relies on mixed cell references.
Click on the first cell where your depreciation formula is entered, usually under the first tax year column for your first asset.
In the formula bar, click on or highlight the specific cell reference that needs to be locked, such as the asset cost or start year (e.g., J3).
Press the F4 key on your keyboard to cycle through reference types. Stop when the dollar sign ($) locks the correct element. For example, toggle to $J3 to lock just the column, or B$2 to lock just the row header.
Construct the full logical formula. A typical depreciation check might look like: =IF(AND(B$2>=$J3,B$2<=$K3),$H3,0). Press Enter to apply.
Click the fill handle (the small square at the bottom right corner of the selected cell) and drag it across the financial-year columns to the right. Then, drag it downwards to apply the calculations to the remaining assets.
Easily Manage Complex Formulas in WPS Spreadsheet
WPS Spreadsheet offers powerful formula management tools, complete compatibility with Excel functions, and intuitive shortcuts like F4 to quickly apply absolute and mixed references for accurate financial calculations.
- 1. Open WPS Spreadsheet: Launch WPS Office, open your financial workbook, and navigate to your depreciation schedule.
- 2. Enter and Lock the Formula: Type your depreciation formula into the target cell and press the F4 key to toggle and apply absolute ($A$1) or mixed ($A1 / A$1) references.
- 3. Use the Fill Handle: Click and drag the small square at the bottom-right corner of the cell to seamlessly copy your locked formulas across all tax year columns and asset rows.
Frequently Asked Questions
What does the dollar sign ($) do in an Excel formula?
The dollar sign ($) creates an absolute or mixed reference. It tells the spreadsheet program to 'lock' the column letter or row number immediately following it, preventing that part of the reference from shifting when you copy and paste the formula to another cell.
Why isn't the F4 shortcut working to lock my cells?
If pressing F4 doesn't cycle through absolute references, ensure your cursor is directly clicking on or highlighting the cell reference inside the formula bar. On some laptops and compact keyboards, the function keys are secondary, so you may need to hold down the 'Fn' key simultaneously (Fn + F4).
What is the difference between $A1 and A$1?
$A1 locks column A but allows the row number to change when copied downwards. A$1 locks row 1 but allows the column letter to change when copied horizontally. These are known as mixed references, heavily used in grid calculations like spreading costs across tax years.
Can I apply absolute references to multiple cells in a formula at once?
Yes. If you highlight a specific range of cell references or an entire section of your formula within the formula bar and press F4, the application will simultaneously cycle through the reference lock types for all selected cells.
