Excel Magic Trick 807:Protect Sheet To Allow Data Enter Only In Cells With Raw Data

1. See how to use the Protect Sheet feature to allow people to only select cells that have been unlocked.
2. Learn how to unlock cells using Format Cells Dialog Box
3. Perfect for a Form where people enter raw data.

[PEACHYDAR]

thanks! I wanted a way to protect those cells with formulas from ME!! I know on occasion I have type in a cell where a formula was in error!! Great way to protect the sheet from just ME!!! Love it!

[MalcolmXtra]

Love how the way you just get to the point.

[vibrantbutterflybeau]

Awesome! Thank you! This was EXACTLY what I was searching for! And so simple! You're fantastic!!! :D

[floiirr]

You are the God Of Excel. Thanks  for the tips !

[excelisfun]

@PEACHYDAR1, I am glad that the videos helps!

[DrewLivin]

Damn I love excel! the more I learn, the more I love =D

[MariusMusty]

You should specify that the method is reversible and in order to have unique protection you also have to set a password.

[jonpitt]

Your magic tricks are fantastic and just that, Magic. That said, how do I ensure password protected sheets with unlocked cells can't be broken by coding provided on the web? I only ask as I have set up a spreadsheet with unlocked cells for others to populate, password protected the sheet to avoid others from altering specific data, however they have used coding from the web to break the password protection. Please provide a Magic Trick video that shows how to password protect an excel doc that can't be broken by any coding if possible. Thanks in advance.

[azaanali]

thank you for this, , ,, i tried protect sheet for an excel table, but now im unable to add more lines to the table, ( tabing from the last cell, only goes to the first unlock cell rather than adding a new line to the table. any help on this ???

[etologen]

Great video! Very helpful! I am doing something similar to a table with 12 columns - three columns with formulas are locked, and the rest unlocked. BUT - the thing is, even if I check ALL the users rights when I protect the sheet they still cannot add rows to the table.

I could just make a bunch of rows for my users, but that messes up their chart. Any ideas?? Thank you :)

[danieloladele]

Helpful video, but how can I restrict cursor movement to a particular range of cells?

[ankitgarg]

Hi,
I have a Query.

In case I have grouped columns/row and i want to permit the user whom i am sharing the workbook with to ungroup and view the columns/rows.

Is there a method by which i can protect the sheet and yet permit them to ungroup the columns/row.

Thanks

[RainyLake]

Is there a way to do this with merged cells

[heathsanders]

How can you access font color on the unlock cells when sheet is use "SumRed" so i cant change color on the open cells...Thanks Mate

[danellekoalska]

Is there a way to protect the formatting from people that copy and paste the data from other spreadsheets.  I have protected the sheet, but if someone copies and pastes data from another form, it changes my format.

[jitendrakumarsoni]

Dear sir ji,
We have a Mis & MRST report in the warehouse. Data analysis report than growth sale
Report Format :
YTD, MTD, L2LM, Total warehose qty, site qty lable
Report

[justcuz]

Hello!

I am have a form that sends data to a new sheet. However I would like the new sheet to be totally blocked but still allow for the data to be transferred. I am using a Macro for the data to be send to the next sheet.

dould you or anyone help please with this.
(I tried to do this but then the Macro stopped working, and gave and error message ) 

[lilianwong]

Is there a way to protect worksheet but allow users to Sort Data with unlocked cells?

[excelisfun]

I do not. You may have to post here and ask for a work around:

mrecel [dot] com/forum

[emanalbar]

what if my sheet is having a vba, it will give me an error message. please help.