Forum Discussion

Copper Contributor

Jan 07, 2019

Solved

Can I delete a cell entry, but lock a cell formula so it's retained?

Hi I use an Excel work sheet as a yearly accounts ledger / accounts book. The worksheet has 12 tables, each table being a month of the year. Column headings in each table include 'Amount ex VAT',...

excel

Reboot

Copper Contributor

Jan 08, 2019

Please find attached an example (I hope I've managed to attach it OK).

I'm not sure but I think we've been at cross purposes. You can see in the sheet that there are formulas in the 'VAT', 'Total Incl VAT' and 'Totals' columns/rows. If I delete an entry in the 'Amount Ex VAT' column, there's not a problem because the cells in that column contain no formulas. However, if I delete an entry in cells containing formulas, both the entry (e.g. £20 in the VAT column) and the formula are deleted; this is a problem because when busy, it's easy to accidentally delete the entry and formula when just the entry was meant to be deleted. Therefore, can I lock (for want of a better word) cells with formulas so that the entry can be deleted while leaving the cell formula untouched/in place?

Thanks

Example.xlsx10 KB

Wyn Hopkins

MVP

Jan 08, 2019

Thanks for the attachment

You can lock cells from accidental deletion.

The technique is to actually unlock the cells you want to type in and then protect the worksheet

Highlight the Amount cells and right click and choose Format Cells, then go to the Protection tab and uncheck the Locked box

Then go to Review > Protect Sheet and you will only be able to edit the "unlocked" cells

HelpMeWithStuff
Copper Contributor
Apr 05, 2024
I followed these steps however the unlocked cells when edited and a user clicks delete the formula deletes. How can I protect the formula from a user deleting it on accident when they're trying to only delete their work? Wyn Hopkins

The photo below shows where the green highlight is at, the formula that was present is deleted and where the $ signs are the formula is still present.