Sometimes while working on a project we need to share our Excel worksheet with users or clients. Suppose you want the users to only be able to view the worksheet but they will not be able to copy and paste any kind of data from that worksheet. Basically, we want to prevent users from doing any kind of modification to our worksheet. In most cases, people use macros to disable copy and paste in Excel. But, in this article, we will cover how to disable copy and paste in excel without macros.
Use ‘Protect Sheet’ Option to Disable Copy and Paste in Excel without Macros
To prevent the users from modifying your Excel worksheet Microsoft Excel provides an option named “Protect Sheet”. By using this option we can disable the copy and paste in Excel without macros. The “Protect Sheet” option prevents users from any kind of editing in our worksheet with a password. In fact, with this option, we can disable not only the copy and paste option but also any kind of modification in our worksheet from users. This article will illustrate this option to you by using two criteria.
Criteria 1: Disable Not Only Copy and Paste But Also Selection of Cells in Excel
In the first criterion, we will disable copy and paste as well as a selection of cells in excel. So, after applying this method in a worksheet, we will not be able to copy and paste any cells. We will not be able to select any cell from the worksheet either. For example, take the following dataset of “Marks” & “Subjects” for different students. We will disable the options copy, paste, and selection cells in this dataset.
So, let’s see the steps to perform this action.
STEPS:
- First, go to the Review tab. From the Protect option of the ribbon, select the option Protect Sheet.
- In short: Go to Review > Protect > Protect Sheet
- Now, a new dialogue box named “Protect Sheet” will appear.
- Next, insert a password in the box named “Password to unprotect sheet”.
- Then, uncheck the options “Select locked cells” & “Select unlocked cells”.
- Now, press OK.
- After that, a new dialogue box will appear named “Confirm Password”.
- Type the password again in the box named “Reenter password to proceed”.
- Then, press OK.
- Finally, after the above actions if you try to select or copy and paste any cell from the worksheet you will see the following message box.
Criteria 2: Selection of Excel Cells is Enabled but Copy and Paste are Disabled without Macros
In this example, we will also disable the copy and paste in Excel without macros by protecting the worksheet with the option “Protect Sheet”. But unlike the previous example, this time we will enable the selection of cells in our worksheet. So that users will be able to select cells from our worksheet but will not be able to copy and paste any kind of data. To clarify this method we will use the same dataset that we did use in the previous example.
So, let’s take a look at the steps to perform this action.
STEPS:
- Firstly, go to the Review tab. Select the option Protect Sheet from the Protect option of the ribbon.
- In short: Go to Review > Protect > Protect Sheet
- So, a new dialogue box will appear named “Protect Sheet”.
- Secondly, input a password in the box named “Password to unprotect sheet”.
- But, this time do not uncheck the options “Select locked cells” & “Select unlocked”. cells”.
- Then, hit OK.
- The above command will give a new dialogue box named “Confirm Password”.
- Thirdly, input the password again in the box named “Reenter password to proceed”.
- Press OK.
- Next, click on cell D5. So, we can see that we are now able to select the cells in our worksheet.
- After that, copy cell D5 and paste it to cell C12.
- Lastly, the above action will give the following message box. So, after this method, we can only select the cells from our worksheet but we can not copy and paste any kind of data.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In the end, this tutorial is an overview of how to disable copy and paste in Excel without macros. Download the practice worksheet that comes with this article to test your skills. If you have any questions, please leave a comment in the box below. Our team will try to reply to you as soon as possible.
Related Articles
- How to Copy Horizontal and Paste Vertical in Excel
- How to Copy Paste Vertical to Horizontal in Excel
- How to Copy and Paste a Column in Excel
- How to Copy Merged and Filtered Cells in Excel
- How to Copy and Paste in Excel with Merged Cells
- How to Copy and Paste in Excel When Filter Is On
- How to Fill Across Worksheets in Excel
- If Value Exists in Column Then Copy Another Cell in Excel
- How to Copy Cell If Condition Is Met in Excel
Seems you can still copy and paste it into a new book though, how to stop that?
Thanks for your question in our platform.
You may opt for the first method mentioned here to prevent Excel sheet content to another workbook completely.
Or you can read the article(https://www.exceldemy.com/protect-excel-sheet-from-copy-paste/#1_Use_Info_Option_to_Protect_Excel_Sheet_from_Copy-Paste) to prevent the workbook content from being copied to another workbook. You can ignore the VBA coding method if you want.