Suppose we have a sheet of some students’ IDs, names, and a record of their attendance on specific dates. We want to give access to another person who takes care of these records however, we don’t want them to have full access to the Excel sheet. So, let’s set some permissions for a shared Excel file.
Method 1 – Limit Permission Rights by Making Shared Excel File as Final
Assume that we just want to allow permission to the other user to just Read-Only or View the spreadsheet. For this, we need to follow some procedures below.
STEPS:
- First, go to the File tab from the ribbon.
- Click on Info from the backstage view.
- 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 designated as final.
- Now, if you go to check the backstage view again by going to the File tab and then click Info, you will see the permission section is now updated.
Read More:Â How to Enable Share Workbook in Excel
Method 2 – Encrypt Shared File with Password in Excel
Microsoft Office password protection is a safety mechanism that uses a user-supplied password to safeguard Microsoft Office applications.
STEPS:
- Go to the File tab from the ribbon.
- Next, from the backstage view, select Info.
- 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.
- 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.
Read More: How to See Who Is in a Shared Excel File
Method 3 – Set Permissions for Certain Users to Edit Certain Ranges of a Shared File
Although the worksheet is password secured, we may designate any single user to modify a certain range.
STEPS:
- Select the cell you allow users to edit. So, we select the range D5:F8.
- Go to the Review tab from the ribbon.
- 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.
- 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.
- Enter a password under the Range Password field.
- Click on the OK button to proceed.
- 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 box.
- Click on Permission if you want to make the worksheet more restricted.
- This will appear as a dialog name Permission for Range 1. We don’t want to make any other changes so 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 that 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. If you don’t provide a password 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.
- Click OK to complete the procedure of setting up permission.
Limitations of Shared Excel File
Whenever you share an Excel worksheet, you should be aware of the constraints that 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.
- Subtotals.
- Slicers and sparklines are two types of slicers.
- Hyperlinks.
- Formulas in an array.
- Macros.
- 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.
Download Practice Workbook
Download the workbook to practice what you will learn in this tutorial.
Related Articles
- [Solved]: Share Workbook Not Showing in Excel
- [Fixed!] Unshare Workbook Greyed Out in Excel
- How to Share Excel File for Multiple Users
<< Go Back to Share Workbook | Workbook in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
