Quickly clear Excel spreadsheet values but keep your text and formulas


If you have an Excel spreadsheet that you want to re-use without all of the data (say, to share with colleagues or use as a generic input form), you’ll want to keep all of the formulas and text fields intact but clear the input values.

The quickest way to do this is with the Go to Special command, which is tucked under the Find & Select button on your Home tab in Excel 2007 and Excel 2010:

Go to Special

Now select Constants and then uncheck everything except Numbers in the list below it:

Select Constants

Now just press the Delete key to remove all of the values. Check that only the data you wanted was deleted – you can always Undo (Ctrl-Z) if it’s not right and try again.

If you do want to remove Text fields, you could simply leave this box checked. You’ll also no doubt note the other options available in the versatile dialog box displayed above – there’s a lot of power in Go To Special command!

Suzanne

Comments (6)

  1. Anonymous says:

    Excellent, thank you so much.  Even though I already deleted EVERYTHING from my new sheet, I can just go back to the original and do it over again.

    To Diane, hope this answers your question.  Save the sheet you want and name it, say, peanut butter.  It is now safely tucked away.  With it still open, click on Save as… and this time save it as, say, jelly.  Now, with jelly open, follow the instructions above and you will have another blank spread sheet ready for your new data.

    Now I am hungry and must go make a sandwich.  Hope I helped.

  2. Erin says:

    Excellent instructions so easy…thank you

  3. Bruce says:

    Even the Excel resource in our dept didn't know this one. I got to teach him something. Thanks

  4. paulclk430 says:

    Thanks, that was easy!  Saved a lot of needless reenter a/o copy paste of formulas (and time) on a big spreadsheet!

  5. Diane says:

    What if you want to save a copy of that sheet with the data, and clear it for the next use?

  6. Jack R says:

    Excellent, thank you so much.  Even though I already deleted EVERYTHING from my new sheet, I can just go back to the original and do it over again.

    To Diane, hope this answers your question.  Save the sheet you want and name it, say, peanut butter.  It is now safely tucked away.  With it still open, click on Save as… and this time save it as, say, jelly.  Now, with jelly open, follow the instructions above and you will have another blank spread sheet ready for your new data.

    Now I am hungry and must go make a sandwich.  Hope I helped.