How to Delete Column in Excel Without Affecting Formula (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

You just can’t simply delete any column in Excel which has formulas in other columns. Doing this blindly will remove your hard-written formula and data. In this article, we will discuss how you can delete the column in Excel without affecting the formula.


How to Delete Column in Excel Without Affecting Formula: 2 Ways

Here, we discussed how you can delete column content in Excel without affecting the cell content or formula in two separate ways. Consider the following dataset. Here we have Unit Production in column C and Per unit Cost in column D. Moreover, after multiplying these two we get the total cost per week, and adding all the weekly total costs we get the monthly cost. Now we want to delete columns D and C without affecting the formula in column E. In order to avoid any compatibility issues, try to use the Microsoft Office 365 edition.

how to delete column in excel without affecting formula


1. Use Excel Go To Special Feature for Deleting Column

Excel Go To feature is a special feature that can carry versatile tasks at ease. This feature can be turned on by simply pressing a keyboard shortcut. The process is described below.

Steps:

  • Select all the data (C5:E9) and press F5. The Go To box will appear.
  • Click on Special.

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

  • The Go To Special box will appear.
  • Select Constants and click OK.

  • It will select your data cell only from your selected range of cells.

  • After that, press Delete. Only your selected data cell will be deleted.

  • Then if you check, the formula in cell E5 is still functional.

column deleted without affecting formula

  • If you give some new dummy entries in the range C5:D6, you will get new values in the range E5:E6.
  • This means we have successfully deleted the column content without affecting the formula in cells.


2. Erase Column Without Affecting Formula with Paste Special Feature

If you want to keep the values of your formula cell before deleting the columns, you can use the Paste Special command in Excel. For this, we obviously have to copy the dataset first.

Steps:

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

Erase Column Without Affecting Formula with Paste Special Feature

  • Select an empty cell, like cell F5. Right-click on it.
  • From the Context Menu, select Paste Special.

  • The Paste Special box will appear. Select Values under the Paste option.
  • Next, select None under the Operation option and press OK.

  • This will copy your values of the formula cell and paste their range F5:F9.

  • Now select the cells of the column you want to delete.
  • In this case, it is C5:D8.

  • To delete contents in cells, go to Home > Editing > Clear > Clear Contents.

  • It will delete the cells of the column, but your formula values won’t be affected.


Download Practice Workbook


Conclusion

To sum it up, we can delete a column in Excel without affecting the formula here in two separate ways. For this problem, a workbook is available to download where you can practice these methods. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the community will be highly appreciated.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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