Quickly clear Excel spreadsheet values but keep your text and formulas

Quickly clear Excel spreadsheet values but keep your text and formulas

  • Comments 6
  • Likes

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
  • Excellent instructions so easy...thank you

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

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

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

  • 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.

  • 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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment