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.

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

55 Responses to Copy data from strictly-protected sheet

  1. Matt says:

    Appreciate the info, but when I have the section of the workbook selected that I want to copy, I press Ctrl C, I do not get the “marching ants”, and go to a blank workbook and paste and I only get two little boxes with question marks in them. When I select in that field where I just pasted it is empty except for a space? Any guidance here?


    • MF says:

      Hi Matt. That’s strange.
      My guess is the Copy action did not properly take place. You may try again by showing the clipboard to confirm you have “copied” what you need successfully.
      Hope It helps.


  2. TC says:

    Awesome, this saved me ours of time. TC


  3. Sayali says:

    Works really well! Thanks!


  4. florabel says:

    Hi, can you help me please. I cannot copy the whole worksheet even if I follow your instructions.
    Please help


  5. D Simon says:

    When I try this method and record it into a macro, the namebox selection is not recorded. How do I register the cells which are input into the namebox into the macro?


  6. Jeanne M Knight says:

    THANK YOU SO MUCH! I would have never figured that out


  7. Brian Johnson says:

    How do you do this with, sheets that have formulas in it. Cause the sheets I have the formulas will not be moved with it.


    • MF says:

      Hi Brian,
      Could you please elaborate your question a bit?


      • Brian Johnson says:

        Sorry, I was meaning if its like keeping track of sports data, like points plus/minus and automatic. here is the link to the data sheets I was wanting to work on, and change around, for my own stats and fun. Someone on Answers went off me and saying it was wrong of me trying to change stuff. I was trying to just get blank sheets so I can play a tournament and keep track for it on X-box.


  8. GYAN says:



  9. Halefom T says:

    Really helpful!!! thanks bro!!


  10. Sudz says:

    This is BRILLIANT and you are AMAZING!!!


  11. Thanks Dude this helped me a lot 🙂


  12. Kristal says:

    You just saved my TONS of time. Thank you! 🙂


  13. Nigel says:

    Just used in 2017, saved me loads of time. Thanks


  14. Sam says:

    The range idea allowed me to select the section of the workbook that was hidden but the data wasn’t automatically copied. However, I was able to copy and paste then! Thank you!


  15. Rizwan Shah says:

    Done, thanks a lot, this made the entire exercise easy.


  16. Sandra says:

    Thanks. Made my working day 🙂


  17. ALEX says:



  18. SK says:

    Fantastic! super hack


  19. RATISH RAVI says:



  20. Nanda Kumar says:

    Thanks guys.. really helped.


  21. S D says:

    Awesomeeee! Used in 2016


  22. MF says:

    You are welcome ! 😀


  23. Claire in NZ says:

    Thank you from another happy user!!!


  24. 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 😁


  25. Michelle says:

    Thank you thank you thank you!!!


  26. Igor says:

    I like this one!


  27. 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.