How to Delete Columns without Losing Formula in Excel (3 Easy Steps)

If your datasheet contains both formulas and values, you need to apply some techniques to delete the contents or the cell values in a column or a range without affecting the formulas applied to it. In this article, you will learn how to delete columns without losing formula in Excel.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


3 Easy Steps on How to Delete Columns in Excel without Losing formula

Let’s assume a scenario where we have an Excel file that contains information on the Annual Salary of the employees of a company along with the individual Tax Rates that will be applied to the salary of each of the employees. The image below shows the worksheet along with the Tax Amount for each employee that we have derived using the below formula.

=B5*C5

We will now learn how to delete the column Tax Amount without losing the formula above.

How to Delete Columns in Excel Without Losing Formula


Step 1: Open Go To Special Menu to Delete Columns in Excel

With the Go to Special feature, we can find and select cells based on what they are containing. To clear contents without deleting formulas we can use this feature.

  • First, select all the cells of your data range except the column header.
  • Then, go to Home > Editing > Find & Select and click on Go to Special.

Open Go To Special Menu in Excel

  • Alternatively, you can also press the F5 key to open the Go To menu and then click on the Special button on the down-left corner of that menu.

Open Go To Special Menu in Excel

Read More: How to Delete Columns Based on Header Using VBA in Excel


Step 2: Select Constants From Go To Special Menu in Excel

  • Next, we will select Constants from the Go To Special.
  • Then, we will click OK.

 Select Constants From Go To Special Menu in Excel

Read More: VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)


Similar Readings:


Step 3: Delete Column without Losing Formula

  • Now, if we come back to the worksheet, we will see that only the cell values of the Annual Salary and Tax Rate columns are selected.

 Delete Column Without Losing Formula

  • Next, we will click the DELETE button to delete or remove these cell values as these values are constant rather than derivates from a formula.
  • Finally, we will see that cell values in the Tax Amount column are filled with zeroes (0) but the formula applied to this is not lost or distorted.

 Delete Column Without Losing Formula

  • Now, we will insert values to check if the formula is working correctly. We will find that formula is showing Tax Amount when we have inserted Annual Salary and Tax Rate.

 Delete Column Without Losing Formula

Read More: VBA to Delete Column in Excel (9 Criteria)


Quick Notes

🎯 You can read this article to learn how to clear content in Excel without deleting formulas.

🎯 Also read this one to learn how to delete columns in Excel without affecting the formula.


Conclusion

In this article, we have learned how to delete columns in Excel without losing the formula. I hope from now on you can delete columns in Excel without losing the formula very easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo