From the Not Just Numbers blog:

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

From the Not Just Numbers blog:

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

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

From the Not Just Numbers blog:

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

From the Not Just Numbers blog:

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.

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

From the Not Just Numbers blog:

I’m sure most of you are familiar with the SUM command for totalling ranges of cells, but have you heard of the SUBTOTAL command? – and if so, do you know what it does?

I must admit that I had only looked at the SUBTOTAL command in any detail this week – prompted by a question from a reader. I had never really used it, because I normally advocate doing any analysis of a data list separately using PivotTables, rather than within the list itself and didn’t realise that it offered some interesting differences to just using the SUM command.

At first glance it would appear to be the same, but it has a number of additional powers!

I am aware that most of you will know this next bit, but I have included it for completeness:

The SUM command is used as follows:

=SUM(number1,[number2],….)

only number1 is required and should be a number, or a reference to a number or range of numbers.

So, number1 could be, for example, 3, A4, A1:A4 or a named range.

The same rules apply to number2 and number3 all the way up to number255, except these are all optional.

The SUM function then returns the total of all of these numbers.

The SUBTOTAL command has very similar arguments except it has one additional argument in front of them:

=SUBTOTAL(function_num,ref1,[ref2],….)

ref1 and the optional arguments ref2 to ref254 are very similar to number1, etc. in the SUM function, however they must be references to cells or ranges of cells – i.e. not actual numbers.

The function_num argument must be between 1 and 11 or between 101 and 111. This argument determines how the function is to summarise the numbers. The Excel function that will be applied for each of function_num 1 to 11 is given below:

1 AVERAGE

2 COUNT

3 COUNTA

4 MAX

5 MIN

6 PRODUCT

7 STDEV

8 STDEVP

9 SUM

10 VAR

11 VARP

This is the first real difference between SUM and SUBTOTAL. SUBTOTAL can mimic any of these 11 functions.

The second difference is more subtle. You would expect the following two functions to return the same result:

=SUM(A1:10)

=SUBTOTAL(9,A1:10)

and in most cases they will. The difference becomes apparent when you apply a filter to the data. This will have no effect on the SUM result but the SUBTOTAL will exclude any rows hidden by the filter from the calculation. This could be very useful if you regularly work with a data table that you filter in place using the AutoFilter facility, as it will show you the total of the displayed rows.

You can further affect the result by using function_num 101 to 111. These work exactly the same as 1 to 11 but exclude rows hidden using the Hide command as well.

One final difference is that the SUBTOTAL command will ignore any other SUBTOTAL commands in the range being summed, thereby avoiding double-counting. Thanks to Jeremy for pointing out that I’d missed that one!

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

From the Not Just Numbers blog:

Please accept my apologies for the lack of posts these last couple of weeks – I’ve been away on holiday, chilling out on the Isle of Skye (that’s me in the picture relaxing on the coral beach near Claigan).

I had intended to write some posts in advance but it didn’t exactly work out as planned!

Now I’m back, I thought I’d write a (vaguely) holiday related post.

If you want to know how to work out the number of working days between two dates (allowing for weekends AND holidays), read on.

To do this, we can use the NETWORKDAYS function.

The syntax for this function is:

=NETWORKDAYS(StartDate,EndDate,[Holidays])

I would not recommend entering the dates directly into the function. This is not only good advice for making this particular function work, but it is best practice when referring to any variable in Excel – enter its value in a cell and refer to the cell.

Ignoring the optional argument, if A1 contains 1/8/2014 and A2 contains 31/8/2014, then

=NETWORKDAYS(A1,A2)

returns 21, being the number of working days in August 2014 (if you ignore public holidays).

The best way to use the optional third argument is to refer to a range, where you can enter holiday dates. So, say we add the third argument as follows:

=NETWORKDAYS(A1,A2,B1:B20)

We can now enter the dates of any holidays in cells B1 to B20 and these will be excluded from the calculation.

So, in the UK, last Monday was our August Bank Holiday, so if we enter 25/8/2014 into any of the cells from B1 to B20, the formula returns 20.

A practical way of using this functionality would be to enter the whole year’s holidays into the range referred to (B1:B20 in our example), any of those dates that fall between the start and end dates would then be excluded from the calculation.

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

From the Not Just Numbers blog:

Do you ever wonder how you get those professional looking tick boxes (or check boxes) in your spreadsheet?

They’re easier to add than you think, and it’s pretty straight-forward to have calculations depend on the result of the tick box.

In this post, I’ll show you how to do both.

Before you can add a tick box, you need the Developer Ribbon enabled. If you don’t see it as one of the named Ribbons at the top of Excel, then you can add it by selecting File – Options – Customize Ribbon and then ticking Developer in the list of Main Tabs on the right-hand side.

To insert a tick box, go to the Developer ribbon and click Insert in the drop-down menu that appears, click the Check Box (Form Control) icon. This is the tick box under the heading Form Controls – if you hover over it you will see the name “Check Box (Form Control)” appear.

Your cursor will become a cross and you can now click where you want the tick box to appear.

