How to always get the value from above cell when inserting or deleting row in Excel?
In Excel, referencing the value of the cell directly above is a common requirement for creating running totals, month-on-month comparisons, or simply maintaining data consistency as your worksheet evolves. Normally, you can reference the cell above using a straightforward formula like =D5, but this solution has its limitations: if you insert or delete rows, your formula may not continue to reference the new "above" cell as you’d expect. As illustrated below, inserting a new row above can break the continuity or keep the reference linked to the original cell instead of adjusting to the new position.
To solve this issue and ensure your formula always retrieves the value from the immediate cell above, even after you insert or delete rows, there are several approaches you can take. Each method comes with different trade-offs based on your worksheet complexity, whether you want an automatic update or a manual formula, and whether you are comfortable using VBA/macros.
Table of contents:
- Always get the value from above cell when inserting or deleting rows with formula
- Automatically update cell value from the cell above using VBA event-driven macro (always dynamic)
Always get the value from above cell when inserting or deleting rows with formula
To address this problem in a straightforward way that does not require macros or complex setup, you can use a formula that dynamically refers to the cell above, no matter how your rows change. The formula uses Excel’s INDIRECT and ADDRESS functions so that referencing always "tracks" the cell above, even if the rows are shifted due to insertions or deletions. This makes it especially suitable for worksheets where you frequently modify the row structure, such as adding new data at the top or middle of a list.
Enter the following formula directly into the cell where you want to always get the value from above (for instance, in cell B6 if you want to reference B5):
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))Press Enter after typing the formula. The current cell will instantly display the value of the cell immediately above it, as shown below:
Now, if you insert a new row anywhere above the reference formula cell, the formula will recalculate and always display the value from the new cell above. This way your formulas are always up to date, regardless of row insertions or deletions. Take a look at the following screenshot for reference:
Parameter explanation and tips:
- This formula will pull the value of the cell directly above the current formula cell—so using it in B6 will always reflect B5, even if rows are inserted or deleted above.
- If you use this formula in the first row of your data (such as A1), it may try to pull data from a non-existent row and return a
#REF!error. You can avoid this by adding error-handling, for example with=IF(ROW()=1,"",INDIRECT(ADDRESS(ROW()-1,COLUMN())))to show blank in the first row. - Keep in mind that INDIRECT is a volatile function, so in extremely large worksheets, excessive use can slow down calculations.
- Using this formula works well when you want to preserve a strict relationship to the row location, no matter how your sheet structure changes.
Troubleshooting and summary suggestions:
If your formula does not update as expected after inserting or deleting rows, double-check that it is entered in the intended cell. Also, ensure you are not using absolute cell references (such as $A$1), which are static. If you encounter #REF! errors in the first row, consider using a conditional formula as previously mentioned. For advanced automation, or if you need the value copied, not just referenced, see the VBA event-driven macro solution below for a dynamic, code-based approach.
Automatically update cell value from the cell above using VBA event-driven macro (always dynamic)
If you require a solution where the cell value always automatically updates to match the cell above regardless of inserting or deleting rows—and you want this behavior managed without copying formulas—using an event-driven VBA macro is effective. This method is ideal for users comfortable with enabling macros and allows for dynamic updating: whenever you select a specific cell or range, or when you change a value, the macro can instantly set the target cell to always equal the value above, making data entry consistent no matter how your worksheet changes. This is especially useful in templates where you want a fixed pattern or default value setup in each new row.
Here’s how to set this up using the Worksheet_Change event:
1. Right-click on the worksheet tab where you want this functionality, and select View Code. The Microsoft Visual Basic for Applications editor will open to the correct worksheet module.
2. Copy and paste the following VBA code into the worksheet module window:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
On Error Resume Next
' Set the range you want to monitor (for example, B2:B100)
Set WatchRange = Intersect(Target, Me.Range("B2:B100"))
If Not WatchRange Is Nothing Then
Application.EnableEvents = False
Dim cell As Range
For Each cell In WatchRange
' Avoid the first row, or adjust as needed
If cell.Row > 1 Then
cell.Value = Me.Cells(cell.Row - 1, cell.Column).Value
End If
Next cell
Application.EnableEvents = True
End If
End SubParameter notes:Replace "B2:B100" in Me.Range("B2:B100") with the actual range where you want this behavior (you can set it to an entire column, e.g. "B:B", but narrowing the range improves performance and avoids unintentional overwrites).
3. Close the VBA editor. Now, whenever a cell is changed, inserted, or the worksheet is updated within your specified range, Excel will automatically update those cells to reflect the value above. For example, if you insert a row at row 5, all monitored cells in column B from that point will pick up the value from the cell directly above their new position.
- Be cautious: this code will overwrite manually entered values within the monitored range. Use with care if you have formula cells or want to preserve original entries.
- If you want to continue using this VBA macro in the workbook in the future, you need to save the file as a macro-enabled workbook (.xlsm).
- Event code will only work on the worksheet module where the macro is pasted (not across all sheets unless code is added to each sheet module).
- If you want the update to occur on selecting a cell instead of upon changing a value, you can use Worksheet_SelectionChange and a similar logic.
Troubleshooting and summary suggestions:
If the VBA script does not seem to work after copying, ensure macros are enabled in your workbook and that you pasted the code into the correct sheet module (not a standard module). If you get errors or experience Excel freezing, double-check that Application.EnableEvents is set to False before your automated cell changes and reset to True after to avoid recursive looping. For other advanced behaviors or finer control, consider custom scripting based on your data structure.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in