Preventing formulas being pasted over while editing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am a semi experienced excel user however I find when entering data for
monthly reports, I seem to be deleting formulas when editing data and
therefore my spreadsheets don't calculate correctly. I have heard there is a
button that I can turn on and off when editing worksheets that will protect
my formulas. Where is that device and or how do I enter data so that I won't
lose my formulas? Thanks
 
Presume you're talking about accidental overwrite of formula cells with
manual inputs ? (A cell can only hold either a value or a formula at any one
time)

To minimize accidental overwrites of formula cells, we can apply sheet
protection with data cells unlocked to allow inputs

Try this:

Select* the data input cells, say, select B2:B10
Click Format > Cells > Protection tab > Uncheck "Locked" > OK
Then click Tools > Protection > Protect Sheet > Enter password (optional) >
OK

*We can multi-select data cells by holding down the CTRL key while
selecting, and unlock all at one go
 
In addition to turning on protection and for use when you have it
turned off you could also color code input cells and calculated cells.
Color Coding Cells for Usage (#colorcoding)
http://www.mvps.org/dmcritchie/excel/colors.htm#colorcoding

Basically my suggestion is:
white or pale color background for input areas
formula results with another text and/or background color

Conditional Formatting may add additional color to your mix.
 
Back
Top