How to always get the value from above cell when inserting or deleting row in Excel?
Normally, we can reference above cell value by using a simple formula, but, when you insert a new row above the reference cell formula, the reference cell will not be changed automatically as following screenshot shown. This article, I will talk about how to always get the above cell value when inserting or deleting rows in Excel.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
To deal with this job, here is an easy formula can help you, please do as follows:
Enter this formula into a cell where you want to reference the above cell: =INDIRECT(ADDRESS(ROW()-1,COLUMN())), and press Enter key to get the value from above cell, see screenshot:
And now, when you inserting new row above the reference formula cell, the referenced cell will always be the above cell value. See screenshot: