How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Mike B Your Own Question
Mike B
Mike B, Information Analyst
Category: Computer
Satisfied Customers: 280
Experience:  15+ years experience with computers, programming, tech support
16526543
Type Your Computer Question Here...
Mike B is online now
A new question is answered every 9 seconds

How to protect cells in excel from being changed?

1.    Highlight all the cells on your worksheet that you’re interested in.
2.    Right-click and select Format Cells
3.    Select the Protection tab
4.    Un-check the "Locked" option (this will enable editing for all cells)
5.    Click OK
6.    Now, select the cell that you DO NOT want edited
7.    Right-click and select Format Cells
8.    Select the Protection tab
9.    Check the "Locked" option (this will lock the cell from being edited)


Now, we need to protect the sheet:

1.    Click the Tools menu
2.    Select Protection
3.    Select Protect Sheet
4.    Type in a password and Unprotect the sheet.
5.    Click OK
6.    Confirm Password

 

Read full interaction below »

Resolved Question:

How can I protect a cell in Excel from being changed but still allow it to selected for a copy (of the value) & paste
Submitted: 8 years ago.
Category: Computer
Expert:  Mike B replied 8 years ago.
Customer,

You can enable sheet protection and any cell that has the Locked attribute set, will not be editable, but can still be copied.

Please do the following:
  1. Highlight all the cells on your worksheet that you're interested in.
  2. Right-click and select Format Cells
  3. Select the Protection tab
  4. Un-check the "Locked" option (this will enable editing for all cells)
  5. Click OK
  6. Now, select the cell that you DO NOT want edited
  7. Right-click and select Format Cells
  8. Select the Protection tab
  9. Check the "Locked" option (this will lock the cell from being edited)

Now, we need to protect the sheet:
  1. Click the Tools menu
  2. Select Protection
  3. Select Protect Sheet
  4. Type in a password ***** Unprotect the sheet.
  5. Click OK
  6. Confirm Password

Please let me know if you have any questions.
Mike B and 4 other Computer Specialists are ready to help you
Customer: replied 8 years ago.
I don't understand. It looks like you're saying unlock the cells (to allow editing) then lock the cells (to protect from editing). What am I missing?
Expert:  Mike B replied 8 years ago.
Exactly... I wasn't trying to confuse you.

Excel, be default locks ALL cells of a spreadsheet. Your initial query was to "protect a cell in Excel"... from this I gathered that you were looking to protect ONE single cell.

That is why I said, you should unlock all the cells, then lock only the one cell that you would like to protect.

Does that make sense?
Customer: replied 8 years ago.
but it's not doing what I need. But I may not have described my situation sufficiently. Give me a few minutes to elaborate.
Customer: replied 8 years ago.

Mike, I have a workbook with multiple sheets. It was created by somebody else who also created an add-in that, among other things, appears to automatically protect all sheets when the workbook opens.

I want to be able to copy data from some of the sheets and paste to another application but with the cells locked and the sheets protected I’m not able to select the data I need. So I created a new sheet and populated it with references (=OldSheet!E12) to the data I need. The plan was to copy from the new sheet. But I couldn’t select data from it either.

So I unprotected the new sheet and unlocked the cells I wanted to copy, then closed, saved, and reopened the workbook. I can now select from the new sheet, but – here’s the problem: I could also inadvertently edit the cells I was attempting to copy. I want to copy but not be able to edit.

Is that clearer?

Expert:  Mike B replied 8 years ago.
Customer,

I don't understand why you can't copy from the protected sheet... When I protect a sheet in my Excel file, it does not prevent me from copying any of the data to another sheet. Maybe there is something else going on with your file? Can you share the file with me? If so, just upload it to http://wikisend.com ... Wikisend will give you a Download Link, which you have to copy/paste into a reply here, so that I can download your file.

Thanks,
Mike B.
Customer: replied 8 years ago.
I can send you the file but not the add-in that it uses or the database that the file reads when it opens. Should I send it?
Expert:  Mike B replied 8 years ago.
No... do not send it.

The new workbook that you have with the unprotected cells... What happens if you select all the cells, make sure the "Locked" option is checked on, the protect the sheet?
Are you able to select and copy the cells?

Let me know.

Thanks,
Mike B.
Customer: replied 8 years ago.
It's a new worksheet in the existing workbook.

But if I Unprotect the sheet (so I can select all cells) then turn on "Locked" for all cells, then protect the sheet - I'm right back where I started: can't select Locked cells on a protected sheet.
Expert:  Mike B replied 8 years ago.
Can you try this in a completely new workbook?

