Excel Tip: Using the HYPERLINK function

This week, I want to introduce you to the HYPERLINK function.

This function allows you to add hyperlinks to your spreadsheet for both navigation, and to link to external websites or files.

And, because it is an Excel function, you can use cell contents elsewhere in the spreadsheet to determine the hyperlink.

The syntax of the HYPERLINK function is as follows:

=HYPERLINK(Link,[Friendly Name])

Link can be a URL or a file reference. So, some possible examples are:

"http://www.notjustnumbers.co.uk"
"C:\My Documents\SalesSpreadsheet.xlsx"
"[C:\My Documents\SalesSpreadsheet.xlsx]Consultancy!A1" (note that this one will go to cell A1 of the Consultancy tab of SalesSpreadsheet.xlsx)

Friendly Name is optional and is the text that you want to use as the hyperlink. So, for example:

=HYPERLINK("[C:\My Documents\SalesSpreadsheet.xlsx]Consultancy!A1","Consultancy Sales")

will show a clickable hyperlink that says Consultancy Sales - and clicking on it will take you to cell A1 of the Consultancy tab of SalesSpreadsheet.xlsx.

If this optional argument is not entered, the Link itself will appear as the hyperlink.

The function really comes into its own though when we use other cell addresses in the arguments. If you have two columns of data being company name and website address, for example, then you could have a third calculated as

=HYPERLINK(B1,A1)

which will show the company name (from column B) as a clickable hyperlink that will take you to the company website (from column A).

Or you could use text functions such as the ampersand to create the links. For example you could have a list of sales transactions and the documents stored as Word documents with the invoice number as the file name.

Say column A contains the invoice number and the directory the invoice documents are in is C:\Invoices\, The following HYPERLINK formula will give you a clickable version of the invoice number that takes you to the document:

=HYPERLINK("C:\Invoices\"&A1&".docx",A1)

If A1 contains the invoice number 324, then the function will return a clickable 324 that takes you to the document C:\Invoices\324.docx.


Before I go, I have some great news. Mynda Treacy's Excel Dashboards course is once again available - for a limited time.

Order before 16th October 2014 to get 20% off and, as a special offer from me, enter the code FEECHAN when you order and I will send you my Introduction to Pivot Tables course absolutely free.

Click here to find out more about the Excel Dashboards course and to take advantage of this offer.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: What's the difference between Delete and Clear?

There was a great response to my Filtered Excel Training offer last week. If you missed it you can still sign up to get 53 Online Excel Training modules absolutely free.

Also, if you are looking for an Excel training solution across your business, take a look at Filtered's business offering and receive 20% discount as a Not Just Numbers reader.

On with today's post. Whether we have made a mistake or are tidying up a spreadsheet, there are many occasions where we wish to delete things that we, or someone else, has already done.

Excel offers a few ways to do this - and they're not all the same. Do you know what each of them do? If not, read on.

The most common method of deleting in Excel is to use the Delete key on your keyboard. This will delete the contents of the selected cell or cells, however it will leave any formatting intact. This makes it very useful for correcting incorrect data input, but not ideal when you are tidying up a spreadsheet and you don't want any old formats hanging around.

The next option (also called delete) is when you select Delete from the right-click menu or the Home ribbon. This option deletes the actual cell itself, giving you the option to move cells left or up, or to delete the entire row or column. This is quite different to the delete key as this is changing the structure of the spreadsheet.

Finally, there is the Clear option. This is available from the Home ribbon and offers the following options:

• Clear All
• Clear Formats
• Clear Contents
• Clear Comments
• Clear Hyperlinks

These options are pretty self-explanatory and "do what it says on the tin", but it is worth commenting on the Clear All, as this is very useful when cleaning up a spreadsheet as it gets rid of any traces of what was there before.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Online Excel Training - Not Just Numbers readers can get 53 modules from Filtered

We've got a slightly different post this week. I want to tell you about a new exciting partnership I have agreed with Filtered, providers of excellent on-line training in Excel and Office.

And to cement this partnership, Filtered have agreed to provide Not Just Numbers readers with the first two sections of their Excel course completely free. That's 53 modules giving you a very decent grounding in Excel - absolutely FREE!

To take up this offer, just click here (all you need to do to set up your account is enter your name and email address and to choose a password).

To ensure that you get the most out of your training in as little time as possible, Filtered provide you with a unique test which is a combination of questions about your own perception of your Excel aptitude and actual questions about Excel. This then tailors the training to suit your specific needs - no need to trawl through all the stuff you already know to get to the useful bits.

Even without this offer, I would recommend Filtered as a great way to plug the gaps in your Excel knowledge, but getting so much of it free, in my opinion, makes it a no-brainer!

So, don't forget to sign up for your free account before you leave this page.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Knowing where you are in your spreadsheet, using the ROW and COLUMN commands

Sometimes in your spreadsheet, like in life, it is good to get your bearings to establish where you are.

This can be very useful in a formula if you want the result to be dependent on the location in the spreadsheet.

For example, you might want to:

• format odd and even rows (or columns) differently, or
• apply a different formula depending on how far down a list an item appears, or
• populate cells with a particular value, formula or format based upon  an entered width and height.

To do any of these types of things with a formula, the formula will have to know its own location (i.e. its row and column) first.

The ROW and COLUMN commands do exactly that.

The syntax of the ROW command is:

=ROW([Reference])

where Reference is the cell reference that you wish to return the row of. So,

=ROW(A5)

for example, will return 5.

But notice that Reference is in square brackets which, you may remember form earlier posts, means that it is an optional argument. So, the function can be written as just:

=ROW()

This will return the number of the row in which the formula sits, i.e. giving us the row information that we would need for all of our examples above.

Typically we will use it in an IF statement to drive the different outcomes in our examples, for example:

=IF(ROW()<=10,1,2)

will return a 1 in rows 1 to 10 and a 2 thereafter.

The COLUMN function works exactly the same way so:

=COLUMN(A5)

returns 1 (the column is returned as a number not a letter, so A is 1, B is 2, etc. - this does make it easier for adding and subtracting column positions). Also,

=COLUMN()

returns the column number of the column in which the formula sits.

One word of caution. You need to remember that you have used these functions if you start inserting or deleting rows and columns. Most functions will adjust accordingly and maintain the same relative references, but because these refer to the actual cell positions, their results will change if their position changes.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".