MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Protect Formula Cells

September 21, 2017 - by Bill Jelen

Protect Formula Cells

How can you protect only the Excel cells with formulas? This article will show you some easy ways.

Worksheet protection in Excel is a little strange to use. Using the steps below, you can quickly protect just the formula cells in your worksheet.

It seems unusual, but all 16 billion cells on the worksheet start out with their Locked property set to True. You need to unlock all of the cells first.

  1. Select all cells by using the icon above and to the left of cell A1.
  2. Press Ctrl + 1 (that is the number 1) to open the Format Cells dialog.
  3. In the Format Cells dialog, go to the Protection tab. Uncheck the Locked status. Click OK.

    Format Cells Dialog
    Format Cells Dialog

While all cells are still selected, select Home, Find & Select, Formulas.

At this point, only the formula cells are selected. Press Ctrl + 1 again to display Format Cells. On the Protection tab, choose Locked to lock all of the formula cells.

Cell Protection Options
Cell Protection Options

But locking cells does nothing until you protect the worksheet. On the Review tab, choose Protect Sheet. In the Protect Sheet dialog, choose if you want people to be able to select your formula cells or not. Don’t bother putting in a password. Passwords are easily broken and easily lost. You will find yourself paying $39 to the Estonians who sell the Office password-cracking software.

Protect Sheet Dialog
Protect Sheet Dialog

Watch Video

  • All 16 billion cells on the sheet start out Locked
  • First, unlock all cells.
  • Select all cells using triangle northwest of A1
  • Ctrl + 1 to display Format Cells
  • Go to Protection Tab and uncheck Locked
  • Home, Find & Select, Formulas, Ctrl + 1, Locked
  • Review, Protect worksheet
  • Don't bother with a password. Easy to lose. Easy to break.
  • The only person who wins with a password are the Estonians who get $39
  • Scroll through the Protect Sheet dialog: you can choose to allow sorting, filtering
  • Preventing people from seeing your formulas
  • Choice 1: Locked, and uncheck Selected Locked Cells. Problem: strange to navigate with keys
  • Choice 2: Locked, Hidden, and check Select Locked Cells. Easier to navigate.

Video Transcript

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!

Download File

Download the sample file here: Podcast2032.xlsx

Title Photo: edusoft / Pixabay

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.