ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tips & Tricks

Excel Tips & Tricks #466 - Setting range permissions in Excel Online

Author: Bani Lamba

Published: 13 Feb 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.
Hello and welcome back to Excel Tips and Tricks! This week we have a Basic User level post in which we look at how to set up range permissions in Excel Online to protect your spreadsheets.

Excel Online is great to use when you want to work collaboratively in a dynamic spreadsheet. However, there may be instances where you want to share a spreadsheet with others but restrict changes users can make to certain cell ranges.

This is when knowing how to set up permissions on ranges can be very handy to protect your spreadsheet from accidental or deliberate unwanted changes and edits!

Manage protection in Excel Online

Let’s start with an example. I have a loan modelling template in Excel Online that I want to share with others. While I want other users to be able to change the inputs, I want to protect the modelling outputs, which are all formula-driven, from any unwanted changes.

To make this easier to follow, all my input fields are highlighted in yellow.

screenshot from an excel spraedsheet

To get started on this, I navigate to the Review tab and select ‘Manage Protection’

screenshot from an excel spraedsheet

This will open a toolbar on the right-hand side. To get going with protecting the worksheet, I need to turn ‘On’ the ‘Protect sheet’ option. This will protect the whole worksheet from any unwanted changes or edits.

screenshot from an excel spraedsheet

However, I want to be able to allow users to edit the input fields in my model. To do this, I will need to add ranges that can be edited while protection is on. To do this, if I expand the ‘Unlocked ranges’ dropdown, I will see an option that allows me to add these ranges.

screenshot from an excel spraedsheet

From here I can select the range I want to protect and label it.

screenshot from an excel spraedsheet

It’s important to note here that adding a password to the unlocked range is optional and will add another level of protection by only allowing those with the password to edit the unlocked range.

screenshot from an excel spraedsheet

In my example, I’d like to let users to edit the unlocked ranges freely. As a result, I won’t be adding an additional range password.

Once I’ve added all my inputs to ‘Unlocked ranges’, I can now see the permissions in action! As expected, I can change my ‘Loan amount’ input to ‘15,000’ but I can’t make any changes to the ‘Closing Balance formula’.

screenshot from an excel spraedsheet

Once my range permissions are in place, I want to make sure that only certain users can make changes to these protections. While this is an optional field, to further restrict who can edit my permission ranges, I can add a ‘Sheet protection password’.

screenshot from an excel spraedsheet

Now the password will be required to access the ‘Manage Protection’ option and make changes to the protection permissions I have set.

screenshot from an excel spraedsheet

Managing protection options

From the ‘Options’ dropdown, you can select what you want to allow all users of this sheet to be able to do in your spreadsheet.

screenshot from an excel spraedsheet

In my example, I have allowed users of my spreadsheet to only be able to select locked and unlocked cells, which is the default setting.

If I wanted to allow users to be able to make changes to the format of cells for example, then I can check this option to enable this ability.

Pause protection

There may be instances where you want to disable the protection permissions to allow you to make changes to the spreadsheet, while keeping it protected for others. Excel allows you to pause protection permissions without removing or having to delete all set permissions.

To do this, I can navigate to the Review tab and select ‘Pause Protection’.

screenshot from an excel spraedsheet

As I set a ‘Sheet protection password’ in earlier steps, I will be prompted to enter the password to pause protection.

screenshot from an excel spraedsheet

The trick here is that now, I can make all the edits to the spreadsheet that I need, but if anyone else has it open, they will still be subject to the protection permissions.

Once I’ve made my changes, to then return to my set protection permissions I can simply select the ‘Resume Protection’ option in the Review tab.

screenshot from an excel spraedsheet
Or, when I close the workbook, the next time I open it, the protection will automatically be resumed.

This tip covered range permissions in Excel Online. Similar (though not identical) functionality exists in Excel Desktop; more guidance on how to set permissions for different ranges in Excel Desktop has been covered in Tip #159
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250