From the Not Just Numbers blog:

The HYPERLINK function can be very helpful in Excel for creating multiple links to websites, other documents or cells in the current document based upon cell data.

You can create a static hyperlink without it, but if you have a list of URLs or file paths, the function can allow you to dynamically create links to them without having to create each one individually and with the added advantage that the links will update if the underlying data is edited.

It’s a really simple function to use, but the Excel help function is very vague on how to use it to link to cells on another sheet in the same workbook.

First of all a quick introduction on how to use the HYPERLINK function.

It’s syntax is:

=HYPERLINK(Link address,[Friendly name])

Where the Link address can be a file path, a cell location or a URL. The Excel Help on the function gives a useful list of the syntax for each of these (except for a cell on another sheet!).

The Friendly name is optional and is the string you want to appear as the hyperlink. If this argument is not entered, the Link address will show in the cell.

Take a look at the spreadsheet below:

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:

Just a short but very useful tip this month. But before we start, I’d like to thank those readers who donated to Macmillan Cancer Support when I ran the Great North Run earlier this month. All donations are much appreciated and the page is still open for a little while longer if anyone would still like to donate.

Have you ever written a long formula that isn’t giving the result you’d expect (or is returning an error), but you don’t know which part is the problem? If you’ve ever written a long formula, then it’s a fair bet that you have!

Well, there’s an easy little trick that you might not be aware of, that can make this much easier to investigate.

The problem:
Say you have a formula such as:

=IFERROR(IF(A2>3,1,0),0)

It is returning 0 and you don’t think it should be. This could be because A2 is 3 or less, or because the IF function is returning an error and you don’t know which.

The solution:
You can evaluate any expression within the formula individually, using the F9 key.

Within the cell or formula bar, highlight the section of the formula that you want to evaluate. In this case it would be useful to see whether the output of the IF function alone is a zero or an error, so highlight the IF expression:

=IFERROR(IF(A2>3,1,0),0)

and press the F9 key. The formula will now show the result of the selected expression within the full formula, rather than the expression itself, e.g.

=IFERROR(0,0)

or maybe

