How do I clear cells of data but leave teh formulas in Excel

Jan 29, 2013 6:42AM PST

iMac 0s10.6.8 Office, Microsoft Excel 2011 - I have a spreadsheet that I used last year for tax information. I would like to use the same spreadsheet this year but I can not clear the cells without removing the formulas as well. Does anyone know what I need to do.. I have followed the directions that I have found and tried all ways but still no success.

Discussion is locked

Reply to: How do I clear cells of data but leave teh formulas in Excel
PLEASE NOTE: Do not post advertisements, offensive materials, profanity, or personal attacks. Please remember to be considerate of other members. If you are new to the CNET Forums, please read our CNET Forums FAQ. All submitted content is subject to our Terms of Use.
Reporting: How do I clear cells of data but leave teh formulas in Excel
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
- Collapse -
Let's say the formula is as such.
Jan 29, 2013 6:50AM PST


This would be in cell a1. To clear the values I clear just cells b1 and c1. Of course that may result in some ERR! being shown in a1 but that's proper.

- Collapse -
Re: clearing
Jan 29, 2013 7:10AM PST

How to do this:
1. Protect all cells with formulas, so they won't be deleted.
2. Switch protection for the worksheet on.
3. Delete all.
The settings stay on, so next year it's just step 3 (unless the tax laws or your situation and thus the formulas change).

It makes sense to save a copy of the original worksheet before you do this. Just in case you erase a formula because you forgot to protect it.


- Collapse -
Re: clearing
Jan 13, 2016 4:07AM PST

I'm using Office 2016 . . . this doesn't seem to be working. With the worksheet protected, I can't get past the warning.

- Collapse -
Which is proper for a protected sheet.
Jan 13, 2016 7:55AM PST

You either unprotect or clear what is not protected.

CNET Forums

Forum Info