Protecting cells and formulas - Microsoft Excel Tutorial

- [Instructor] All right guys, time to talk about protection. I'm talking about workbook protection specifically. Now we've all heard this story. You've spent hours, days, maybe even weeks building this incredible, complex, intricate dashboard, or data model in Excel. And then one day, Gary the intern strolls in, he hard codes all your formulas, he deletes half the rows accidentally, and then saves it as a flat CSV file. And all that work you just did is gone down the drain. So in cases like that, I know it's a little bit extreme and it's a fictional tale, but it's not that uncommon. In cases like that you've got to look back and say, "You know what, I could blame Gary, "or I could have applied some workbook protection settings "to prevent this from happening." And that's exactly what we're going to talk about now. So when you look at the Format Cells dialogue box you're going to see a Protection tab that allows you to specify exactly how cells are going to behave once the worksheet is protected. So maybe you've got a simple report like this. In this case I'm looking at baseball stats from 2010 through 2015 for a specific player and I've created a data validation cell in row two so that users can change the player name and view their metrics right there in my report. So I want my users to be able to change the selection in the dropdown cell, but I don't want them to be able to edit, or in some cases even see the formulas in the other cells of the worksheet. So our protection tools are going to allow us to set those exact settings to do what we need. So if I select that data validation cell I can use the Control + 1 shortcut to launch the Format Cells dialogue box. And from there you can navigate to the sixth and final tab in that view called Protection. And in that Protection tab you'll see two different options: locked and hidden . Locked means that users can view the cell contents but they can't edit or change those contents. Now by default that will be set with a check mark, so all cells will be locked by default. Hidden means that users can't even see the underlying formulas or references when they select a cell. So it's almost like an additional layer of protection on top of simply locking the cell. Now what's important to keep in mind is that these protection settings have absolutely zero effect until you actually protect the sheet or protect the workbook. And this is kind of a nuance thing that a lot of people don't quite understand. Everything in Excel is going to be locked by default, but because your workbook isn't protected it has no impact on how you see or interact with those cells. The only time these protection settings come into play is when you protect that sheet or workbook. So what that means is that if you want to protect certain components of your workbook, your goal isn't to protect certain cells, it's to unprotect the ones that you want users to edit. It's kind of coming at it from the other side. I'm going to show you exactly what I mean when we go through the demo in just a second. So common use cases here, obviously preventing users from accidentally modifying sensitive content. (clear throat) Gary. Two, obscuring underlying formulas or cell references from view. Again, that's that deeper level of protection so that users can't even see the formula deriving the value. And then last but not least limiting user interaction to a specific set of inputs or cells. This is the way that I personally use cell protection most frequently. It's when I'm building things like models or dashboards where I want users to be able to edit certain cells but not others. So let's jump into our Excel workbook, actually practice modifying some of these settings. All right, now if you've been following along with the course up to this point, go ahead and open up your Excel Pro Tips workbook and head to the workbook Protection tab in the blue Productivity Tip section. Now, for those of you who have taken my "Database with Charts and Graphs" course this view should look pretty familiar. This is the baseball dashboard that we built in that course. And basically what we're doing is allowing users to select individual players and have this nice clean report update dynamically to show six years of metrics along with options to customize the views and the metrics that they're showing in this visual. So that's great but in its current state there's no protection applied whatsoever. So this is a pretty fragile worksheet. What I mean by that is that users, anyone with this file can see the formulas that are deriving these values. Not only that they can delete those values as well. So let's go ahead and undo that, we want to get our formula back. And what this means is that we need to apply some sort of protection to this sheet to prevent that from happening. So as a first pass maybe we head to our Review tab and just click Protect Sheet. Now, by default you won't have a password here. Let's not add one, you can if you want. But these default settings basically say that once we protect the sheet all that we want to allow users to do is select cells whether they're locked or unlocked. They can't edit them, they can only select them. So that sounds fine, let's press OK. And now if you tried to delete one of these values, you see this popup saying that you're trying to change a value on a protected sheet. So you've got to unprotect it and heads up, you might need a password to do that. And that's fine because I don't want people deleting these values. But it also means that a user can't even select a different player here. And the same goes with these data validation cells down here in row 14 as well. So as a next step what I need to do is unprotect the sheet and then target or isolate just the cells that I want users to continue to be able to edit. So for instance, cell T2, this dropdown here. let's right-click, format the cells, you can also use the Control + 1 shortcut there. And all we're going to do is uncheck that locked box and press OK. Do the same thing for these two cells here by the chart. Control + 1, unlocked, OK. Control + 1, unlocked, there we go. Now when we protect our sheet, keep the default settings, we still prevent users from editing the values but now the user can actually go ahead and select a different player or customize their chart with any of the cells that we've enabled for editing by unprotecting. So that's great but I'm not quite all the way there yet, because maybe I don't want users to be able to see the underlying functions producing these values here. So even though a user can't edit or delete these values they can still see the underlying formulas. So to fix that we need to add that additional layer of protection. So let's go ahead and unprotect again, select that whole range of cells containing those formula driven values. Control + 1, and all we're going to do is add a checkbox to the hidden box and that will obscure those formulas from view. So let's press OK, Protect Sheet, OK, and now check this out. If you try to click on any of these content cells here in my report, all you see is a blank formula bar. So you have no idea how these formulas are being derived, what source data they're coming from and so on. But those cells that are unprotected are still free to allow users to select certain players and the data in those underlying hidden cells or locked cells can still update accordingly. So there you go, this is a much better, a much more protected version of this report. And there you have it. That's your crash course on workbook protection settings.