Where is it Wednesday: Where did they move Tools, Protection, Protect Sheet? In Episode 863, I will show you how to protect only the formula cells in Excel 2007. While this was complicated in Excel 2003, it is just a bit more complicated in Excel 2007 when you can't find the final command, Protect Sheet.
Transcript of the video:
Hey, alright, welcome back to the MrExcel netcast.
It's "Where Is It Wednesday".
Thanks to George Wood for our "Where Is It Wednesday" theme.
I was doing a seminar and someone said, "How about Protect Sheet?
We can't find Protect Sheet in the new Excel." Let's talk about how we would protect all of the formula cells in this worksheet.
First thing we want to do is, we want to select every single cell in the worksheet, so that's the same as Excel 2003-- we click the icon above and to the left of Cell A1, and then we want to go into Format Cells.
Now, you have a couple of ways to do this: You can use Ctrl+1-- that worked in any version; or my favorite is here on the Home tab-- these tiny little icons, in the lower right-hand corner of these three sections, will take us right into the old Format cells dialog box, we go to Protection, and unlock every single cell in the workbook.
Click OK.
Now, from within the selected cells, I want to select only the formulas that used to be.
Edit, go to Special, and then formulas.
Well, now we have to use Home, Find and Select, go to Special, and then Formulas.
And what that will do is, that will select only the formula cells.
Now, I want to go back into Format Cells-- so, again, that's Ctrl+1-- and lock the formula cells, click OK.
Alright, so, now all of that leads up to the question.
The person said, "I now cannot find Protect Sheet; it used to be under Tools, Protection, Protect Sheet-- well, there is no Tools to have anymore so we can't find that." Well, it turns out-- and this is really strange when we finally found it-- it is on the Home tab, and for some reason, it's under Format.
We go to Format, and then choose Protect Sheet, click OK.
And now what happens is someone can change any of the hard-coded cells; but if they go in and try and change one of the formula cells, it's now protected and read-only.
So, of course, the feature is still there.
Protecting worksheets is one of those important things, and we need it to protect people from changing cells we don't want to change.
Unfortunately, just made it fairly difficult to figure out where it is, hiding it under the Format drop-down on the Home tab.
So there you have it-- how to protect only the formula cells in a worksheet.
We'll see you next time for another netcast from MrExcel.
[ music ]
It's "Where Is It Wednesday".
Thanks to George Wood for our "Where Is It Wednesday" theme.
I was doing a seminar and someone said, "How about Protect Sheet?
We can't find Protect Sheet in the new Excel." Let's talk about how we would protect all of the formula cells in this worksheet.
First thing we want to do is, we want to select every single cell in the worksheet, so that's the same as Excel 2003-- we click the icon above and to the left of Cell A1, and then we want to go into Format Cells.
Now, you have a couple of ways to do this: You can use Ctrl+1-- that worked in any version; or my favorite is here on the Home tab-- these tiny little icons, in the lower right-hand corner of these three sections, will take us right into the old Format cells dialog box, we go to Protection, and unlock every single cell in the workbook.
Click OK.
Now, from within the selected cells, I want to select only the formulas that used to be.
Edit, go to Special, and then formulas.
Well, now we have to use Home, Find and Select, go to Special, and then Formulas.
And what that will do is, that will select only the formula cells.
Now, I want to go back into Format Cells-- so, again, that's Ctrl+1-- and lock the formula cells, click OK.
Alright, so, now all of that leads up to the question.
The person said, "I now cannot find Protect Sheet; it used to be under Tools, Protection, Protect Sheet-- well, there is no Tools to have anymore so we can't find that." Well, it turns out-- and this is really strange when we finally found it-- it is on the Home tab, and for some reason, it's under Format.
We go to Format, and then choose Protect Sheet, click OK.
And now what happens is someone can change any of the hard-coded cells; but if they go in and try and change one of the formula cells, it's now protected and read-only.
So, of course, the feature is still there.
Protecting worksheets is one of those important things, and we need it to protect people from changing cells we don't want to change.
Unfortunately, just made it fairly difficult to figure out where it is, hiding it under the Format drop-down on the Home tab.
So there you have it-- how to protect only the formula cells in a worksheet.
We'll see you next time for another netcast from MrExcel.
[ music ]