Type some stuff into a few cells.
Protect the whole sheet.

Can you select and/or copy the cells?

Please let me know.

Thanks,
Mike B.
Expert:  Mike B replied 8 years ago.
Customer,

I think I found it! It's a setting in the Protect Sheet dialog box...

When you protect a sheet, you can make it selectable or not... Please see the screenshot below:

graphic

When those checkboxes are unchecked, you will not be able to select or copy any of the cells that are locked or unlocked.

Please double-check this setting in your workbook when you go to protect the sheet.

Thanks,
Mike B.
Customer: replied 8 years ago.
OK -
New workbook. Added data to some cells. Cells Locked by default. Protected worksheet.
I can Select and Edit cells that have the data.

Regarding Protect Sheet dialog box - You're right - if I uncheck those boxes I can't select or copy them - but I need to be able to select them so I can copy them.i
Expert:  Mike B replied 8 years ago.
Customer,

What I was trying to say was, that you need to make sure that those checkboxes in the Protect Sheet dialog box are checked ON, this way you can still protect the sheet from being edited, AND you'll be able to select and copy the cells.

Thanks,
Mike B.
Customer: replied 8 years ago.
Mike - sorry this is taking so much time and apologize if I'm not describing the issue correctly,

but

Those boxes are checked ON, the sheet is Protected, the cells are Unlocked, and I can Select and Copy and, unfortunatly, Edit
Expert:  Mike B replied 8 years ago.
Ok, it's not a problem... MS just happened to make this protect/unprotect/lock/unlocked business a bit confusing...

Please make sure the cells are LOCKED, not UNlocked... this will prevent editing.

Thanks,
Mike B.
Customer: replied 8 years ago.
Sorry - I said the cells are Unlocked
I should have said:
"Those boxes are checked ON, the sheet is Protected, the cells are Locked, and I can Select and Copy and, unfortunately, Edit"
Expert:  Mike B replied 8 years ago.
Ok... now that is weird...

Let's try this:
On the sheet that you would like to have protected...
Press CTRL + A (this will just highlight all the cells on the sheet)
Right-click anywhere on any of the cells
Select Format Cells...
Click on the Protection tab
Does it look like this?
graphic

Or like this:
graphic

Please let me know.

Thanks,
Mike B.
Customer: replied 8 years ago.
The 2nd. Locked is checked
Expert:  Mike B replied 8 years ago.
That's a good one...

Can you download this file and see what happens when you try to edit the contents of the cells?

http://wikisend.com/download/969164/Book1.xls
Customer: replied 8 years ago.
I can Select (and Copy) but not Edit. Just what I'm looking for.

I'm using Excel 2002, SP3
Expert:  Mike B replied 8 years ago.
Well, at least that's something...

I wonder if it has something to do with your version of Excel... Can you send me your file (even though it's going to have the bro***** *****nks and no add-in)... I will see if I can protect the sheet for you and see if that works?

Just upload your file to Wikisend, like I mentioned earlier.

Thanks,
Mike B.
Customer: replied 8 years ago.
http://wikisend.com/download/499174/DR_Reconciliation.xls (Wikisend is very cool)

The new sheet is "Copy Sheet"

But Mike, this is all above and beyond what you thought you were getting into. Feel free to bail out with my thanks. *****'ll give you an Excellent rating.

And I have to leave now for a couple of hours.
Expert:  Mike B replied 8 years ago.
No worries... I like the challenge!

Here is your locked file: http://wikisend.com/download/595988/DR_Reconciliation.xls

It wasn't protected...

Thanks,
Mike B.
Customer: replied 8 years ago.
it wasn't protected because the add-in wasn't available.

I'm not sure what to expect with the file you returned. I open it I can't select from "Copy Sheet".

But I really have to go. I assume justanswe will have timedout when I get back. You can reach me at***@******.***

Thanks
Expert:  Mike B replied 8 years ago.
Customer,

Just bookmark this page: http://www.justanswer.com/questions/28o5b-protect-cell-excel-changed

You can come back here any time, and we can continue...

Thanks,
Mike B.
Expert:  Mike B replied 8 years ago.
Customer,

As it appears, the add-in is unprotecting the cells in your file. The "Book1.xls" file I sent you worked as expected, but yours didn't... I did the same procedure on both files, the only difference was that your file has the add-in.

I would talk to the programmer that wrote the add-in and see if they can modify to suit your needs.
Mike B and 4 other Computer Specialists are ready to help you
Customer: replied 8 years ago.
That sounds like a good idea - thanks for your help