Deleting Everything Except Formulas

Summary: Need to get rid of everything in a worksheet except the formulas? It's easier to make this huge change than you think it is. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Let's face it—when it comes to developing worksheets, we spend most of our time creating formulas. It seems, then, a waste to throw out perfectly good formulas and recreate a worksheet from scratch just because you need to get rid of a bunch of values in the worksheet. There may be an easier way—just get rid of everything except the formulas. Follow these steps:

Display the worksheet you want to edit.
Press F5 to display the Go To dialog box.
Click Special. Excel displays the Go To Special dialog box. (Click here to see a related figure.)
Make sure the Constants option button is selected. The check boxes below Formulas should then become accessible.
Using the four checkboxes under Formulas, specify what type of constants you want to select.
Click OK. Excel selects all the cells that meet your criteria.
Press Delete. The contents of the selected cells are deleted.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3053) applies to Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Step Up and Take Control! Subscribers to ExcelTips know just how valuable a resource it is. ExcelTips Premium provides twice the number of exceptional, easy-to-understand tips every week in an ad-free newsletter, as well as substantial discounts on ExcelTips archives and e-books.

Check out ExcelTips Premium today!