06-03-2020 03:25 PM
06-03-2020 03:25 PM
Hello! I work at a community college as a Program Coordinator for non-credit classes. I have an online class and in order for students to access their class, they have to create an account with their student ID. Students have to call or email me for their ID (and it could be a class of 20+) but I was wondering if there was a way that I could send a spreadsheet of the class roster with the student ID numbers and birth dates, and the students can enter their birth date as the "password" to unlock their student ID... or something like that.
I know that Excel currently has something like this set up because (on a separate occasion), I was sent an excel spreadsheet via email and the only way I could view it was if I put in my birthdate as the password (when I opened the spreadsheet).
That being said, is it possible to do something like this in Excel but 1. create the password as a unique cell in the spreadsheet, and 2., have that password only show certain cells in the spreadsheet?
I might be asking for too much but I thought I would ask! Thank you all for reading and thank you for your time.
06-03-2020 06:47 PM
@AraliAlvarado Please see the attached workbook for a suggestion. The worksheet is currently protected with a password that only you should know to prevent any editing. Each student will open the sheet and enter their birthdate in the format of ddmmyy directly into the formula bar (that's the only place they could select because everything else is locked). Their Student ID will be populated if the password matches your record.
Your records of Student ID and passwords are in column A and B (currently hidden).
You will unlock the sheet to unhide columns, make changes, etc. by the password "teacher".
Once you are done with your changes, go to Review/Protect Sheet/ enter your desired password, tick the box "protect contents ..." right below, ensure the only box of "Select unlocked cells" is ticked.
06-03-2020 07:26 PM
Excel's password to open the file is pretty strong, but it's internal security has historically been pretty weak (such as worksheet protection and vba project protection). I don't know if 365 has added new features that might be useful (I've never used excel for sensitive information, so don't keep up with its security features).
If you change the .xlsx to .zip, open it, and then open the worksheet file in a text editor, you can remove the sheetprotection xml tag, save, and then change the file extension back to .xlsx. Then, open the file in excel and unhide the columns containing everyone's passwords.
Or, they can open the vba editor and execute a copy command to copy the hidden cells on sheet1 to a new sheet and expose the data that way.
06-04-2020 05:55 PM
@JMB17 I learnt a new thing from you today. Thanks a lot for sharing your knowledge of about Excel protection vulnerability. I do not know that it is so easy to crack the password-protected feature.
@AraliAlvarado From what JMB17 shares, I do not think it is a good idea to include confidential data in an Excel workbook to share at all. I just tried the first method and everything is revealed.