How to Protect Formulas but Allow Copy in Excel (3 Simple Methods)

This tutorial will demonstrate how to protect excel formulas but will allow you to copy them. Suppose, we will share our excel file with other users. Our excel file contains different formulas. We don’t want to let our user make any change in the formula of the source excel sheet. But we will allow them to copy the formula from the source excel sheet to a new sheet. Without any further delay, let’s see how we can do this.


Download Practice Workbook

You can download the practice workbook from here.


3 Simple Methods to Protect Excel Formulas but Allow Copy

In this article, we will illustrate 3 simple methods to protect excel formulas but will allow you to copy them. To illustrate the 3 methods we will use the following dataset. The dataset contains information about the names of students, their subjects, and marks in corresponding subjects. Here, the column Total contains formulas that calculate the total marks of students in both subjects. Using this dataset we will explain all the methods of this article.

3 Simple Methods to Protect Excel Formulas but Allow Copy

To show the formula used in this dataset, go to Formulas > Show Formulas.

3 Simple Methods to Protect Excel Formulas but Allow Copy

So, in the following image, we can see the formulas used in the cell range (E5:E10).


1. Allow Copy of Protected Formulas with Review Tab in Excel

In the first method, we will use the Review tab to protect formulas in excel but will allow copying them. To perform this method follow the below steps.

STEPS:

  • Firstly, go to the Home tab.

Allow Copy of Protected Formulas with Review Tab in Excel

  • Secondly, from the ribbon click on the option ‘Find & Select’ and select ‘Go To Special’.

Allow Copy of Protected Formulas with Review Tab in Excel

  • A new dialogue box will open.
  • Thirdly, check the option Formulas from that box and click OK.

Allow Copy of Protected Formulas with Review Tab in Excel

  • As a result, the above command only chooses cells that contain formulas.

Allow Copy of Protected Formulas with Review Tab in Excel

  • Next, right-click on the selected cell and select the option Format Cells.

Allow Copy of Protected Formulas with Review Tab in Excel

  • The above command opens a new dialogue box named Format Cells. We can also launch the dialogue box by pressing Ctrl + 1.
  • Then, go to the Protection tab.
  • Also, tick the option Locked and then click OK.

Allow Copy of Protected Formulas with Review Tab in Excel

  • Furthermore, go to the Review tab.
  • Select the option Protect Sheet.

Allow Copy of Protected Formulas with Review Tab in Excel

  • So, a new dialog box named Protect Sheet. Click OK after entering a password in the input field.

  • Another dialog box will pop up, asking us to confirm our password.
  • Then, re-enter the password and click on OK.

  • Afterward, select any cell in the E5 to E10 A message box similar to the one below appears. The message indicates that the cell we are trying to access is password protected.

  • After that, select the cells that contain formulas.
  • Press Ctrl + C to copy the selected cells.

  • Then go to a new worksheet named NewSheet. Select cell E5 and paste the data.
  • Finally, we get results like the following image. So, we can protect excel formulas allowing us to copy them.

Read More: How to Protect Formulas in Excel (With Easy Steps)


2. Utilize Name Box to Protect Excel Formulas but Allow Copy

In the second method, we will utilize the Name Box. With the name box, we can copy formulas from a protected excel sheet. To do this we will follow the below steps.

STEPS:

  • First, input the cell range (E5:E10) in the name box.

Utilize Name Box to Protect Excel Formulas but Allow Copy

  • Next, press Enter.
  • The above command selects cells with formulas.
  • Then, press Ctrl + C to copy the selected cells.

Utilize Name Box to Protect Excel Formulas but Allow Copy

  • Now, go to another sheet named NewSheet
  • Press Ctrl + V to paste the copied data.
  • Lastly, we can see that we have copied the excel formula from a protected sheet.

Read More: How to Protect Formula in Excel but Allow Input (2 Methods)


3. Protect Excel Formulas but Allow Copy with VBA Code

We can also use a VBA code to protect excel formulas but still allow us to copy them. Like the previous methods, we will use the same dataset to illustrate this method. Just do the following steps to perform this action.

STEPS:

Protect Excel Formulas but Allow Copy with VBA Code

  • In addition, right-click on the sheet name VBA.
  • Then got to Insert > Module.

Protect Excel Formulas but Allow Copy with VBA Code

  • A blank VBA code window will appear.
  • Afterward, type the following code in that blank code window:
Sub Protect_Formulas()
ActiveSheet.Unprotect "123"
ActiveSheet.Range("A1:XFD1048576").Locked = True
ActiveSheet.Protect "123"
End Sub
  • Now, click on the Run icon or press the F5 key to run the code.

  • As a result, the above code protects the sheet but allows copying formulas from the worksheet.
  • After that, copy the cell (E5:E10) from the sheet VBA. Then, paste it to the cell range (E5:E10) of the worksheet named NewSheet.

Read More: Excel VBA: Protect and Hide Formulas


Conclusion

Ultimately, this article demonstrates how to protect formulas in excel but allow the copy option of those formulas. Download the practice worksheet that comes with this article to put your skills to the test. If you have any questions, please leave a remark in the box below. Our team will do everything necessary to react to you as soon as possible. So keep an eye out for more new Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo