How to Clear Contents in Excel Without Deleting Formulas (3 Ways)

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.

data


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.

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.

go to special window

As a result, you will see only the contents of your dataset are selected. Press DELETE to delete the contents.

clear contents in Excel without deleting the formulas

So now the contents of your dataset are cleared.

clear contents in Excel without deleting the formulas

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.

testing formula

Read more: How to Delete Columns in Excel Without Affecting Formula


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.

clear contents in Excel without deleting the formulas

As a result, all the contents of your dataset will be cleared without deleting the formulas.

clear contents in Excel 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.

vba

Type the following code, in the Immediate window and press ENTER,

Selection.SpecialCells(xlCellTypeConstants, 23).Clear

The code will clear the contents from your selected cells without deleting the formula.

Immediate window

Close the VBA window and you’ll see all the contents of your selected cells are deleted but the formulas are not affected.

clear contents in Excel without deleting the formulas


Conclusion

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.


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