Dennis demonstrates how to prevent over-writing of formula cells by locking cells and activating worksheet protection.
- [Instructor] When you protect a sheet in Excel you protect a specific worksheet just one at a time. In this worksheet here, you're worried about the fact that not only can different people access this file, make editing changes to it, someone might accidentally get rid of a formula. There are formulas in Column E. One of the protection schemes available is protect sheet. It's found on the Review tab. There's this term right there. Prevent unwanted changes from others by limiting their ability to edit.
Before using this, we want to point out a fact that you might not be aware of. Every cell in an Excel worksheet has an attribute that it is either locked, or unlocked. Right click any cell, go to Format Cells. Protection tab. It's likely to be locked unless someone has unlocked it. Now, if we want to allow changes here and yet prevent them in certain areas, certain columns, or rows, whatever, we first click in the upper left corner. Let's unlock all cells and then come back and lock the ones where we don't want changes to be made.
By clicking the upper left hand corner, we've selected all cells. We can get to format cells quickly with Control one. Go to the Protection tab if it's not already selected. Let's uncheck Locked. Make sure that looks white, and not gray. And no check mark in it. Click Okay. Then, let's prevent Column E. And as I hold down the Control key, Column G, Column H. You don't want people making changes there. Then, right click anywhere within here or press Control one.
And now we want to lock these cells. Click Okay. We're ready to activate the feature called Protect Sheet. Let's click it. And there's a huge list of unchecked boxes here. Meaning, if we want other users to be able to format the columns, the rows, insert columns, insert rows, a lot of these you want to prevent. As long as you leave them unchecked they're prevented. So you might thing that out a little bit. At a minimum, if we want others to be able to make changes to the unlocked cells the second box, select Unlocked Cells Must be Allowed.
We could possibly just turn off the button for Select Locked Cells meaning someone will not be able to click on a cell that's locked, or tab into it anyway. Provide a password. Provide it again. And if we save the workbook now and others are about to use it any time others can certainly make changes here. And here, if someone changes, for example, I'll change that to a four the column to the right reacts. But what if someone says, well, I know this should have been changed and I'm trying to click here.
I can't click on E three. I'm pressing the right arrow here. Escape, right arrow. It jumps right over. I'm pressing right arrow it leaps right over. So you couldn't have a situation, for example, where someone says, I think I'll change my friend's compensation on here. You can't even click in it. You recall that earlier I'd made the selection to not allow selection of locked cells. These are locked cells, Column G, Column H and Column E. So even though we can cause changes in Column E by changing the dates in Column D we can't click there, we can't tap into there and make changes.
And if I were to right click on Column D and try and hide it, or insert a column to the left and delete it, none of these are selectable. Although some of them could be. If at some point, when this is back in your hands, if you want to make changes you can unprotect the sheet. And of course, you'd have to know the password, and you would. And now when it's in this state we can click on these cells, we could make changes here, we could change the formulas, we could adjust the compensation amounts while it's in this state. So I think you can see the benefit of this.
Sometimes you do this strictly for yourself, I don't want to access family type over the formulas here. I want them to work, that's fine. We could unlock all cells, lock Column E, turn on Protect Sheet. Still get a lot done. And when we do that, by the way, protect sheet, we could allow many of these features here. It's a huge set. But I think ideally, the feature is designed so that when certain people are using this you want to make sure that changes don't occur in areas that don't get created by people typing over formulas that's the main thrust of this particular movie.
Dennis begins with the basics—how to display data so that errors can be easily spotted. Next, he offers handy tips to ensure data is entered correctly the first time, using the AutoFill feature and using AutoCorrect shortcut codes for frequently used entries. Dennis provides easy ways to validate your data, which is particularly helpful when multiple team members are contributing to the same spreadsheet. This includes restricting the data that can appear in a spreadsheet by setting value limits, pre-populating data with drop-down lists, and other methods. He also shows how to avoid mistakes in formulas, how to hide data that doesn't need to be seen, and how to use workbook protection to prevent errors, and more.
- Streamlining data entry steps
- Using Excel shortcuts and automation features
- Protecting worksheets and workbooks
- Validating data
- Basing entries on formulas
- Detecting errors in formulas
- Using Range Names
- Finding mistakes in large, complex spreadsheets