Sometimes you need to delete rows in Excel but want to keep the formulas. In this article, we will discuss how to delete rows in Excel without affecting the formulas.
Consider the following dataset. Here we have week-wise Production of 4 units of an Automobile plant from column C to column F. After applying the sum formula in row 10 we get the total automobile production of a month for each unit. Now we want to delete rows 6-9 of the following dataset without affecting the formula in row 10.
Read More: How to Delete Rows Using Excel Shortcuts
Similar Readings:
- How to Delete Row If Cell Contains Value Using Macro in Excel
- How to Delete Row Using Macro If Cell Contains 0 in Excel
- VBA to Delete Every Other Row in Excel
- How to Delete Multiple Rows with VBA in Excel
- How to Delete Rows in a Range with VBA in Excel
- How to Delete Hidden Rows in Excel
How to Delete Rows in Excel without Affecting Formulas: 2 Ways
Method 1. Using the Go To Special Feature to Delete Rows without Affecting Formulas
In this method, we will use the Go To Special dialog box to keep our formula row intact but delete the data cells. Following the steps below we can delete rows in Excel without affecting formulas.
Step 1:Â
- Select all the data sets but the formula cells of row 10 that you want to delete.
- I have selected the range C6:F9.
Step 2:
- Now press the F5 or CTRL + G keys at the same time to launch the Go To box.
- After the box appears click on Special.
Step 3:Â
- After clicking Special, the Go to Special dialog box will appear.
- Now check Constants & then click OK.
Step 4:Â
- Now select all the rows that you want to delete but the formula.
- Then press Delete on the keyboard or Right-click on the mouse and then select Delete.
- Though all the data from rows have been deleted but the formula in C10 is still active & it will remain so for the rest of the formulas.
Step 5:Â
- Now apply a new set of data in rows 6-9 & it will show new results in row 10 using the same old formula.
Read More: How to Delete Multiple Rows in Excel at Once
Similar Readings:
- How to Delete All Rows Not Containing Certain Text in Excel
- How to Delete Rows Based on Another List in Excel
- How to Remove Rows Containing Identical Transactions in Excel
- [Fixed!] Not Enough Memory to Delete Rows Error in Excel
- How to Delete Row Using VBA
- How to Delete Row with VBA Macro If Cell Is Blank in Excel
- How to Delete Entire Row Based on Cell Value Using VBA in Excel
Method 2. Use of Paste Special Option to Delete Rows without Affecting Formulas
In this method, we will learn how to delete rows without affecting the formulas using the Paste Special option. We will use Paste Values under the Paste Special category.
Step 1:Â
- If you want to keep the formulas active but delete your row data, you can use the Paste Special.
- Select the formula cells only and press CTRL + C or right-click the mouse and select Copy.
Step 2:Â
- Now select a blank cell and right-click on it & Context Menu will appear.
- From there click on the Paste Special on the ribbon or try the keyboard shortcut CTRL + ALT + V.
- Now select the Values option from the Paste Special box & then click OK.
Step 3:Â
- Now select the cells of the rows that you want to delete.
- Then go to the Home tab & then follow Editing >> Clear >> Clear contents.
Step 4:Â
- It will delete the cells of the rows but your formulas will not be affected.
Step 5:Â
- Now apply a new set of data in rows 6-9 & it will provide new results in row 10 using the same old formula.
Notes: While following step 3 of method 2, the Paste Special feature might look different depending on MS Office versions. Just keep searching for the Paste Values option or use the keyboard shortcut directly.
Read More: How to Delete Selected Rows in Excel
Similar Readings:
- How to Delete Row If Cell Is Blank in Excel
- How to Delete Empty Rows at the Bottom in Excel
- How to Delete Multiple Rows in Excel Using Formula
- How to Delete Multiple Rows in Excel with Condition
- How to Delete Rows in Excel That Go on Forever
- How to Delete Infinite Rows in Excel
Practice Section
I have provided a data set for you. Practice it on your own.
Read More: Delete All Rows Below a Certain Row in Excel
Similar Readings:
- How to Delete Filtered Rows in Excel
- How to Delete Unused Rows in Excel
- How to Find and Delete Rows in Excel
- How to Delete Every Other Row in Excel
- How to Delete Blank Rows in Excel
- Delete Row If Cell Contains Specific Values in Excel
Download Practice Workbook
Related Content: How to Delete Rows in Excel with Specific Text
Conclusion
By using any of the two quick methods you will be able to delete rows without affecting your formulas in Excel. If you have any confusion, please leave a comment.
Related Content: How to Remove Highlighted Rows in Excel
Related Articles
- Applying VBA Code to Delete Rows Based on Multiple Cell Value
- Excel VBA to Delete Rows with Specific Data
- How to Delete Selected Rows with Excel VBA
- How to Use VBA to Delete Empty Rows in Excel
- How to Use Macro To Delete Rows Based on Criteria in Excel
- How to Filter and Delete Rows with VBA in Excel
- How to Delete Unfiltered Rows in Excel Using VBA
- How to Delete Hidden Rows in Excel VBA