Deleting Constants while keeping formulas in Excel

In the excel spreadsheet we will have formulas and data values.  There are some situation where we need to delete only values keeping formula in the sheet. Excel provides a solution for this. Here is a quick step by step way to select only values in a cell or only formulas in a cell.

It is very useful when you are working with excel templates. Excel templates normally will have cells with calculated fields. We just need to update the values. The Excel formulas will automatically generate the calculated cells.  The easiest way to do is just selecting the cells wityh only values and remove it. Now you can enter values in the blank cells. This will protect you from accidental removal of formulas.

This method of selecting only values in excel worksheet works on all the versions of Excel.  You can use this method to select only formulas or error values in a cell, last cell etc.

Step to retain only Values and deleting non formula cells

Below steps will guide you on how to select only values in a excel worksheet.

• Select the range where you want to delete only values, retaining formulas. If you want to delete all non formula value cells on the entire worksheet, then select any single cell.
• Chose Home->Editing->Find&Select->Go to Special. This will take you to the Go to Special dialog box.
• In the Go To Special dialog box, select the Constants option and then select Numbers.
• Click OK. Now only nonformula numeric cells are selected.
• Press Delete to delete the values.

Note:

1. If you need to delete the value cells on a regular basis, then you can specify a name for the input cells. This can be achieved through defining the names to range. After completing Step 4 choose formula ->Defined Names-> Define Name to display the new Name box. Enter a name for the selected cells. Click OK to close the New Name Dialog box and create the name. After naming the input cells, you can select the named cells directly by using the Name box- the drop down list to the left of the Formula bar. Then press delete and you are done.
2. Shortcut key to reach Goto Special dialog box is Ctrl+G and Alt+S.