Freigeben über

Quickly clear Excel spreadsheet values but keep your text and formulas

  • Artikel

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

  • Anonymous
    January 01, 2003
    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.

  • Anonymous
    February 01, 2012
    Excellent instructions so easy...thank you

  • Anonymous
    February 07, 2013
    Even the Excel resource in our dept didn't know this one. I got to teach him something. Thanks

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

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

  • Anonymous
    September 24, 2013
    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.