Row Level Security in Excel

Introduction:

Row-Level Security (RLS) is a data security feature that restricts data access at the row level for a user or a group of users in a database or data analytics tool. It ensures that users can only access the data they are authorized to view. Though this feature is built in BI Tools like Power BI, SQL Server, and Azure Synapse Analytics, it is not readily available in Excel. We will find a solution to this problem.

Problem:

The Row Level type of security is not inherently possible with Microsoft Excel. However, there is a turnaround to implementing this on Excel Workbooks. This can be very useful if the user has some already existing reports in Excel and wants to provide restricted access to signed in users based on certain filter criteria.

Setup:

I have an Excel Report called ‘Data to Displayed to the Users’ which needs to apply the entity level filter based on Entity Code on cell ‘G9’.

The same Workbook contains another sheet ‘Users’ which contains the Email Address against a Code. We need to grant them access to respective Email Addresses by prefiltering the report based on their defined access code only.

Solution:

1. Enable Developer Tab for Excel.

Go to File → Options → Customize Ribbon → Developer → Ok

2. Once the Developer Tab is available, Click on the Visual Basic Icon.

3. Add a new Module

4. Insert the Code Below to the new module you created.

Sub ApplyUserCode()
    Dim wsData As Worksheet
    Dim wsUsers As Worksheet
    Dim loggedInEmail As String
    Dim emailRange As Range
    Dim foundCell As Range
    Dim userCode As String

    ' Define worksheets
    Set wsData = ThisWorkbook.Sheets("Data to Displayed to the Users")
    Set wsUsers = ThisWorkbook.Sheets("Users")

    ' Get the logged-in user's email address
    loggedInEmail = Environ("UserName") & "@halma.com" ' Update @yourdomain.com if needed

    ' Find the logged-in email in the 'Users' sheet
    On Error Resume Next
    Set emailRange = wsUsers.Columns("A") ' Assuming "Email ID" is in column A
    Set foundCell = emailRange.Find(What:=loggedInEmail, LookIn:=xlValues, LookAt:=xlWhole)
    On Error GoTo 0

    ' If email is found, get the corresponding 'Code' and update G9
    If Not foundCell Is Nothing Then
        userCode = wsUsers.Cells(foundCell.Row, "B").Value ' Assuming 'Code' is in column B
        wsData.Range("G9").Value = userCode
    Else
        MsgBox "Your email address was not found. Please contact the administrator.", vbCritical
    End If
End Sub

5. Now Add the Code to ThisWorkbook so that is Module is run Automatically once the Excel is opened.

Private Sub Workbook_Open()
    Call ApplyUserCode
End Sub

6. Save these Visual Basic Project and close the window. Now also save the Excel using the Extension as .xlsm i.e Excel Micro Enabled.

Testing:

Now you can ask an user to open the File and see that the RLS gets implemented as the user only is able to see the Report Prefiltered based on the Code that his Email Address has access to from the User master sheet.

For Example:

Conclusion:

The Row Level Security can be implemented using Macros/VB script by enabling Developer Tab in Excel directly and saving the file as . xlsm. The Security can be further strengthened by locking access to the sheet. This would just show Data Filtered based on user access and not allow them to edit any data.