The box will appear with some default text to the right of it (this will usually be Check Box 1 if this is the first one that you have inserted into the spreadsheet. This text can be edited (or deleted) by double-clicking on the text and editing or deleting as required.

This is your tick box created and you can now click it to toggle between ticked and unticked.

However, I did say that I would show you how to use the result of the tick box.

If you right-click on the tick box you will see the menu option “Format Control”. Click on this and go to the Control tab.

You will see a box entitled Cell Link. Enter a cell reference here, where you wish to store the result of the tick box (let’s say C3). Now when you click the tick box, cell C3 will switch to show TRUE, and when you untick the tick box, C3 will show FALSE.

You can then use cell C3 in a formula. The most common way to use it would be as the criteria argument of an IF function. As C3 contains the logical value TRUE or FALSE, then this is all you need as your criteria argument, so:

=IF(C3,100,0)

will return the value 100 if the tick box is ticked, or 0 if not.

It’s as simple as that!

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

From the Not Just Numbers blog:

When working with financial transactions, we often want to analyse them between our financial periods rather than just calendar months.

This is pretty easy if your financial year is January to December and  your periods are the calendar months, but what if your year starts in April or you have four and five week periods – or both?

I’ll explain how to address each of these scenarios in this post.

In each case, let us assume the date we want to identify the period for is held in cell A2.

Calendar Year with Calendar Months

This is by far the simplest scenario as we can use the MONTH function to strip the month number from the date, so =MONTH(A2) will do the trick, returning 1 for January, 2 for February, etc.

Non-Calendar Year with Calendar Months

This situation is slightly more complicated but we can use our knowledge of the IF function to address it, along with the MONTH function used above.

Let’s say our year end is March So the calendar month 4, is our period 1. So deducting 3 from the month will give us our period, however this will only work from April onwards. If our date is in February and we deduct 3 from the month, we will have -1, rather 11, which is what we require. This is where the IF statement comes in.

If the month is greater than 3, then we want to deduct 3, otherwise we want to add 9 (which is 12 -3).

So, our formula becomes:

=IF(MONTH(A2)>3,MONTH(A2)-3,MONTH(A2)+9)

The 3 is the month of our year-end and the 9 is 12 minus the month of our year-end, so if our year ended in October (month 10), then the formula would be:

=IF(MONTH(A2)>10,MONTH(A2)-10,MONTH(A2)+2)

Non-Calendar Year with Non-Calendar Periods

For this situation, we need to take a different approach. We don’t have a simple rule for the periods, so we will need to tell the spreadsheet them. We can do this with a LOOKUP table.

We should have a two-column table with the first column for the  start date of each period (earliest to latest), and the second column for the period number.

We can then use VLOOKUP with a TRUE (or omitted) fourth argument to look up the correct period.

Say the lookup table is in cells D2:E13 (when doing this for real, I would recommend putting this on a separate tab), then our formula would be:

=VLOOKUP(A2,$D$2:$E$13,2) will return the period number.

I have applied the dollar signs to the range so that it stays fixed when copied to other rows.

This last approach is obviously the most flexible and can be used for many other scenarios, e.g. identifying VAT quarters for example.

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

From the Not Just Numbers blog:

This weekend, I went on a bushcraft training weekend with my fifteen year-old son, building our own camp in the woods, etc.

This was an amazing experience and is still fresh in my mind as I write this blog post. So, it got me thinking, “In the Excel jungle, what would I have in my survival kit?”

I thought I’d ask myself the following question (and I’d love to hear your answers too, in the comments) – If I could only have three functions/features in Excel, what would they be? I am going to assume that basic mathematical functions like adding and subtracting and SUM are there, but what are the top three additional features that I wouldn’t want to be without?

So, here are my top three (in no particular order):

1. VLOOKUP – Having Excel look up information from tables elsewhere in the spreadsheet is an incredibly useful facility. I did, however, think for a while about this as I would have preferred INDEX and MATCH, but that would have taken up two of my choices!
2. PivotTables – I use them all of the time and they remove the need for formulae in many cases.
3. The IF statement – being able to get Excel to essentially make decisions based upon the information it has available is too useful a feature to give up!

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

From the Not Just Numbers blog:

A quick reminder before we start, that the 20% off deal on Mynda Treacy’s Excel Dashboards course expires on Thursday. So, if you want to take advantage, hop on over there now while you’ve still got the chance.

This week, I’ve got a simple tip to quickly name and access ranges in Excel.

You can use the Name Manager on the Formulas ribbon to define range names, however I will show you a much quicker way to name fixed ranges, as well as go to them and highlight them once they exist.

This tip uses the Name Box at the top left of your screen, just below the ribbon to the left of the formula bar.

This box normally shows the cell reference of the active cell (the cell that is currently selected). If you click in cell B6, for example, you will see the name box shows “B6”.

While you are in cell B6, click in the Name Box and type the name “Fred”. Click in another cell and the name box will change to the cell reference of the cell you have clicked in. Now click back on cell B6 and notice what happens. The Name Box says “Fred”! You have successfully renamed B6, Fred!

You can do the same thing with a range of cells. If you highlight the range C5:E10, for example, and type “Bill” in the Name Box, click in another cell and then re-highlight the range C5:E10, the Name Box will show “Bill” again.

Note that these names follow the same rules for named ranges named in any other way – most notably, they should not include any spaces.

What’s more, if you click the dropdown arrow next to the Name Box, you will see Bill and Fred are both listed there. If you select one of them from the list, you will go to that range and highlight it. This works from any of the other worksheets in the workbook too.

You might not find too many reasons to name cells Bill or Fred, but if B6 was renamed TaxRate, then that might be useful. You could use the name TaxRate in any formula (instead of B6) and if you need to check what the TaxRate is, or change it, you can go to that cell by selecting it from the dropdown in the Name Box.

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