How to Delete a Column in Excel Without Affecting the Formula – 2 Methods

 

The following dataset showcases Unit Production in column C and Per unit Cost in column D. The total per month is calculated in column E.

To delete columns D and C without affecting the formula in column E:

how to delete column in excel without affecting formula


Method 1 – Using the Excel Go To Special Feature

Steps:

  • Select C5:E9 and press F5.
  • In Go To, click  Special.

Use Excel Go To Special Feature for Deleting Column without affecting formula

  • In Go To Special, select Constants and click OK.

  • It will select the defined cell range.

  • Press Delete. Only the selected data will be deleted.

  • The formula is still displayed in E5.

column deleted without affecting formula

  • If you enter new data in C5:D6, you will get new values in E5:E6.


Method 2 – Delete a Column Without Affecting the Formula using the Paste Special Feature

Use the Paste Special command in Excel.

Steps:

  • Select E5:E9 that contains the formula and press Ctrl+C.

Erase Column Without Affecting Formula with Paste Special Feature

  • Select an empty cell, here, F5. Right-click it.
  • In the Context Menu, select Paste Special.

  • In Paste Special, select Values in Paste.
  • Select None in Operation.
  • Click OK.

  • Values will be copied and pasted in F5:F9.

  • Select the column you want to delete, here C5:D8.

  • Go to Home > Editing > Clear > Clear Contents.

  • It will delete the content, but your formula won’t be affected.


Download Practice Workbook


Related Articles


<< Go Back to Delete Columns | Columns in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo