How to Delete Columns in Excel Without Affecting Formula (Two Ways)

You just can’t simply delete any column in Excel which is used in formulas in other columns. In this article, we will learn how to delete columns in Excel without affecting formulas.

Consider the following dataset. Here we have Unit Production in column B and Per unit Cost in column C. After multiplying these two we get the total cost per week and adding all the weekly total costs we get monthly cost. Now we want to delete columns B and C without affecting the formula in column D.

DATA deleting column

Download Practice Workbook

Deleting Column in Excel without Affecting Formula

1.   Using Special Feature

Select all the data (B5:D9) and press F5. A Go to box will appear. Click on Special.

deleting column without affecting formula

After that, the Go to Special box will appear. Select Constants. Then click OK.

deleting column without affecting formula

It will select your data cell only.

deleting column without affecting formula

Now press Delete. Only your data cell will be deleted.

deleting column without affecting formula

The formula in column D4 is still functional.

deleting column without affecting formula

If you give new entries in Columns B and C, you will get new values in Column D.

deleting column without affecting formula

2.   Using Paste Special

If you want to keep the values of your formula cell before deleting the columns, you can use the Paste Special feature. Select the formula cells and press CTRL+C.

deleting column without affecting formula

Select an empty cell. Right-click on it. Select Paste Special

deleting column without affecting formula

Paste Special box will appear. Select Values and press OK.

deleting column without affecting formula

It will copy your values of the formula cell.

deleting column without affecting formula

Now select the cells of the column you want to delete.

deleting column without affecting formula

Go to Home> Editing>Clear>Clear content.

deleting column without affecting formula

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

deleting column without affecting formula

Conclusion

By using any of the above methods you will be able to delete columns without affecting your formula. If you have any confusion, please leave a comment.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo