While working with Microsoft Excel, sometimes we might share the workbook with other users. A group of people may work on the same document simultaneously by sharing a spreadsheet. There are no duplicates because everyone’s data just contains a single worksheet. While accessing an Excel file, we are allowing other people to modify the same document, which eliminates the effort of maintaining track of different editions. But we may want to make them edit into specific data on the workbook. In this article, we will be setting permissions for a shared excel file.
Download Practice Workbook
You can download the workbook and practice with them.
Excel allows users to safeguard protected work in a variety of ways, including preventing someone from entering a workbook without a password, providing Read-Only access to a workbook, and even just encrypting a spreadsheet so we do not even accidentally erase any formulae.
Suppose we have a sheet of some students’ IDs, names, and some reports for their presence on some specific dates. Now, they may present in the class. But while taking presents they may be absent at that particular time. So, we want to give access to another person who takes care of those records. But we don’t want them full access to the excel sheet. So, let’s see some setting permission for a shared excel file.
Assume that, we just want to allow permission to the other user to just Read-Only or View the spreadsheet that everything is okay or not. For this, we need to follow some procedures below. Let’s look at the procedures to allow users just to view the spreadsheet.
- Firstly, go to the File tab from the ribbon.
- Secondly, click on Info from the backstage view.
- Thirdly, choose Mark as Final from the Protect Workbook drop-down menu.
- This will appear as a Microsoft Excel dialog box showing the message ‘This workbook will be marked as final and then saved’.
- Now, click on the OK button to proceed.
- By clicking ok, another excel warning dialog will appear. Again click OK.
- Keep an eye out for the yellow bar at the top of the workbook, which indicates that it is designed as final.
- Now, if you go to check the backstage view again by going to the File tab and then clicking Info. You will see the permission section is now updated.
By encrypting passwords we secure our Excel files to stop everyone else from processing the system they contain. Microsoft Office password protection is a safety mechanism that uses a user-supplied password to safeguard Microsoft Office applications. To encrypt passwords we need to follow the steps down.
- Likewise, in the previous method, go to the File tab from the ribbon.
- Next, from the backstage view, select Info.
- Further, from the Protect Workbook drop-down box, select Encrypt with Password.
- Excel displays the Encrypt Document, Now from there enter a password in the Password field.
- Then, click OK.
- Again a Confirm Password dialog will appear to ensure the password you are entering is correct or not.
- Click OK to finish the process.
- Finally, If you go to the File tab and then select Info, you may examine the backstage view once again. You’ll see that the permissions section has been modified.
Although the worksheet is password secured, we may designate any single user to modify a certain range. Let’s follow the procedure below to set up the permission for certain users to edit certain ranges.
- In the first place, select the cell you allow users to edit. So, we select the range D5:F8.
- Next, go to the Review tab from the ribbon.
- Then, Select the Allow Edit Ranges option from the Protect group.
Note: If the Excel spreadsheet is not password secured, this Allow Edit Ranges option will show.
- This will display the Allow Users to Edit Ranges dialog.
- Now, click on New to take the referred cells and the title.
- New Range dialog will open up.
- Here, type a title name under the Title field.
- Select the cells on Refers to cells field which we have already selected.
- Further, enter a password under the Range Password field.
- Click on the OK button to proceed.
- Furthermore, confirm the password by entering the password on the Reenter password to proceed field.
- Again, this will take us back to the Allow Users to Edit Ranges dialog.
- After that, click on Permission if you want to make the worksheet more restricted.
- This will appear as a dialog name Permission for Range 1. As we don’t want to make any other changes, we click on Cancel.
- To unprotect the sheet, click on Protect Sheet bottom left of the dialog.
Note: To change an already existing editable range, check the option labeled Ranges unlocked by a password when the sheet is protected. Then select Modify from the drop-down menu.
- Protect Sheet dialog will appear.
- Type the password which you put earlier on the Password to unprotect sheet.
- Make sure that Select locked cells and Select unlocked cells are checked.
- Then click OK.
Note: In the Range password box, the passcode will be used as a key to access that specific range. This password, on the other hand, is absolutely customizable. If you don’t give a password, though, any other user can simply alter the cells in your worksheet. Select Permissions from the drop-down menu. This will bring up the Range Permissions dialog box.
- A Confirm Password popup will display once again to verify that the password you are entering is accurate.
- Finally, click OK to complete the procedure of setting up permission.
Whenever you share an Excel worksheet, you should be aware of the constraints which the shared spreadsheet does not support. So take a peek at it.
- Filtering and sorting by formats.
- Formatting that is conditional.
- Cells are combining.
- PivotTable views and Excel tables.
- Illustrations and graphs.
- Validation of data.
- Worksheet security.
- Data is grouped or outlined.
- Slicers and sparklines are two types of slicers.
- Formulas in an array.
- There are a few additional details.
You have the authority to utilize the current Excel capabilities, but not to create or alter them. If you want to use any of the above-mentioned alternatives, make sure to do something before distributing the Excel files.
The above methods will assist you in Setting permissions for a shared Excel File. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!