Normally Formulas are associated with the referenced cell but for any reason or circumstances if you want or wish to shift cells down in Excel without changing the formulas then you are allowed to do it by following certain ways. In this article, I’m going to explain how to shift cells down in Excel without changing the formula.
To make the explanation clever, I’m going to use a sample dataset that represents information about a particular employer regarding salary and bonus. The dataset contains 4 columns: Employee Name, Salary, Bonus, and Gross Salary.
How to Shift Cells Down in Excel without Changing Formula: 4 Ways
In my dataset, I have calculated the Gross Salary using the Base Salary and Bonus to create the formula.
The formula I used is:
=C5*D5+C51. Using Drag Option to Shift Cells Down in Excel without Changing Formula
The Drag option is one of the most convenient options to shift cells down without changing the formula.
STEPS:
- Here, to shift the formula cells down select the cells of your choice.
- I selected the cell range E5:E12.
- Now, hold the SHIFT key then drag it down to shift cells down.
Here, the Cells are Shifted Down without changing Formula.
Read More: How to Shift Cells Down in Excel
2. Using Context Menu to Shift Cells Down in Excel without Changing Formula
You also can shift cells down using Context Menu while the formula won’t change.
STEPS:
- To begin with, select any cells of your choice.
- I selected cell E5.
- Now, right-click on the mouse to open the Context Menu.
- From the Context Menu select Insert.
A dialog box of Insert will appear.
- Select Shift cells down then click OK.
As a result, you will notice the cell is shifted down without changing the formula.
3. Insert Entire Row to Shift Cells Down
By Inserting a new Row you also can shift cells down while the formula won’t change.
STEPS:
- To begin with, select any row of your choice.
- I selected row 7.
- Now, open the Home tab >> from Insert >> select Insert Sheet Rows option.
Here, the new row is inserted without changing the formula.
4. Cut and Paste to Shift Cells Down in Excel without Changing Formula
Here, Cut & Paste is another way to shift cells down without changing the formula.
STEPS:
- To begin with, select any cells of your choice.
- I selected cell E7.
- Now, right-click on the mouse to open the Context Menu.
- From the Context Menu select Cut.
- Now, select any empty cell to Paste the value.
- Then, right-click on the mouse to open the Context Menu.
- From the Context Menu select Paste.
Hence, the cell is shifted down without changing formula.
Practice Section
You can practice the explained methods in the practice section.
Download to Practice
Conclusion
In this article, I have shown 4 methods to shift cells down in Excel without changing formula. These ways will help you to shift cells down. Feel free to comment down below for any types of queries and suggestions.
