Data Protection Issues

Display User-Specific Rows in Excel with a PIN

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Create a login-style Excel report that displays rows associated with a selected user after a PIN is entered.

Display User-Specific Rows in Excel with a PIN
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook contains rows for multiple users and needs a convenient way to display one user's rows.
Observed behavior
The desired report shows matching rows after a user ID and PIN are entered; it is not intended to secure confidential data.
Before you start

Prepare a unique user ID and PIN for each user and ensure every data row contains the appropriate user ID. Use this workbook only for nonconfidential data: anyone with the file may be able to inspect its data and PINs.

Solution 1Recommended

Build a PIN-checked report with Excel formulas

Use a login sheet and a FILTER formula to show matching rows as a convenience feature, not as a security boundary.

This example requires an Excel version that supports FILTER. The Users and Data sheets remain accessible to someone with the workbook, even if you hide them or protect them with a password.

1
Create the source sheets

Add sheets named Login, Users, Data, and Report. On Users, put user IDs in A2:A100 and their PINs in B2:B100. On Data, put user IDs in A2:A1000 and the corresponding report data in columns B through D.

2
Set up the login cells

On Login, label A2 as User ID and A3 as PIN. Select B2:B3 and use Home > Number Format > Text, then enter a user ID in B2 and PIN in B3. Format the matching Users columns as Text before entering values, especially PINs with leading zeros.

3
Add the report formula

On Report, add the Data column headings in A1:D1. In A2, enter =IF(OR(Login!B2="",Login!B3=""),"Enter user ID and PIN",IF(SUMPRODUCT(--(Users!A2:A100=Login!B2),--(Users!B2:B100=Login!B3))=0,"Invalid user ID or PIN",FILTER(Data!A2:D1000,Data!A2:A1000=Login!B2,"No matching rows"))). Leave room below and to the right for the results to spill.

4
Test the report

Enter a valid user ID and PIN on Login, then open Report to check the matching rows. Try an invalid PIN and a user with no data rows to verify the messages.

Build a PIN-checked report with Excel formulas
This is not data protection: The PIN check, hidden sheets, and worksheet passwords do not prevent a workbook recipient from accessing underlying data or PINs. For confidential data, use a secure database or service with authenticated, user-specific access controls.
Work with spreadsheets in WPS Office

Create a user-specific report in WPS Spreadsheets

Use WPS Spreadsheets to build a login-style report for nonconfidential data. A PIN-check formula and FILTER can display matching rows in versions that support FILTER, but they do not secure the workbook.

  1. 1. Prepare the workbook: Open WPS Spreadsheets and create Login, Users, Data, and Report sheets. Place user IDs and PINs in Users columns A and B, and report data in Data columns A through D, with user IDs in column A.
  2. 2. Enter login details: On Login, format B2:B3 as Text, then enter the user ID in B2 and PIN in B3. Format the Users ID and PIN cells as Text before entering their values.
  3. 3. Create the report: On Report, copy the Data headings to A1:D1 and enter the PIN-check and FILTER formula from the solution above in A2. Check that your WPS Spreadsheets version supports FILTER.
  4. 4. Test the results: Change the user ID and PIN on Login and open Report to verify the displayed rows and invalid-PIN message. Use a secure access-controlled service instead if the data is confidential.
Display rows for a selected user in a dedicated report sheetOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface for formulas and worksheetsUse WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Does hiding the Users and Data sheets protect the information?

No. Hiding or password-protecting sheets can make a workbook easier to navigate, but it does not reliably prevent recipients from accessing the underlying data or PINs.

Why does the PIN lose its leading zero?

Excel may treat the PIN as a number. Before entering PINs, select the PIN cells on Login and Users and choose Home > Number Format > Text.

What if FILTER is not available in my Excel version?

The formula requires an Excel version that supports FILTER. You can use Data > Filter to manually display a selected user's rows, but that does not provide a PIN check or protect the other rows.

How can I securely restrict each user to their own rows?

Store the data in a secure database or service that authenticates each user and enforces user-specific permissions before returning rows. Do not distribute a workbook containing other users' confidential data.