A recent question we had went as follows: “Every time I open my “Example “ excel sheet, I get messages asking for a password for a completely different document, and a links message. Why is Excel asking for a password on an unprotected file? How can I stop it from happening or asking it without just clicking don’t update when I open it?
Table of contents
The issue here is that the password is on the file that Excel is trying to link to. Excel is being extra secure and making sure that you have the right password to also see the information from the file that your spreadsheet is linked to.
So if you open a spreadsheet that you know doesn’t have a password, yet you get a screen like this, it means that Excel is trying to link to and get information out of a spreadsheet that is password protected.
If it is purposeful then you need to get the password and work with it. But it it is annoying and you want to stop it happening you need to follow a few simple steps.
Firstly you need to find all the cells that link to this protected spreadsheet. Note in the picture above it tells you which spreadsheet it is looking for the password. You can use this or else see all the files that the spreadsheet is linked to.
YouTube Excel asks for password on unprotected file
Find the Excel files that your spreadsheet links to
With your spreadsheet open, click on DATA, then EDIT LINKS to see the screen below.
Note that it tells you all the names of the spreadsheets that your spreadsheet links to. This tool is very useful but for now we are only interested in the EXACT name of the spreadsheet. Write it down or remember all of it including the .xls, xlsx etc.
Now we need Excel to find any cell that references these spreadsheets. Because of the initial screen which asked for the password, we know that the problem one is called MarkModel v1.xls so this is what we will look for.
Break all the Links instantly
If you know for sure that the links are not important, you can just push the Break Links button shown above and all cells that contain that link will be replaced with values. Although this is a nice quick way to do this, you may be worried about which cells have the links as perhaps it needs to stay.
Finding all the cells that link to an External spreadsheet
We can use the FIND tool for this. You can activate the FIND tool either by using the shortcut CTRL + F or under the HOME tab, on the far right under FIND & SELECT.
Note in the image below we have typed in the exact name of the file including the .xls (this just makes sure you don’t waste your time on other cells that contain similar names).
Note that we also clicked on the OPTIONS button to expand where we search and we have chosen WORKBOOK and FORMULAS. If you don’t choose WORKBOOK, Excel will only search the current sheet and you will need to repeat it on each sheet. By making sure it searched within formula we will only look for the links.
You can now either choose FIND ALL or FIND NEXT and work through the cells. You need to decide how important the cell is, whether it is used elsewhere and whether you can either break the link and keep the number or text, or delete it completely.
If you work through the cells and you are happy they all can be broken, use the step above to break links.
If you run the FIND and it says it can’t find any matching cells, then it means the link is not in a cell but in something else. You will need to look through some or all of these items to find the link:
- charts
- named ranges
- data validation
- conditional formatting
Related
Excel slicer with password protection
Double click Excel cell to follow formula not working