Table of Contents
Excel is an incredibly powerful tool for data analysis and reporting. It is capable of organizing data into tables, creating complex formulas to analyze data, and creating dynamic charts and graphs to visualize data. One of the most useful features of Excel is the ability to clear contents but not formulas. This can be a powerful time-saving tool for quickly resetting data tables or for quickly updating spreadsheets with new data. By understanding the importance of this feature, users can maximize their efficiency and make the most out of Excel.
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.