قالب وردپرس درنا توس
Home / Tips and Tricks / How to protect workbooks, spreadsheets and cells from editing in Microsoft Excel

How to protect workbooks, spreadsheets and cells from editing in Microsoft Excel



  excel logo

You have worked hard on the worksheet. You don't want anyone to destroy it. Fortunately, Excel provides some pretty good tools to prevent people from editing different parts of a workbook.

Protection in Excel is password-based and happens on three different levels.

  • Workbook: You have some options for protecting a workbook. You can encrypt it with a password to restrict who can even open it. You can open the file as read-only by default so people have to choose to edit it. And you protect the structure of a workbook so everyone can open it, but they need a password to reorganize, rename, delete, or create new worksheets.
  • Worksheet: You can protect data on individual worksheets from being
  • Cell: You can also protect only specific cells on a worksheet from being modified. Technically, this method involves protecting a worksheet and then exiting certain cells from that protection.

You can also combine the protection of the different levels for different effects.

Protecting an entire workbook from editing

You have three choices when it comes to protecting an entire Excel workbook: encrypting the workbook with a password, writing the workbook read-only or protecting only the structure of a workbook.

Encrypt a workbook with a password

For the best protection, you can encrypt the file with a password. When someone tries to open the document, Excel asks them for a password first.

To set up, open your Excel file and go to the File menu. You will see the category "Info" by default. Click the "Protect Workbook" button and then select "Encrypt with password" in the drop-down menu.

 click on the protect workbook and select encrypt with password command

In the Encrypt Documents window that opens, type your password and then click "OK".

 type password and click OK

Note! Pay attention to the warning in this window. Excel gives no way to reset a forgotten password, so make sure you use one that you remember.

Enter your password again to confirm and then click "OK".

 confirm password and click ok

You return to your Excel sheet. But when you close it, the next time you open it, Excel will prompt you to enter the password.

 type password and click ok

If you ever want to remove the password protection from the file, open it up (which of course requires you to enter the current password) and then follow the same steps you used for to enter the password. Only this time, clear the password and then click "OK".

 use a blank password to clear password protection

Make a workbook available only

Make a workbook open as read-only is super simple. It does not provide any real protection because anyone who opens the file can make editing possible, but it can serve as an attempt to be careful about editing the file.

To set up, open your Excel file and go to the file menu. You will see the category "Info" by default. Click the "Protect Workbook" button and then select "Encrypt with password" from the drop-down menu.

 click on the protect workbook and select the open command read only "width =" 625 "height =" 681 "src =" /pagespeed_static/1.JiBnMqyl6S.gif "onload =" sidespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this ); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Now anytime someone (including you) opens the file, they get a warning stating that the file's authors prefer to open it as read-only, unless they need to make changes.

 warning that the file's author wants you to open it read only "width =" 650 "height =" 103 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages .loadIfVisibleAndMaybeBeacon (this);

To delete the reading setting, go back to the File menu, c Lick the "Protect Workbook" button again and switch to the "Always open reading" setting.

Protecting a workbook structure

The last way to add workbook protection is to protect the structure of the workbook. This type of protection prevents people who do not have the password from making workbook level changes, which means they will not be able to add, delete, rename or move spreadsheets.

To set up, open your Excel file and head to the File menu. You will see the category "Info" by default. Click the "Protect Workbook" button and then select "Encrypt with password" from the drop-down menu.

 click on the protect workbook and select the protection of the workbook structure "width =" 601 "height =" 705 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Enter your password and click "OK."

 type a password and click ok "width =" 311 "height =" 234 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon; "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Confirm your password and click "OK."

 confirm password and click ok "width =" 449 "height =" 232 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" sidespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerro r =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Everyone can still open the document (provided you don't also encrypt the workbook with a password), but they won't

 structural commands are not available "width =" 477 "height =" 364 " src = "/ pagespeed_static / 1.JiBnMqyl6S.gif" onload = "pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon)," onerror = "this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

If someone knows the password, they can access these commands by switching to the "Review" tab and clicking on the "Protect Workbook" button.

<img class = "alignnone wp-image-405051 size-full" data-pagespeed-lazy-src = "https://www.howtogeek.com/wp-content/uploads/2018/06/x2019-02 -12-17.png.pagespeed.gp + jp + jw + pj + ws + js + rj + rp + rw + ri + cp + md.ic.6uIF1w2jL2.png "alt =" on the tab review, click protect workbook "width =" 607 "height =" 302 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" sidespeed. lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; [this]; [1 9459027]

They can then enter the password

 write your password

And the structural commands become available

 structural commands are now available "width = "320" height = "355" src = "/ pagespeed_static / 1.JiBnMqyl6S.gif" onload = "pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);" onerror = "this.onerror = null; ); [19659003] It is important to understand that this action removes the workbook structure from the document. To restore it, you must return to the file menu and protect the workbook again.

Protecting a worksheet from editing

You can also protect individual worksheets from editing. When you protect a spreadsheet, Excel locks all cells from editing. Protecting your worksheet means no one can edit, reformat or delete the content.

Click on the "Review" tab on the main Excel band.

 switch to review tabs "width =" 527 "height =" 287 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" sidespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this. onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Click "Protect Sheets."

 click on protect sheet button "width =" 598 "height =" 316 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages. loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Enter the password you want to use to unlock the sheet in the future.

 write your password "width =" 588 "height =" 310 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Select the permissions you want users to have for the spreadsheet when it is locked. For example, you can allow people to format but not delete rows and columns.

 Select permissions "width =" 588 "height =" 310 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" this.onerror = null; sidespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Click "OK" when you have finished selecting permissions.

 click OK "width =" 588 "height =" 310 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Enter the password you made to confirm that you remember it and then click "OK".

 confirm your password "width =" 632 "height =" 313 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this. onerror = null; pagespeed.la zyLoadImages.loadIfVisibleAndMaybeBeacon (this);

If you need to remove that protection, go to the "Review" tab and click the "Unprotect Sheet" button.

 on the Review tab, click on the protected sheet "width =" 523 "height =" 175 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon

Enter your password and then click "OK".

 write your password

Your sheet is now unprotected. Please note that the protection is completely remote and that you must protect the sheet again if you wish.

Protecting specific cells from editing

Sometimes you can only protect specific cells from editing in Microsoft Excel. For example, you can have an important formula or instructions that you want to keep. Whatever the reason, you can easily lock only certain cells in Microsoft Excel.

Begin by selecting the cells you do not want to lock.

 select cells that you want to unlock "width =" 529 "height =" 260 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Right-click on the selected cells and select the "Format Cells" command.

 right click on selected cells and select format cells "width =" 575 "height =" 295 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon), "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

In the Format Cells window, switch to the "Protection" tab.

 bite to protection tab "width =" 593 "height =" 282 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Clear the "Locked" check box.

 Clear the Locked check box. "Width =" 595 "height =" 265 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" sidespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); "onerror =" this .onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); [19659003] Then click "OK".

 click ok "width =" 479 "height =" 255 "src =" / pagespeed_static / 1.JiBnMqyl6S.gif "onload =" pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon), "onerror =" this.onerror = null ; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);

Now that you have selected the cells that you want to allow editing, you can lock the rest of the spreadsheets by following the instructions in the previous section.

Note that you can lock a spreadsheet first and then select the cells you want to unlock, but Excel approx n be a bit flaky about it. This method of selecting the cells that you want to stay unlocked and then locking the sheet works better.


Source link