Copy data from strictly-protected sheet

How to copy data from a range where you cannot select, due to worksheet protection?

Excel Tips - Copy protected cellExcel Tips - Copy from stickly-protected sheet

Here’s the situation: You receive a worksheet with data but not insight.  Well, it happens and you get use to it already.  You plan to copy the (raw) data and paste it to a new worksheet and do some analyses on your own.  Unfortunately you are prohibited from performing such a simple action because the worksheet is protected in a way that you cannot select the cells with data you need.  (Of course you do not know the password to unprotect the worksheet.)

What are you supposed to do then?  Print a hard copy and then re-input the data one by one?  If this is the approach you used, you’d better read this.

With Name Box (sitting on the top of cell grid; and on the left to formula bar), you may go to any range on s spreadsheet, even if it is protected from selecting.

Do the following:

Excel Tips - Copy from stickly-protected sheet 2

IMPORTANT: Once you hit Enter, nothing happens apparently.  Press Ctrl+C (before you hit or click on anything else).

Now you should see the “marching ants” surrounding A4:E8.  It means that you have put that range into clipboard successfully for Pasting to somewhere else.

The next simple action is to go to a new worksheet or a blank worksheet and do the normal Paste (Ctrl+V).

Excel Tips - Copy from stickly-protected sheet 3

There you go! Without the hassles of re-input manually.  :)

Thoughts of protecting cells from selecting

Many people do that without a particular reason.  “Just do it” is their motto in workplace, maybe.  Nevertheless, there is one good reason I would protect locked cell from selecting. It is to make navigation easier for user’s input.  With locked cells protected from selecting, while allowing “unlocked” cells to be selected, user could then focus on where they need to input.  Although it is not perfect, it reduces chance of inputting data to irrelevant locations.  :)

If human errors can be avoided totally, it is not human errors at all.

This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

24 Responses to Copy data from strictly-protected sheet

  1. Sandra says:

    Thanks. Made my working day🙂


  2. ALEX says:



  3. SK says:

    Fantastic! super hack


  4. RATISH RAVI says:



  5. Nanda Kumar says:

    Thanks guys.. really helped.


  6. S D says:

    Awesomeeee! Used in 2016


  7. MF says:

    You are welcome ! 😀


  8. Claire in NZ says:

    Thank you from another happy user!!!


  9. Marie-Claude Hardy says:

    Wow! Now you might be able to solve my big question here:
    I can copy-paste from a protected sheet here. However, it contains hiddent rows and when I copy-paste it, the hidden rows are included. Since it is protected, I cannot copy only visible row as I cannot access F5… Is there a different way to do so? I have a huge worksheet with severals sheets… and a mega headache!

    Thanks in advance🙂


    • MF says:

      On top of my mind, you may try
      1) Alt+; this is the shortcut for selecting visible cell. Try this before you copy
      2) if the above doesn’t work, you may press Ctrl and then W right after you paste the data. This action will open the paste options where W means keep source column width, which should make the hidden columns hidden again.
      I haven’t tried the above as i am not with my PC.
      Hope either one helps 😁


  10. Michelle says:

    Thank you thank you thank you!!!


  11. Igor says:

    I like this one!


  12. Dr. Demento says:

    Cool!! This works even if cells/rows/columns are hidden as long as the range encompasses them.

    Total hack!! I love it.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s