How to Lock Cells 🔒in Excel to Protect your Formulas & Only Allow Input where Needed

Learn how to lock cells in Excel for creating foolproof templates! This tutorial is ideal for those who want to ensure data integrity in shared documents.

✅ What You'll Learn:
▪️ Setting up cell protection: A guide to using Excel's Review tab for sheet protection.
▪️ Password protection: How to add and remove passwords for extra security.
▪️ Formatting cells for protection: Detailed instructions on selecting and locking cells.
▪️ Customizing cell locking: Techniques for locking only specific cells or ranges.
▪️ Utilizing 'Find and Replace' for efficiency: A unique method to select cells based on color for protection.
▪️ Practical tips: Insights on protecting your formulas while allowing inputs in designated areas.

Ever created this perfect shared Excel template only to come back to a total disaster? Collaborating on shared spreadsheets can be painful. But it doesn't have to be that way! In this video I'll show you how you can lock and unlock any cell, or a whole range of cells, to keep your work protected. This way you'd be able to protect your formula cells and allow people to only input in specific ranges or cells. I'll also show you a cool Excel trick which allows you to multi-select cells based on their color and then lock those cells only.

00:00 How to protect cells in Excel
03:20 How to Multi-Select cells based on Color
05:29 Wrap Up

🚩Let’s connect on social:

This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#Excel

[giyah]

Not only did I learn how to lock/unlock cells, but also learned how to select only shaded cells. Another really easy and helpful tutorial. Thank you!

[wakelingchicken]

Hi Leila

My Top Tip if you're doing a lot of work locking / unlocking cells is to put the "Lock Cell" button into the quick access toolbar. It then acts as a visual indicator as to whether the highlighted cell is locked or unlocked and allows you to quickly change the setting without going into the Ctrl-1 formatting dialogue.

I hope that's helpful for a few people :)

Andy

[danielcarr]

Whilst I knew how to lock cells, I had no clue I could select cells using the find function by colour - what an amazing time saving tip - awesome 👏

[lorikmor]

I can’t like this enough. This is one of the most counterintuitive concepts in Excel. Thanks for breaking it down so simply.

[broderp]

I hope you are paid very well in some capacity for for your awesome methods are to the point, easy to understand and are exactly what I need. Thank you!

[TheLikkebaard]

Best video I saw ever explaining a(nother) confusing aspect of Excel, your explanation makes it so easy to follow!!

[radhaganesan]

Best practice would be
1) Unlock the entire sheet (Ctrl +1) -> protection-> unlock
2) Select the columns or cells which you wanted to lock (Ctrl +1) -> protection-> lock
3) Protect sheet now (would suggest to always use a password for security)

This works!!

[rlibby]

I abandoned hope for good Excel tutorials many years ago, they are usually the most terrible and unreliable videos out there, but you, you really did it.

[DinoDelight]

I use locked cells loads, but always untick the "select lock cell option" that way users can't even select the locked cells this avoids that annoying error pop up.
And if you use filters, make sure that option is unticked that way users can use the filter even though the cell will be locked

[TigerTedd]

My top tip for this, instead of the find and replace option, is to use Cell Styles to unlock cells.

There’s a cell style called Input. Unfortunately I think MS have dropped the ball slightly on this as it would make sense to have this cell style default as unlocked. But it doesn’t, so you have to right click on it and modify, then tick that you want to include protection in the cell style, and then got to format, and unlock the style in the same way, you can also change its colour here.

In this way, when you’re creating your spreadsheet, whenever you come across a cell you know should be unlocked, you can just give it the ‘input’ cell style, rather than making it blue, or yellow or whatever, and it will automatically be unlocked.

You can also then use cell styles to quickly change the colour or borders of all your input cells if you need to.

I’ve had it many times where I’ve made a big spreadsheet with lots of fairly random input cells, and I’ve lost track of what’s an input cell and what isn’t. And you have to go back through later and unlock them all. Best practice is to highlight then in some colour, even if that’s just default yellow for now. But using this method means you’re highlighting them, unlocking them and leaving the door open to quickly recolour them later if you want to.

I just wanted to share that, as I was very pleased with myself when I worked it out. I hope that helps someone.

[TarunSharma-llge]

I make excel calculators a lot and I use this feature but this color coding will save a lot of my time as I used to manually untick the 'locked' for each cell. Thanks for this video.

[silviopontesx]

Thank you for teaching us; I watched many of your videos, I admire your sensitivity and the simplicity and fluidity of how you delve into the topic! What a monument to knowledge! Thank you so much!

[JayBee-hbpm]

Everything you post about Excel is so relevant to my job role. I just love how you come up with things that make our lives so much easier Leila. Thank you for sharing yet another great tip

[DeltaSanjula]

Working in a rural Australia has many challenges, especially when finding quality learning resources. Subscribing to your channel is like access to the ultimate office encyclopedia. Thank you for unscrambling the chaos that sometimes is excel!!

[vincentcollin]

We can add that when protecting the sheet, we can uncheck the box "Select locked cells".
And therefore leave only the unlocked cells accessible.😉
Thank you for this lesson.

[michaelmwakawele]

I have learnt a lot from your presentations. Your explanations are usually clear and easy to follow. However, sometimes I have questions. Do you mind having a one on one question and answer?

[michaelcorvin]

Thank you for explaining things in a very logical and easy manner! I love your tutorial videos on EXCEL!

[RussellWard]

Nicely done! You have a teaching gift - making the complex simple. Perhaps you would consider presenting the process a little slower in your next video as I am an Excel rookie.

[sulemanhassanali]

You are saving my days! Additional learning how to select specific formatting.

[laurituttle]

So, I did have some issues. I had to format the cells BEFORE I locked them. Selecting cells that you want to be locked, right click - format cells - lock (or unlock cells or block of cells). When all of your cells are formatted the way you want, THEN protect the sheet. This will have the cells you want locked, locked... and the ones you want unlocked, unlocked (or where ONLY cells can be adjustable)! Thanks for the tutorial!