Display User-Specific Rows in Excel with a PIN
Question details
Create a login-style Excel report that displays rows associated with a selected user after a PIN is entered.

- 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.
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.
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.
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.
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.
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.
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.

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. 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. 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. 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. 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.

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.




