Home

Protecting cells in EXCEL

%3CLINGO-SUB%20id%3D%22lingo-sub-715343%22%20slang%3D%22en-US%22%3EProtecting%20cells%20in%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-715343%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20a%20spreadsheet%20for%20others%20to%20use%20with%20complicated%20manipulation%20and%20formulae.%20My%20desire%20is%20to%20protect%20the%20part%20of%20the%20cells%20so%20things%20can't%20be%20changed%20and%20leave%20the%20rest%20for%20user%20input.%20I%20did%20lock%20the%20desired%20cells%20and%20then%20protect%20the%20sheet%20but%20then%20all%20cells%20are%20unavailable%20for%20user%20input%2C%20rendering%20the%20complete%20sheet%20useless.%20Please%20direct%20me%20toward%20leaving%20the%20user%20input%20portion%20available.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-715343%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-715351%22%20slang%3D%22en-US%22%3ERe%3A%20Protecting%20cells%20in%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-715351%22%20slang%3D%22en-US%22%3ELock%20the%20columns%20of%20your%20formula%20cells%20and%20unlock%20the%20columns%20of%20your%20input%20cells%20before%20you%20apply%20sheet%20protection%20with%20password.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-715432%22%20slang%3D%22en-US%22%3ERe%3A%20Protecting%20cells%20in%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-715432%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364984%22%20target%3D%22_blank%22%3E%40Pmuts%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20key%20for%20you%20is%20unlocking%20the%20user-input%20cells%20before%20protecting%20the%20worksheet.%3C%2FP%3E%3CP%3EThe%20settings%20are%20on%20the%20Home%20Ribbon%20tab%2C%20Cells%20group%2C%20then%20either%20Lock%20Cells%20or%20Format%20Cells%20...%3C%2FP%3E%3CP%3EThe%20latter%20will%20allow%20you%20to%20hide%20the%20formulae%20as%20well%20as%20protecting%20them%20from%20change.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-716736%22%20slang%3D%22en-US%22%3ERe%3A%20Protecting%20cells%20in%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-716736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364984%22%20target%3D%22_blank%22%3E%40Pmuts%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20an%20extra%20tip%20to%20the%20excellent%20replies%20already%20mentioned%2C%3C%2FP%3E%3CP%3EThe%20concept%20of%20protection%20in%20Excel%20is%20reversed%2C%20which%20means%2C%20when%20you%20click%20on%20Protect%20Sheet%20on%20the%20Review%20Tab%20(read%20the%20wording)%20it%20says%20%22%3CEM%3EProtect%20Sheet%20and%20content%20of%20locked%20Cells%3C%2FEM%3E.%22%3C%2FP%3E%3CP%3EBy%20default%20the%2017%2C179%2C869%20cells%20in%20any%20sheet%20are%20locked%20%26gt%3B%26gt%3B%20so%20all%20of%20them%20become%20protected.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20when%20I%20said%20the%20concept%20is%20reversed%2C%20I%20mean%20in%20Excel%20you%20specify%20what%20%3CSTRONG%3ENot%20to%20protect%3C%2FSTRONG%3E%20by%20going%20to%20the%20Format%20Cell%20dialog%20Box%20CTRL%20%2B%201%20%26gt%3B%26gt%3B%20Protection%20Tab%20%26gt%3B%26gt%3B%20uncheck%20%3CSTRONG%3ELocked%3C%2FSTRONG%3E%20for%20the%20cell%20where%20you%20want%20to%20enable%20editing.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854756%22%20slang%3D%22en-US%22%3ERe%3A%20Protecting%20cells%20in%20EXCEL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854756%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20thanks%20for%20your%20suggestions.%20I've%20another%20question%3A%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20created%20several%20excel%20worksheets%20in%20various%20workbooks%20and%20I%20need%20to%20lock%20just%20some%20cells%20with%20specific%20formats%20(e.g.%20only%20cells%20in%20green%20for%20all%20the%20sheets%20and%20workbooks%20I%20created).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20know%20a%20quick%20way%20%2F%20method%20to%20lock%20all%20such%20cells%20thanks%20to%20their%20specific%20format%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGaetano%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Pmuts
Occasional Visitor

I have created a spreadsheet for others to use with complicated manipulation and formulae. My desire is to protect the part of the cells so things can't be changed and leave the rest for user input. I did lock the desired cells and then protect the sheet but then all cells are unavailable for user input, rendering the complete sheet useless. Please direct me toward leaving the user input portion available.

4 Replies
Lock the columns of your formula cells and unlock the columns of your input cells before you apply sheet protection with password.

@Pmuts 

The key for you is unlocking the user-input cells before protecting the worksheet.

The settings are on the Home Ribbon tab, Cells group, then either Lock Cells or Format Cells ...

The latter will allow you to hide the formulae as well as protecting them from change.

@Pmuts 

Just an extra tip to the excellent replies already mentioned,

The concept of protection in Excel is reversed, which means, when you click on Protect Sheet on the Review Tab (read the wording) it says "Protect Sheet and content of locked Cells."

By default the 17,179,869 cells in any sheet are locked >> so all of them become protected. 

So when I said the concept is reversed, I mean in Excel you specify what Not to protect by going to the Format Cell dialog Box CTRL + 1 >> Protection Tab >> uncheck Locked for the cell where you want to enable editing.

Thanks

Nabil Mourad

@nabilmourad 

Hi, thanks for your suggestions. I've another question: 

I've created several excel worksheets in various workbooks and I need to lock just some cells with specific formats (e.g. only cells in green for all the sheets and workbooks I created).

 

Do you know a quick way / method to lock all such cells thanks to their specific format?

 

thank you,

 

Gaetano 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies