Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

given access to locked column based on Email for excel online

Copper Contributor

I developed a google sheet where the team lead gives approvals to work done as filled in each row by the employee. This was achieved by sharing the spreadsheet and granting everyone the editing access. For the columns or cells, I don't want the employee to touch I lock it against them by unmarking/unchecking their email. Only the Team lead's emails weren't unmarked, thereby giving them access to those columns.

However, the Management is requesting that we migrate to using Excel since it has an online version. However, i have been finding it difficult to achieve that flexibility.

I would like to know if this is achievable with excel online also.

1 Reply

@AbayomiOyeleye 

In Excel Online, achieving the same level of flexibility as in Google Sheets with regard to selectively locking or unlocking cells based on user emails may not be as straightforward, especially if you're looking for a solution without VBA or Macros, as Excel Online has limitations compared to the desktop version.

However, you can try the following approach using Office 365 features like "Protect Workbook" and "Share Workbook":

Protecting the Workbook:

1.Protect Workbook:

    • Open your Excel workbook in the desktop version of Excel.
    • Navigate to the "Review" tab on the ribbon.
    • Click on "Protect Workbook" and choose "Protect Structure and Windows."
    • Set a password if required.

Sharing and Permissions:

1.Share Workbook:

    • Save your workbook to OneDrive or SharePoint, allowing you to work collaboratively.
    • Click on the "Share" button in the top-right corner.
    • Add the email addresses of your team members with the appropriate access (Edit or View).

Shared Workbook Permissions:

  1. Shared Workbook Permissions:
    • After sharing, click on the "Share" button again.
    • Choose "Advanced" to manage permissions.
    • Uncheck the "Allow changes by more than one user at the same time" option. This might make it more difficult for users to modify the same cells simultaneously.

Locking Cells:

  1. Cell Protection:
    • In the Excel desktop version, you can still lock or unlock cells.
    • Select the cells you want to lock or unlock.
    • Right-click and choose "Format Cells."
    • In the "Protection" tab, check or uncheck "Locked" based on your requirements.
    • After setting up the protection, go back to the "Review" tab and click on "Protect Sheet" or "Protect Workbook."

Note:

  • Excel Online does not support VBA or Macros, limiting the extent of automation you can achieve online.
  • The ability to selectively lock or unlock cells based on user emails might not be as granular in Excel Online compared to Google Sheets.

This solution focuses on protecting the workbook and its structure, and controlling access through sharing and workbook protection. While it may not provide the exact same level of flexibility as Google Sheets, it leverages built-in Excel Online features. Always thoroughly test any access control mechanisms to ensure they meet your specific requirements. The text was created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.