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.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2030 to 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 scram my formulas or be
  • 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
  • north west of cell a1 that little
  • triangle selects all cells and then ctrl
  • one 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 gonna go home
  • find and select and choose formulas you
  • don't even have to go in to go to
  • special there's a choice there now for
  • formulas it selects all the formula
  • cells and then we're gonna go back in
  • control one and lock these cells click
  • OK alright so now we've locked all the
  • formula cells and when 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 bucks 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 production they
  • password-protected it on purpose and
  • then six months later they've lost a
  • little sticky note and they wrote down
  • the password and then they have to go
  • pay money to unprotect it alright if
  • there's a bad person once again in your
  • spreadsheet they're gonna find a way to
  • break this don't rely on the password
  • all right 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 Auto
  • filter so turn those on all right 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 all right so I want to
  • press f2 so you're trying to change is
  • on protect sheet you might be requested
  • inner password if you 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 lock cells all
  • right so now I'm using the arrow keys
  • here I can cruise through the
  • spreadsheet without any hassles but what
  • 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
  • assess I can click there I can click
  • there I can't click there and 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 its right click one
  • two times and now I'm not where 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 and select formulas and then we'll
  • go back into control one and we will say
  • that the formula cells are hidden all
  • right click OK we'll protect the sheet
  • and we're going to allow them to select
  • locked cells and select the unlock 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 anyone 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
  • 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 her on
  • 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 okay
  • uh-huh but they beat me they change
  • those formulas to constant so if
  • someone's just trying to if they're
  • smart up into no 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 either
  • why while 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 what the use case is but at least
  • you understand locked versus hidden
  • alright so all of these tips are in this
  • book it is dripping with spicy tips
  • click that ion that's operating a corner
  • you can buy the entire book right now
  • it's gonna 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 bucks for the e-book 25
  • bucks for the pretty 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 cell's I use this little symbol up
  • here or control a and then control one
  • to display format cells go to protection
  • tab and uncheck locked home find and
  • select formulas and then control one
  • turn it back to locked and then review
  • protect worksheet don't bother with the
  • passwords the only people 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 log 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 okay I want
  • to thank you for stopping by we'll see
  • you next time for another net cast from
  • MrExcel

Download File

Download the sample file here: Podcast2032.xlsx

Title Photo: edusoft / Pixabay

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

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.