ASM Arman
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*C5We will now learn how to delete the column Tax Amount without losing the formula above.
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.
- 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.
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.
Read More: VBA Macro to Delete Columns Based on Criteria in Excel (8 Examples)
Similar Readings
- How to Delete Column and Shift Left Using VBA in Excel (5 Methods)
- Delete Multiple Columns by Number Using VBA in Excel (4 Ways)
- How to Delete Extra Columns in Excel (7 Methods)
- How to Clear Contents in Excel Without Deleting Formulas (3 Ways)
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.
- 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.
- 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.
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!!!