=IFERROR(#N/A,0)

You can do this with as many expressions within the formula as you want, as long as you highlight an expression that on its own would return a result.

In this formula you could have alternatively evaluated A2 or A2>3.

IMPORTANT NOTE: When you have finished doing this, leave the cell by pressing Esc rather Enter so that you do not overwrite the expressions with the results.

This is a simple formula for illustrative purposes, but if you have a really long complex formula, this tool can be invaluable.

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:

Let me start with an apology for leaving it so long without a post. It’s been a busy time work-wise and, in addition, I’ve taken up running to finally lose the excess weight and run the Great North Run half marathon next month. After spending the last 20 years or so doing very little exercise while sitting in front of a computer screen, I decided I had to do something about it – and raise some money for a good cause along the way!

OK! Excuses over! I’ve got a very powerful but often misunderstood function to tell you about.

On the face of it, SUMPRODUCT does a very simple thing, it multiplies arrays together and sums the results. Unless you’re a serious mathematician, you’re probably thinking that this is not something you’ve ever had a desire to do! But bear with me.

First of all, here is a simple explanation of how it works:

=SUMPRODUCT({5,6,4},{7,4,12}) returns 107 because:

You can enter up to 255 arrays like this (as long as they are all the same size) and SUMPRODUCT will multiply them then sum the totals as above. That’s basically what SUMPRODUCT does. Now let’s look at why this is very useful.

First of all, the SUMPRODUCT function allows you to work with arrays without entering the function using Ctrl+Alt+Enter, which means that you don’t run the risk of accidentally clicking into the cell and clicking enter, then wondering why your array formula has stopped working. If you don’t know what an array formula is ignore this point, as you don’t need to worry about it with SUMPRODUCT. If you do, then you’ll know what I’m talking about!

More importantly, the arrays entered into SUMPRODUCT can be formulae that result in arrays. The formula above could have been entered as:

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:

Regular readers and clients will know that I’m a big fan of PivotTables. If you format your data into a table format, you can create all sorts of reports quickly, easily and flexibly using them.

In this (short) post, I want to highlight a little trap to watch out for when you filter a PivotTable – particularly if you are filtering out items, such as blanks for example.

On any of the Row, Column or Report Filter fields you can apply a manual filter by ticking (or unticking) individual entries.

In this field I have filtered out the blanks by unticking “(blank)” and leaving everything else ticked. This is exactly the kind of situation that can lead you to fall into the trap.

To understand the problem, you need to think about how a PivotTable works.

The entries in the PivotTable come from the data itself. So this list only includes the Job Descriptions that currently appear in the data (and possibly some that used to, depending on your settings), so the list above may not be comprehensive in future, as new job descriptions appear in the data.

Let’s say a new job description of “Clean Fridge Freezer” appears in the data.

Our intention above was to exclude (blank), but Excel doesn’t know this. We could just as easily have meant to only include “Clean Oven”, “Clean Oven and hob”, “Clean Range” and “Clean Range and Fridge Freezer”.

So should “Clean Fridge Freezer” be included in the filter or not? We have not given Excel any clue on this, but Excel needs to do something with it, so there is a default position.

By default, Excel assumes that we have pro-actively ticked the things we want to include – which seems a perfectly reasonable assumption, but if that is true, then “Clean Fridge Freezer” hasn’t been ticked, so should not be included.

In our example where we were essentially “unticking to exclude”, as opposed to “ticking to include”, we need to tell Excel this. Fortunately, this is a simple process.

We just need to change a setting in the Field Settings for this field (Job Description in our example), which can be accessed by right-clicking on the Field itself in the PivotTable (or left-clicking on the field in bottom section of the Field List) and choosing Field Settings.

We then need to tick the box to “Include new items in manual filter”:

Once this is ticked, new items will be included by default (just for this field). Note that this setting will not change the selection currently in place, so if you hadn’t ticked it when you set the filter up and only discover this when a new item isn’t included, you will still need to manually tick the new item. However, all future new entries will be automatically ticked.

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 have covered both SUMIFS and COUNTIFS in earlier posts.

To recap:

You can use COUNTIFS to count the instances of a value in a range, e.g.

=COUNTIFS(A1:A100,”H”)

will count how many cells with value “H” there are within the range A1:A100.

You can also add additional pairs of criteria, so that

=COUNTIFS(A1:A100,”H”,B1:B100,3)

will count how many cells with value “H” there are within the range A1:A100, where the corresponding value in the range B1:B100 is 3.

SUMIFS works in a similar way, but you enter an additional range as the first argument that is summed, e.g.

=SUMIFS(C1:C100,A1:A100,”H”,B1:B100,3)

will sum the entries in column C, where the corresponding entry in column A is “H” and B is 3.

Where these are both very useful functions, one particular flaw is that they are not case-sensitive.

So, there is no difference between

=COUNTIFS(A1:A100,”H”)

and

=COUNTIFS(A1:A100,”h”)

A client recently faced this problem in reporting from a holiday spreadsheet where a full day’s holiday is marked with an “H” and a half-day is marked with a “h”.

The client wished to total the number of days holiday, which if COUNTIFS was case-sensitive, would be as simple as:

=COUNTIFS(A1:A100,”H”)+(0.5*COUNTIFS(A1:A100,”h”))

But unfortunately, we can’t use COUNTIFS at all!

We do have a function in Excel that allows us to compare two values, taking account of case, and that is the EXACT function.

=EXACT(A1,B1)

will return TRUE if A1 is exactly the same as B1, so

=EXACT(“H”,”h”)

would return FALSE.

Unfortunately, we can’t use this with COUNTIFS, because COUNTIFS doesn’t allow us to simply insert a condition, as it requires us to enter the conditions in pairs, as above.

We can, however, use the SUMPRODUCT function, which works with arrays, to apply the EXACT function to a whole array (e.g. A1:A100).

=SUMPRODUCT(EXACT(A1:A100,”H”)*1)

The array EXACT(A1:A100,”H”) will be 100 TRUEs and FALSEs. By multiplying these by 1, they become 1s and 0s. So, this will now calculate the number of capital Hs for us.

To complete our example, we can add this to:

=SUMPRODUCT(EXACT(A1:A100,”h”)*0.5)

Giving us:

=SUMPRODUCT(EXACT(A1:A100,”H”)*1)+SUMPRODUCT(EXACT(A1:A100,”h”)*0.5)

which will return the number of Hs added to half the number of hs, giving us our total of holiday taken.

We can easily add multiple criteria too, as well as change it to the equivalent of SUMIFS.

So,

=COUNTIFS(A1:A100,”H”,B1:B100,3)

can be made case-sensitive by using

=SUMPRODUCT(EXACT(A1:A100,”H”)*1,(B1:B100=3)*1)

as SUMPRODUCT will multiply the resulting arrays of 1s and 0s, so the resulting array will only show a 1 when both conditions are true.

=SUMIFS(C1:C100,A1:A100,”H”,B1:B100,3)

can be made case-sensitive by using

=SUMPRODUCT(EXACT(A1:A100,”H”)*1,(B1:B100=3)*1,C1:C100)

as the we have added the array C1:C100, being the values in column C, which are multiplied by the 1s and 0s

So, say, cell A2 contains “H”, B2 contains 5 and C2 contains 30.

The second position in each of the arrays are as follows:

First array: A2 does exactly equal H so the result is 1
Second Array: B2 doesn’t equal three so the result is 0
Third Array: C2 is 30, so the result is 30

The SUMPRODUCT function multiplies these together so that the 2nd position in the resulting array is:

1 x 0 x 30 = 0

And, say, cell A3 contains “H”, B3 contains 3 and C3 contains 15.

The third position in each of the arrays are as follows:

First array: A3 does exactly equal H so the result is 1
Second Array: B3 does equal three so the result is 1
Third Array: C3 is 15, so the result is 15

The SUMPRODUCT function multiplies these together so that the 3rd position in the resulting array is:

1 x 1 x 15 = 15

Hopefully you can see that if both conditions are true, we get the value in column C, but if either are false, we are multiplying by zero, so the array that is summed by the SUMPRODUCT function, will only include the values in column C, where the first two conditions are true. This is exactly the same as a SUMIFS, except it is case-sensitive!

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:

In this post I want to touch on a topic that I’ve not written a lot about on this blog – printing.

There are many settings in Excel that you can use to influence what is printed, and I’m sure I will revisit this topic to cover some more of them soon.

In this post, I simply want to focus on how to determine how a single worksheet appears on paper, when you click the Print button.

This is essentially driven by two elements:

• The Print Area – which determines what section of the sheet is printed
• The Page Breaks – which determine how this is spread across the physical pages printed.

Print Area

Page Breaks

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:

Before I start this post, I must first apologise for the lack of posts over recent months. After returning from my summer holidays, my workload hasn’t really eased (which isn’t a bad thing), and my blogging has suffered.

I wanted to get an (albeit short) tip up before Christmas, though, and it is one of my New Year resolutions to post at least once a month in 2017.

Your Christmas tip then is a simple answer to a particularly annoying aspect of data validation dropdown boxes.

A feature of data validation drodpown lists in Excel is that if the cell already contains a value from the list, the dropdown starts with that value selected, and you need to scroll up if you want to select an earlier value.

This is normally fine except for the following (very common) scenario.

It is good practice to have the dropdown list look at a range where its entries can be edited, and to leave space at the bottom of the list to allow the list to be added to.

However, a side-effect of this is that, when your cell is empty, the dropdown will start at the bottom of the list, as it sees the empty cells at the bottom as a match for the current entry (nothing).

The simplest answer I have found to this is to have a blank cell at the top of the list as well. As this matching feature matches the first match it finds, your dropdown list will now start at the top (for an empty cell).

I often find the neatest way to do this is to have a blank row under the headers that doesn’t look like part of the list:

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:

Logic (particularly Boolean Logic) plays a huge rule in computer programming and circuitry, but is also very useful in Excel.

It is fundamental to the IF statement which in itself is such an important tool in Excel, but has many other roles.

Essentially, when we talk about logic in this context, we are talking about expressions that can either be true or false, e.g.

A1=100
B5<=34
C2=”Yes”

You can simply type the expression as a formula in a cell putting an “=” in front of it, so if you enter:

=A1=100

into, say, cell B1, then cell B1 will show the word TRUE if A1 is 100 and the word FALSE otherwise.

As mentioned earlier, one of the more common places to use these expressions is in an IF statement, which works as follows:

=IF(expression,value to return if expression is TRUE,value to return if expression is FALSE)

e.g.

=IF(A1=100,”Yes”,”No”)

will return Yes if A1 is 100 and No if not.

Some logical functions
There are a few logical functions which extend the possibilities, in particular we will look at NOT, AND and OR.

NOT reverses the result of a logical expression.

If

=A1=100

returns TRUE, then

=NOT(A1=100)

returns FALSE, and vice versa.

AND allows you to list multiple expressions and returns TRUE only if ALL of the expressions would individually return TRUE. Otherwise it returns FALSE.

OR works the same but returns TRUE if ANY of the expressions are TRUE.

e.g.

=AND(A1=100,B5<=34,C2=”Yes”)

will only return TRUE, if all three of those statements are TRUE.

Whereas:

=OR(A1=100,B5<=34,C2=”Yes”)

will return TRUE if any of the three conditions are TRUE.

By using this type of logical expression, particularly within an IF statement, or  a Conditional Formatting condition, we can control how a spreadsheet both looks and calculates based upon the content of cells.

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:

Before I start, a couple of announcements.

First of all, please accept my apologies for the time between posts. A combination of workload and a family bereavement have slowed me down somewhat!

Secondly, if any of you will be in and around London tomorrow (Wednesday) night, the ICAEW are launching their Spreadsheet Competency Framework, with some top speakers from the world of Excel. This document (as suggested by its name) is intended to provide a framework to assess spreadsheet competency, and was developed by the Institute’s Excel Community Advisory Committee, of which I am a member. The event is free to attend, and further details can be found at:

http://www.icaew.com/en/technical/information-technology/it-faculty/it-faculty-events

I hope to see you there.

Right…on with the post.

There are many reasons, you might want to rank a list of numbers in Excel. One I do quite often, is produce a league table. By calculating the ranking of a number and then using lookups to populate a table in rank order, we can easily produce a league table, like the example below:

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