How to Delete Rows in Excel without Affecting Formulas (2 Quick Ways)

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.

Deleting Rows in Excel without Affecting Formulas


Download Practice Workbook


2 Ways to Delete Rows in Excel without Affecting Formulas 

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 formula cells of row 10 that you want to delete.
  • I have selected the range C6:F9.

Deleting Rows without Affecting Formulas Using Go To Special

Step 2:

  • Now press F5 or CTRL + G keys at the same time to launch the Go To box.
  • After the box appears click on Special.

Deleting Rows without Affecting Formulas Using Go To Special

Step 3: 

  • After clicking Special, the Go to Special dialog box will appear.
  • Now check Constants & then click OK.

Deleting Rows without Affecting Formulas Using Go To Special

Step 4: 

  • Now select all the rows that you want to delete but the formula.
  • Then press Delete on the keyboard or Right-click to mouse and then select the 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 Rows in Excel That Go on Forever (4 Easy Ways)


Similar Readings:


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 & Copy.

Deleting Rows without Affecting Formulas Using Paste Values

Step 2: 

  • Now select a blank cell & Right click on it & Context Menu will appear.
  • From there Left click on the Paste Special on the ribbon or try the keyboard shortcut CTRL + ALT + V.

  • Now select the Values option from 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.

Deleting Rows without Affecting Formulas Using Paste Values

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.

Related Content: How to Delete Multiple Rows in Excel Using Formula (5 Methods)


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.


Practice Section

I have provided a data set for you. Practice it on your own.


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 Articles

Asif Khan Pranto

Asif Khan Pranto

Hello! I'm Asif here, currently working with Exceldemy as an Excel & VBA Content Developer. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My goal is to work with an organization which will give myself a chance to upgrade besides having a real impact on our surroundings. I'm passionate about travelling new communities & trekking. In my leisure period I usually read books. I've completed graduation in Mechanical Engineering & now I am pursuing Master of Development Studies to experience a new spectrum of knowledge. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo