Protect Formula Cells

Learn Excel from MrExcel podcast, episode 2030 - Protect All Formula Cells!

I'll be podcasting this entire book, click that “i” in the top-right hand corner, you can get to the playlist and play them all!

Alright, today we want to talk about Protect Sheet, if I want to protect all the formula cells to keep people from A: scramble my formulas, or B: seeing my formulas, there are tools for this. But the really funny thing is, there's 16 billion cells on this spreadsheet, and they all start out, every single spreadsheet starts out with the locked property as TRUE, so we have to select all cells. Now you can use Ctrl+A for that, or click up here, I call it northwest of cell A1, that little triangle selects all cells, and then Ctrl+1 gets us to Format Cells. Go to the Protection tab, uncheck Locked, unlock everything first, it's really weird, we're trying to protect things, first thing we have to do is unlock everything. Now we still have everything selected, we're going to go Home, Find & Select, and choose Formulas. You don't even have to go into Go To Special, there's a choice there now for Formulas, it selects all the formula, cells and then we're going to go back in, Ctrl+1, and lock these cells, click OK. Alright, so now we've locked all the formula cells, and then we go to Review, Protect Sheet, alright, let's talk about this for a little bit.

Password: I never password-protect my sheets, A: it's really easy to lose it, B: it's really easy for someone else to break it. Alright, don't rely on this password. The only people who win when you password protect at worksheet are the Estonians who make the password cracking software, they get $39 when you desperately have to unlock this sheet. If we surveyed them and they surveyed their customers, I bet the majority are people who are honest people who accidentally password-protected the sheet and then lost the password. No, they didn't accidentally password protect it, they password-protected it on purpose, and then six months later, they've lost a little sticky note where they wrote down the password, and then they have to go pay money to unprotect it. Alright, if there's a password once again in your spreadsheet, they're going to find a way to break this, don't rely on the password.

Alright now, see, it starts out here, where they're allowed to Select locked cells and Select unlocked cells, but there's a whole bunch of other choices. Like, you may want to protect your formulas, but still allow them to use filters, or to sort. Alright, so take a look through here and decide, you know, what you're trying to do, it might be fine to sort this data or to use AutoFilter, so turn those on alright. So now, in this case, I'm allowing them to select locked cells and select the unlock cells, alright, and so we can still come in, we can still see the formula, we just can't edit the formula. Alright, so I’m going to press F2, “The cell you're trying to change is on protected sheet. You might be requested to enter a password for you to unprotect.” but in this case we won't. Now if you're somehow trying to protect these formula cells, there's a couple of ways to go, the first way is to protect the sheet and not allow them to select the locked cells. Alright, so now I'm using the arrow keys here, I can cruise through the spreadsheet without any hassles. But when I get to a cell, if I go right from here, see it jumps over to column F, it won't let me select any of those, or if I use the mouse, it just will not click on the cells. I can click there, I can click there, but I can't click there.

I’m not a big fan of that, because you know, I'd like to use the arrow keys to navigate around. And you know here, mentally, if I want to get over to Promo, it’s right-click 2 times and now I'm not where I expect to be. Alright, so the other thing we can do here is: let's select all the formula cells again, so select all cells, Home, Find & Select, Formulas, and then we'll go back into Ctrl+1, and we will say that the formula cells are hidden! Alright, click OK, we'll protect the sheet, and we're going to allow them to select locked cells and select the unlocked cells, click OK. Alright see, now what happens is, look up there in the formula bar, so these are constants, these aren't formulas, and as I move, I can see what's up there. But then when I get over to the formulas, I mean these are amazing, super-secret formulas that I don't want anyone to steal, I'm being sarcastic, of course. I can select the cells, I can click on a cell, I just can't see the formula alright.

And then, of course, you know, my job is to try and break things, so I said “Oh hey, I can get around this, there's a whole bunch of unprotected cells here.” Let's just copy this, Ctrl+C, and then come down here and use Paste Special, and say that I want to paste the formulas, that'll get me the formulas without the Hidden, click OK. Ah, but they beat me, they change those formulas to constant, so if someone's just trying to- if they're smart enough to Paste Special and they're trying to steal your formula, you know, that might keep them out. You know, I don't know what the scenario is or why you're trying to protect people from themselves, or you're giving this to people don't know Excel, or you're afraid someone's going to try and change your formula. I'm not sure what the use case is, but at least you understand Locked vs Hidden. Alright so, all of these tips are in this book, it is dripping with spicy tips, click that “i” on the top-right hand corner, you can buy the entire book right now. It's going to take me months to get through the entire book, but I just think about all the time-saving things that you can uncover now, $10 for the e-book, $25 for the print book.

Alright, recap: all 16 billion cells on the spreadsheet start out locked, before you're going to protect anything you have to unlock all the cells. I use this little symbol up here or Ctrl+A, and then Ctrl+1 to display Format Cells, go to Protection tab and uncheck Locked. Home, Find & Select, Formulas, and then Ctrl+1, turn it back to Locked, and then Review, Protect Worksheet. Don't bother with the passwords, the only people to win are the Estonians, when you're protecting, you can choose to allow sorting and filtering. And now, if you're trying to prevent people from seeing your formulas, you can either go with Locked, and then, as you protect, uncheck Select locked cells, but then it's weird to navigate. Or you can go with Locked and Hidden, and then allow them to select locked cells, they just don't see the formula up in the formula bar, much easier to navigate.

Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!