Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. There are frequent examples where you need formulas only in Excel. Using various ways, you can clear contents in Excel without deleting formulas. This article will help you with three ways to clear contents in excel without deleting formulas. It is applicable for multiple cells in Excel and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.
📁 Download Excel File
Download the Excel file below.
Learn to Clear Contents in Excel Without Deleting Formulas with These 3 Suitable Approaches
The article is going to introduce three approaches in Excel to clear contents without deleting formulas. The methods will help you understand the uses elaborately. The following discussion will cover the diverse uses of the Go To Special option and the Delete key in Excel. You can also use the VBA Macro Code feature in this case. Therefore, users will get a walkthrough of these solutions. A dataset will be modified in the following methods below to help you understand.
1. Using Go To Special Option
The Go To Special is an option to select the cells from a range according to specific types of data within it. After the selection, you can easily delete them and clear contents without deleting formulas in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, press CTRL+G and select the Special option.
- Here, you should get a Go To Special dialogue box.
- Then, choose the Constants option and untick the Text option.
- After that, hit the OK button.
- Now, you can see that Excel has selected the non-formula cells.
- Next, click on the Delete key.
- Finally, you can see your cells with the formulas, but without any value in them.
Read More: How to Select Multiple Cells in Excel (10 Quick Methods)
2. Applying Delete Key
The Delete key removes the non-formatted cell values. This automatically clears the content, but without deleting the formulas.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select the cell ranges B6:B9 and D6:D9 together holding the Ctrl key.
- Secondly, hit the Delete key.
- Third and finally, you will be able to clear contents in Excel without deleting formulas.
Read More: 10 Ways to Select Multiple Cells Without Mouse in Excel
3. Employing VBA Feature
Using VBA (Visual Basic for Applications), you can employ Immediate to apply on your worksheet. Moreover, it is also the only method that will create a new function to clear contents without deleting formulas in Excel automatically. With the proper instruction, you can make your work easier.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell range B6:G9.
- Now, press Alt+F11 to open Visual Basic Application (VBA).
- Next, press Ctrl+G to open the Immediate box.
- Then, write the following code on the Immediate box.
Selection.SpecialCells(xlCellTypeConstants, 23).Clear
- Here, you should see the window like the image below.
- Finally, close the VBA window and you will get all your cells clear of contents without deleting formulas.
📄 Important Notes
🖊️ You should modify your formulas according to your dataset.
📝 Takeaways from This Article
The article lets the readers understand the variety of options available to highlight cells based on the value in Excel.
📌 Primarily, you can use the Go To Special option.
📌 Also, you can utilize the Delete key. Here you can clear the contents and the formulas will stay intact.
📌 You can employ VBA Macro Code for automatic action.
Conclusion
To clear contents in Excel without deleting formulas, three methods are helpful. You can use the Go To Special option and the Delete key in Excel. You can also use the VBA Immediate Code feature to help you with it. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
Related Articles
- 7 Ways to Check If Multiple Cells Are Equal in Excel
- 3 Ways to Divide a Group of Cells by a Number in Excel
- 7 Ways to Assign Value If a Cell Contains Word in Excel