There are times when you might want to make an entry in an Excel file, range or cell that not only links to another file, range or cell but actually goes to that file, range or cell at the click of a button. Just as on a web page, Excel allows us to create a hyperlink to somewhere else on your computer or network or even on the internet. Setting up and editing a hyperlink is very straightforward as this example will show.
This morning someone asked me to show him how to set up hyperlinks in an Excel file that confirms if and when an invoice had been paid. Now, what you are about to see is not foolproof or even the best way to manage this situation but it is what the client wants to do: it’s how they manage their small project invoices.
In their Excel file this client has a table that records for each invoice such details as:
- Name of supplier
- Address of supplier
- Invoice number
- Paid or not
Actually, it doesn’t matter what is in their file because all we are interested in is the final column, paid or not.
The brief, then, was to get Excel to point to a PDF copy of an invoice that has been paid and that will sit in the same folder as the Excel file itself.
Every month, the ZIP file will be sent off to a supervisor/controller who is monitoring this project and its payments.
To set up the hyperlink: firstly, ensure that the PDF invoice files are in the folder they are meant to be in.
- in the paid or not paid column type the word yes or paid or cheque issued … whatever you want … keep it simple and I will enter the word yes for all invoices that have been paid.
- good spreadsheet practice says that you should enter something in the unpaid cells just to show that you haven’t forgotten them. Leaving these unpaid cells blank is not good!
- select insert then click Hyperlink icon
- at this stage there are four choices:
- link to an existing file
- link to a place in this document
- create a new document
- link to an email address
- for this example, we are going to link to an existing file so click to choose the file to link to: the invoice file relevant to the current cell … this is in the folder you have created
- check that the Text to Display says “yes” … you can edit that at any time by coming back here at any time in case you forgot to type yes or even made a mistake!
- when you are happy with what you have done
- click OK
Your hyperlink is ready now and you can test it: to test it just click on the displayed text in the cell/link you just created and it should open the file or worksheet that you have linked it to.