If your datasheet contains both contents and formulas you need to apply some techniques to delete the contents without affecting the formulas. In this article, you will get to know 3 easy ways to clear contents in Excel without deleting formulas.
Suppose, we have a dataset of the weekly production cost of a company. In this dataset, we get the total cost in column D by multiplying unit production(column B) and per unit cost (column C). Now we will clear the contents from this dataset without affecting the formula of column D’s cells.
Download Practice Workbook
3 Ways to Clear Contents in Excel Without Deleting Formulas
1. Go to Special to Clear Contents Without Deleting Formulas
With the Go to Special feature, we can find and select cells based on what they are containing. To clear contents without deleting formulas using this feature, first select your dataset. Then, go to Home > Editing > Find & Select and click on Go to Special.
After that, the Go To Special window will appear. Select Constants. If you do not want to delete the text from your dataset, remove the tick mark from the Text box. Finally, click on OK to select the contents of your dataset.
As a result, you will see only the contents of your dataset are selected. Press DELETE to delete the contents.
So now the contents of your dataset are cleared.
If you want to test whether the formulas of the dataset are deleted or not, follow the following steps. First, give entries in a cell of column B. After that, give another entry in the cell of the same row from column C. Now you can see the cell of column D of the same row is showing a value. That means your formulas are not deleted.
2. Clear Contents Feature
Another simple way to clear contents without deleting formulas is using the clear contents feature. First, select the cells of your dataset which contain only contents. After that go to Home > Editing > Clear and select Clear Contents.
As a result, all the contents of your dataset will be cleared without deleting the formulas.
3. VBA Immediate Window to Clear Contents Without Deleting Formulas
You can use Immediate Window from Microsoft Visual Basic Applications (VBA) to clear contents. First, select the cells of your dataset and press ALT+F11 to open the VBA window. After that, press CTRL+G. It will open the Immediate Window.
Type the following code, in the Immediate window and press ENTER,
The code will clear the contents from your selected cells without deleting the formula.
Close the VBA window and you’ll see all the contents of your selected cells are deleted but the formulas are not affected.
By following any of the 3 ways, you will be able to clear contents in Excel without deleting formulas. If you have any confusion, please feel free to leave a comment.