3 Ways to Clear Contents Without Deleting Formulas in Excel

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.

Dataset for highlighting cells in excel based on value


approach

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.

Trying Go To Special option to clear contents without deleting formulas

  • 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.

Using Go To Special option to clear contents in Excel without deleting formulas

📕 Read More: How to Select Multiple Cells in Excel (10 Quick Methods)


approach

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.

Applying Delete key to clear contents in Excel without deleting formulas

📕 Read More: 10 Ways to Select Multiple Cells Without Mouse in Excel


approach

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.

Employing VBA feature for clearing contents in Excel without deleting formulas

  • 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

(Visited 73 times, 1 visits today)

Tags:

Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo