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.
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:
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!