Excel: How to Clear Contents but Not Formulas

Often you may want to clear the contents of all cells in a particular range in Excel except for cells that contain formulas.

Fortunately this is easy to do by using the Go To Special function in Excel.

The following example shows how to use this function in practice.

Example: How to Clear Contents But Not Formulas in Excel

Suppose we have the following dataset that contains information about sales of various products at some company:

Each cell in the Revenue column contains a formula that multiplies the Units Sold by the Price.

Suppose we would like to clear the contents of all cells in columns B through D except for the cells that contain formulas.

To do so, we can highlight the cell range B2:D8, then press F5.

This will bring up the Go To window:

Click the Special button in the bottom left corner.

In the new window that appears, click the radio button next to Constants:

Once you click OK, all cells except for the ones with formulas will be selected:

Now you can simply press Delete and all of the contents in these cells will be deleted:

Notice that the only cells with contents are the ones that contain formulas in the Revenue column.

For example, cell D2 still contains the formula =B2*C2 but it simply displays 0 now since cells B2 and C2 both contain no value.

All cells in the Units Sold and Price columns that simply contained numbers have been cleared.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Replace #N/A Values in Excel
How to Interpolate Missing Values in Excel
How to Count Duplicates in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *