Cells with formulas

Resolved · High Priority · Version 2016

Tue 23 Aug 2016, 15:48 Delegate Federica said...

Federica has attended:
Excel Intermediate course

Cells with formulas

can I block a cell with formulas in a way that the formula cannot be deleted?

Tue 23 Aug 2016, 15:58 Trainer Richard said...

RE: cells with formulas

Hi Federica,

The easiest way to do this is not with a formula but to turn on the cell protection/locking.

On the Review Tab click Protect Sheet
Type in a Password
Retype Password
Now try deleting the cells on the page, it should prevent you from doing so.

This uses the default cell status that Excel has and locks all formulas/contents in all cells on that page.

Try that first and see if that's what you were looking to achieve.

To undo, go to the Review Tab, click on Unprotect sheet and type in your password.

Protection is covered on our Excel Intermediate course if you need more advanced functionality.

https://www.stl-training.co.uk/excel-intermediate-courses.php

Kind regards

Richard Bailey
Microsoft Certified Trainer

Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 23 Aug 2016, 15:58 Trainer Richard said...

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Wed 24 Aug 2016, 16:20 Delegate Federica said...

RE: cells with formulas

Hi
Probably my message wasn't clear enough - I'm sorry.
I wanted to know if it is possible to protect cells that contains formulas so that if I give a spreadsheet to someone to fill in with data, they don't delete the underlying formula in a cell but can still work on the spreadsheet.
I hope it is now clearer what I am trying to achieve.
Thanks

Fri 26 Aug 2016, 09:43 Trainer Richard said...

RE: cells with formulas

Hi

Now I understand.

You can have restrictions placed on the cell called data validation but that doesn't stop people deleting the formula.

If you can type into a cell you can also delete the formula. That's a limitation of Excel unfortunately.

Kind regards

Richard Bailey
Microsoft Certified Trainer

Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 2 Sep 2016: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Conditional Formatting in Excel 2010

If you have lots of data in a spreadsheet, you may find that it is easier to read if you highlight some of the values. This is Conditional Formatting and here's how to use it:

1) Select the data you wish to apply the format to and click Conditional Formatting
2) A list of options will then appear, from this list, choose the format you wish to display e.g. find all cells with a value less than 0
3) Excel will then highlight all of these cells

To remove this: select the highlighted cells, click the drop down on the Conditional Formatting icon and select Clear Rules from selected cells.

View all Excel hints and tips