With Excel, you can clear content in the sheet. It means that you can clear all the content without deleting the one that has the formula. You must be thinking about how to clear content in Excel when you are not an expert in using this tool.
At times, people use a template that has some formulas already in it. with the help of this template, you can easily manage to delete the contents except for formulas. Keeping formulas is necessary because it helps you later and saves time.
Let’s dive in to see how you can accomplish this task:
How to Clear Contents in Excel with Shortcuts?
With a “Delete” button from the keyboard, you can simply clear content in a cell or even a cell range in Excel. It works similarly even when you have selected one cell, more than one cell that is non-contiguous, or even a series of cells.
Besides all of the above, you can follow the below-given instructions to perform this task.
Backspace: With this key, usually you don’t clear anything, however, it works when you choose a cell and press the backspace key, and it will clear the content from the cell.
ALT + H + E + A: With this key combination, you can enable the Clear Content option given in the Home tab.
Apart from the above shortcuts, we have a few more key combinations for the same task. Let’s find out:
CTRL + SHIFT + F: With this key combination, you can clear the cell formatting while keeping the content. Select the cells that needed to clear and press the CTRL + SHIFT + F from the keyboard. It will save the contents but remove the formatting.
CTRL + SHIFT + O: With this key combination, you can clear comments from the selected cells. Select the cells you need to clear comments from and press CTRL + SHIFT + O keys from the keyboard.
CTRL + SHIFT + H: With this key combination, you can clear the hyperlinks from selected cells. Select the cells and press the CTRL + SHIFT + H keys from the keyboard. It will delete the hyperlinks from the selected cells instantly.
CTRL + SHIFT + A: With this key combination, you can clear all content as well as format cells. For this, select the cells you need to clear and press the CTRL + SHIFT + A keys from the keyboard.
How to Clear Contents in Excel with Go To Special?
In our dataset, you can see Column D has formulas, whereas other cells have static values. Let’s follow the steps given below to clear the content from the cells:
- Select the whole dataset.
- Press the F5 key and you will see the Go-To dialog box.
- Choose the “Special” button. It will open the “Go-To Special” dialog box.
- Choose the “Constant” option.
- Press OK.
Using the above steps will let you select the cells with values only. Cells containing formulas will not be selected. Now, the cells with values are selected, you can simply press the DELETE key to clear the values from these cells.
How to Clear Contents in Excel with VBA?
VBA is not an all-time favorite for some users. However, it does not mean that it reduces the importance of VBA. With this process, you can increase the process functionality because it uses codes. Below you will find the VBA code that lets you clear the content of each cell you have selected without touching the formulas:
Selection.SpecialCells(xlCellTypeConstants, 23).Clear
You can even apply this code to the entire worksheet and for this, you can follow the below code:
Activesheet.cells.SpecialCells(xlCellTypeConstants, 23).Clear
- Open the Excel book in which you need to clear the contents.
- Right-click on the sheet tab.
- Now, choose the View Code option and it will open up the Visual Basic Editor.
- If the Immediate window is not visible, click on the View option from the menu and choose the Immediate Window.
- In the Immediate Window, copy and paste the above-given code.
- Hover the cursor at the end of the line.
- Press the ENTER key.
- Now, you can notice you have cleared the contents from the entire selected area.
That’s it!
Enjoy practicing your Excel tricks!
