Excel Tip: Sorting a list - an introduction

Sorting a list is a very common use of Excel but there are a few things to consider when you do, and a few extra features that you may not be aware of.

The first task is selecting the data you want to sort.

If your data is in a table, if you select any cell in the table, Excel will assume that you want to sort the whole table.

If the data is not in a table, Excel will still guess what you want to sort, however I think it is better to pro-actively choose, rather than assume that Excel will get it right!

Make sure you highlight all of the rows in the range you want to sort. You can highlight whole columns by clicking on the column letters, which will ensure that all rows are included (not a good idea if you have other data below your list though).

Also, make sure that all the columns in your data are included, otherwise you could end up irrevocably breaking up rows of data, by sorting some columns and not others!

Once you have selected your data, click one of the sort buttons.

Clicking either of the two small buttons will sort based upon the first column of your data, either ascending or descending. The larger sort button (ringed above) gives you a lot more flexibility.

Clicking this will reveal the following box:

The first thing to check is that the tickbox "My data has headers" is correct, as, if ticked, this will treat the first row of your range as headers and not include it in the sort.

You can then choose which column to sort on. This is a dropdown of the column headings (if the tick box is ticked), or the column letters if not.

For the purposes of this introduction, we will assume that you want to sort by the contents of the cell (Cell Values in the Sort on dropdown), then in the final box you can select the order you want to sort in. The options will change, depending on the type of data in the column being sorted, e.g. A to Z for text, Smallest to largest for Numbers, Oldest to newest for dates, and all of their opposites.

Then click OK and your range will be sorted.

In a future post we will dig a bit deeper into the sorting options, including how to sort on multiple columns.

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: Some useful keyboard shortcuts to save you time

Hello stranger!

I must start by apologising for the huge expanse of time that has passed since my last post. My consultancy workload has increased over the last year or so and the blog has been the main casualty!

I'm still pretty busy and may not be able to post as regularly as I have at some times in the past, but I will endeavour to post at least a post a month.

OK, on with this month's post...

Personally, I am not a huge user of keyboard shortcuts - as I struggle to remember too many - but there are a few that I find particularly useful, and I thought I'd share those with you. I'm sure many of you have your own favourites, so please share them in the comments. Mine are all for the Windows version. Any Mac users please feel free to post the Apple equivalents.

Here are my favourites...

F4 - add dollar signs

I use this one all of the time. To add the dollar signs that fix rows and columns in formulae, you can simply press F4 while your cursor is on the cell reference in the formula, and each time you press it it will toggle to the next combination of $ signs in the following sequence - none, fix column and row, fix row, fix column and back to none.  If you don't understand how the dollar signs work, take a look at one of my most popular posts - The dollar sign ($) in a formula - Fixing cell references.

Ctrl+; - Enter today's date

This one is really useful when entering data into a spreadsheet, you can enter today's date into the current cell by simply pressing the Ctrl key and the semicolon together.

Ctrl+Shift+; - Enter the current time

Similar to the shortcut above, you can enter the current time into the current cell by pressing the Ctrl  and Shift keys and the semicolon together.

Ctrl+C - Copy
Ctrl+V - Paste
Ctrl+X - Cut

These are standard windows shortcuts, but are as useful in Excel as elsewhere. Be wary though about using cut/copy and paste generally, see these earlier posts if you aren't aware of them:

Beware - Cut and Paste IS NOT the same as Copy and Paste

Copy and Paste - avoiding unwanted baggage

Alt+F11 - Open Visual Basic Editor

This is a one for the coders out there and provides a quick method to open the Visual Basic Editor. This shortcut holds a special place in my heart as I managed to completely forget what it was on a live Webinar I was doing for the ICAEW!

F9 - Calculate

This forces a recalculation of your Excel Workbook, which is useful if you have manual calculation switched on. A lesser known use is that you can highlight a section of a formula and press F9 and it will swap the highlighted text for its value - this is great for trying to find where the problem is in a long formula! See this post for more details - How to evaluate individual parts of a large formula.

As I said, I'm not a big shortcut user, so I'm sure many will consider some of the ones I haven't listed a travesty, so please restore justice in the comments!

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

Merry Christmas...

Excel Tip: Use the HYPERLINK function to link to a cell on another worksheet

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:

The hyperlinks in column C are created using the HYPERLINK function, the formula in C2 being:

=HYPERLINK(B2,A2)

This can then be copied down the column. Columns A and B could be hidden or on a different sheet making the hyperlinks a user-friendly way of navigating to the websites.

Now let's say we had a Sales workbook with a sheet for each department and a Summary sheet listing all departments' sales, with the sales total being in cell H7 on each sheet. Let us also assume that the department name is used as the tab name for each sheet.

We want the summary sheet to show three columns as below:

...with column B showing the sales total in cell H7 on each of the sheets, and column C being a hyperlink to cell H7 on each of the sheets.

We can use the INDIRECT function in B2 as follows:

=INDIRECT("'"&A2&"'!H7")

This can then be copied down.

This is the same as writing

='Retail'!H7

Except we have used ampersands to concatenate the preceding single quote ('), the contents of cell A2 (Retail) and '!H7.

The reason for placing the single quotes around the tab name is to allow for spaces in the tab name.

You would then think that you could enter the following in cell C2:

=HYPERLINK("'"&A2&"'!H7","Visit "&A2)

NB: INDIRECT is not needed here because the HYPERLINK function expects a link in the form of a string.

or even:

=HYPERLINK("'Retail'!H7","Visit Retail")

...but each of these return an error when you click on the hyperlink.

What the Excel Help doesn't tell you is that when referencing worksheets in the same workbook with the HYPERLINK function, you need to prefix the sheet name with a #.

NB: If you enclose the sheet name in single quotes then the # comes before the single quote.

So:

=HYPERLINK("#'Retail'!H7","Visit Retail")

...will work. As will, for our example:

=HYPERLINK("#'"&A2&"'!H7","Visit "&A2)

...which can be copied down 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".

Excel Tip: How to evaluate individual parts of a large formula

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

Excel Tip: An introduction to SUMPRODUCT and why you should learn it

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.

I rated it one of the most useful and under-used functions in a recent survey of Excel experts.

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:

=SUMPRODUCT(A2:A4,C2:C4)

if cells A2, A3 and A4 contained 5,6 and 4 respectively and cells C2, C3 and C4 contained 7, 4 and 12.

But we could also (say) add 1 to each value in the first array before multiplying by entering:

=SUMPRODUCT(A2:A4+1,C2:C4)

resulting in 130  - as ((5+1) x 7) + ((6+1) x 4) + ((4+1) x 12)) = 130.

You're probably still saying, "So what?", but here's where it gets useful.

These formulae can use other Excel functions and, even more usefully, conditions that return arrays of Trues and Falses (converted into 1s and 0s).

Let's say that in the following data, we need to know the total quantity of product A sold in the North:

We could use SUMIFS as follows:

=SUMIFS(C2:C8,A2:A8,"North",B2:B8,"A") which returns 38.

Or we could use:

=SUMPRODUCT(--(A2:A8="North"),--(B2:B8="A"),C2:C8) to return the same answer.

This works as follows...

The formula (A2:A8="North") results in the array:

{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE} because these are the 7 results of the formula (A2="North" is TRUE, A3="North" is FALSE, etc.)

By placing the double minus (--) in front of the formula, it forces this to return 1s and 0s instead of TRUEs and FALSES, i.e.:

{1,0,0,1,0,0,1}

Our SUMPRODUCT formula therefore arrives at the same answer as the SUMIFS because the three arrays we are multiplying are as follows:

By multiplying the two arrays (A2:A8="North") and (B2:B8="A") after converting them to 1s and 0s, the result will only be a 1 if both conditions are true (as if either returns 0, we will be multiplying the other by zero).

Incidentally, if we don't multiply this by the C2:C8 range we can use this as an alternative to COUNTIFS

i.e. 

=SUMPRODUCT(--(A2:A8="North"),--(B2:B8="A"))

is the same as:

=COUNTIFS(A2:A8,"North",B2:B8,"A")

But why would you want to use this instead of SUMIFS, or COUNTIFS?

Well, SUMPRODUCT is a lot more flexible.

Let's say we wanted to know total sales value, rather than quantity.

We can't do this with SUMIFS without adding a new column (being column C x column D) and applying the formula to that, however with SUMPRODUCT we can just multiply by column D right in the formula:

=SUMPRODUCT(--(A2:A8="North"),--(B2:B8="A"),C2:C8,D2:D8) 

or let's say we wanted total sales of product A for North and South:

=SUMPRODUCT(--(A2:A8="North")--(A2:A8="South"),--(B2:B8="A"),C2:C8,D2:D8) 

Here we have added the two arrays together (-- is a +) - which will return a 1 if  column A is EITHER North or South, as one of the columns will be 1 if this is true whereas if it is not, both columns will be 0.

These are still pretty simple uses of the function, but hopefully this is enough to illustrate that they can go far beyond the capabilities of SUMIFS and COUNTIFS.

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: Look out for this when you use PivotTable filters

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

Excel Tip: Case-sensitive COUNTIFS and SUMIFS

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

Excel Tip: Setting the print area and page breaks

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

By default, the print area will be what is know as the Used Range of the worksheet. This is from cell A1 to the column of the rightmost cell with anything in it to the row of the cell furthest down the worksheet with anything in it.

This, however, can be changed by highlighting the range that you want as your Print Area and selecting Print Area, Set Print Area from the Page Layout ribbon.

If everything you want to print is not adjacent to each other, you can select multiple ranges by holding the Control key while you select them.

You can even set the Print Area as a dynamic range, but we'll leave that for a future post.

Page Breaks

Excel breaks up the Print Area based upon the settings on the Page Layout ribbon.

Here, you can set the page orientation, paper size, margins, etc. as well as set the scale, or more usefully, set the number of pages wide the Print Area should be considered to be and how many pages long (using the Width and Height settings).

These Width and Height settings are, by default, set to automatic, meaning that they will be determined by the page size and orientation, along with the scale set beneath them.

You can, however, fix them so that, for example, the whole Print Area is resized to fit on 1 page wide and 1 page tall (by setting them both to 1). Obviously, with that setting, there will be no page breaks.

Another typical setting is to set the width to 1 but leave the height as automatic. This will resize so that it is always only 1 page wide, but will insert as many page breaks as required for the height.

Most of the time, this is all you need to determine your page breaks, however, there are also times where you want the breaks to happen in specific places. You can preview where they are and move them around using Page Break Preview.

This is accessed from the View ribbon and shows blue lines where the page breaks are and a watermark telling you the page numbers. These blue lines can be dragged to wherever you need them.

That's about it, and the good thing is that all of these settings stay with the sheet, so you should only need to set them once unless the layout of the sheet 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".

Excel Tip: Ensuring that dropdown lists start at the top (and Merry Christmas)

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:

In the example above, we could use the range A2:A14 to drive the dropdown list and cell A2 would be the first match for a blank cell, rather than A12.

That's it for now, and 2016! May you and your family have a great, safe and Merry Christmas (or whatever holiday you celebrate) and a fantastic 2017.

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: Using Logic in Excel

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

Excel Tip: Ranking numbers in Excel

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:

The first Rank column is the one we are trying to populate here.

In Excel 2010 onwards we will use the RANK.EQ function, which replaces the RANK function in Excel 2007 and before. Both functions work exactly the same, and both exist in Excel 2010 to 2016, at least. If you know you may have users using Excel 2007 or earlier, use the RANK function - otherwise use RANK.EQ to future-proof your spreadsheet.

The RANK.EQ (or indeed the RANK) function's syntax is as follows:

=RANK.EQ(number,ref,[order])

where:

number is the number we want to know the rank of

ref is the whole range of numbers

order is an optional argument, which determines the order that the numbers should be ranked in. If this argument is zero, or omitted, the numbers are ranked in descending order (the largest number is ranked 1), whereas if this is 1 (or any non-zero value), the numbers are ranked in ascending order.

Where two numbers in the range are the same, they are both given the highest rank (when using the RANK.EQ function - there is a similar RANK.AVG function that gives them their average rank).

In our example the numbers 30,45,97, etc. are in the range B3:B12, so we would use the RANK.EQ function in cells C3:C12. In cell C3, we would enter:

=RANK.EQ($B3,$B$3:$B$12)

Notice that we have fixed all of the references (using the dollar signs) on the B3:B12 range, to ensure that this range stays fixed when we copy it down, but just the column on the B3 reference so that the row (and the number we rank) changes as we copy down.

If we use this function as it is, we will run into a problem, though. As there are two number 24s in our range, we get the following:

Notice that both 24s are ranked 8 (the highest rank), which means there is no rank 9 - hence our error in the league table when we try to look up 9. We need each rank to be unique for our league table to work.

We will need to use another formula to address this, and we can use COUNTIFS to count how many instances have occurred so far in the list.and if this is greater than 1, to add the difference to the rank. Our formula becomes:

=RANK.EQ($B3,$B$3:$B$12)+COUNTIFS($B$3:$B3,$B3)-1

Notice that in the criteria range B3:B3, I have fixed the row on the start of the range and left it flexible on the end, so when our formula is copied down, we are always counting the instances from the top of the column to the current row. By the end of the range, our formula is:

=RANK.EQ($B12,$B$3:$B$12)+COUNTIFS($B$3:$B12,$B12)-1

On the first 24, our RANK.EQ function returns 8 as before, and our COUNTIFS counts that there is 1 24 so far, so:

8+1-1 = 8

On the second 24, our RANK.EQ function again returns 8, but our COUNTIFS now counts that there are two 24s so far, so:

8+2-1 = 9

thereby giving us the result we required:

The league table was created by entering the numbers 1 to 10 in the first column and using INDEX and MATCH to return the number at that rank from the first table.

The INDEX/MATCH function on the first row (row 3) was:

=INDEX($B$3:$B$12,MATCH($E3,$C$3:$C$12,0))

If you did not understand the COUNTIFS or INDEX/MATCH functions, please visit these earlier posts:

Excel Tip: Counting items that meet certain criteria

Excel Tip: INDEX and MATCH - an alternative to VLOOKUP (and HLOOKUP)

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: Adding up columns based on multiple criteria (the SUMIFS function)

Before getting into today's post I want to point you to an excellent free Webinar being offered (for a limited time) by Mynda Treacy, entitled "Creating Excel Dashboards". Mynda is a real expert on Excel Dashboards and her training materials are always excellent. You can register for the webinar here.

I realised the other day that I had never covered one of my most used functions on this blog - SUMIFS. I have covered its predecessor, SUMIF, as SUMIFS has only been available since Excel 2007.

Although SUMIF is still available in later versions of Excel for compatibility purposes, it is essentially redundant, as SUMIFS does the same thing, plus a lot more.

Let us look at an example of some sales data (see left).

Say we want to know how much Mary's sales were, or how much Sarah sold in the East Region, or even how much Ben sold in the North region in the month of January.

SUMIFS can do all of these.

The syntax for SUMIFS is as follows:

=SUMIFS(SumRange,CriteriaRange1,Criteria1,[CriteriaRange2],[Criteria2].....)

You can have as many pairs of CriteriaRange and Criteria as you need. The function works as follows:

SUM SumRange where CriteriaRange1 = Criteria1 and CriteriaRange2 = Criteria2 etc. for however many criteria you have.

For all of the examples above the SumRange will be D2:D21, as this is the range we want to sum, subject to our criteria. We will look at how we construct the rest of the formula for each of our examples above.

How much did Mary sell?
Here we only have one criteria:

CriteriaRange1 = C2:C21
Criteria1 = "Mary"

=SUMIFS(D2:D21,C2:C21,"Mary")

returns £16,853.

How much did Sarah sell in the East Region?
This time we have two criteria:

CriteriaRange1 = C2:C21
Criteria1 = "Sarah"

CriteriaRange2 = B2:B21
Criteria2 = "East"

=SUMIFS(D2:D21,C2:C21,"Sarah",B2:B21,"East")

returns £1,085.

How much did Ben sell in the North Region in the month of January?
This time we actually have four criteria:

CriteriaRange1 = C2:C21
Criteria1 = "Ben"

CriteriaRange2 = B2:B21
Criteria2 = "North"

CriteriaRange3 = A2:A21
Criteria3 = ">="&DATE(2016,1,1)

CriteriaRange4 = A2:A21
Criteria4 = "<="&DATE(2016,1,31)

There are two elements to these last two criteria that need further explanation.

The first is that if our criteria is anything other than equals, we need to include the criteria in inverted commas, for example ">23", or "<=15", to make it a string. If rather than 23, we wished to refer to a cell (say G5) we can use the ampersand (&) to join two strings together, e.g. ">"&G5.

The second is that if we wish to refer to a date directly, we need to refer its sequential number which we can calculate using the DATE function. The three arguments for the DATE function are Year, Month and Day, so to get the date sequence number for 1st January 2016, we can use DATE(2016,1,1). Note that if we entered 1/1/2016 in cell G5, we could just use ">="&G5 for Criteria3, as the cell value when you enter a date, is its date sequence value.

Our function is therefore:

=SUMIFS(D2:D21,C2:C21,"Ben",B2:B21,"North",A2:A21,">="&DATE(2016,1,1),A2:A21,"<="&DATE(2016,1,31))

which returns £4,007.

In most real situations we are likely to have all of the criteria in other cells, as we are usually doing more than one calculation.

With careful planning and smart use of dollar signs, you can structure your formula so that you only need to write it once. For example if we wish to populate the following grid from our data:

If we put the following function in cell J2, we  can copy it to all of the other cells:

=SUMIFS($D$2:$D$21,$C$2:$C$21,$I2,$B$2:$B$21,J$1)

We have used dollars to fix both the rows and columns of the references to the data table, as these should not change, no matter what cell that we are in.

For our criteria however, we want those to change between cells, so we have fixed the column of our name criteria ($I2), as we are always going to look to column I for the name, but want it to change as we change rows. Similarly, we have fixed the row of our region criteria (J$1), as we always want to look at row 1, but want it to change with the columns.

Hopefully this gives you an idea how flexible SUMIFS can be.

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

ICAEW Spreadsheet Capability Framework - your opinions?

As you may be aware, I am a member of the Institute of Chartered Accountants in England and Wales (ICAEW) Excel Community Advisory Committee.

An important piece of work that the committee has been working on is what we are (currently) calling the Spreadsheet Capability Framework.

This document is intended to provide a reference point that spreadsheet users, employers and job applicants can use to discuss and assess competence in spreadsheet use - beyond glib phrases on a CV, such as "proficient with Excel".

Given the authorship of the framework, it is obviously primarily aimed at those in the finance field, so apologies to my non-accountant readers.

The framework has now reached the stage where we are actively seeking opinions outside the committee.

To this end my colleague, David Lyford-Smith has posted a copy of the document to the ICAEW's IT-Counts website, where your comments would be greatly appreciated.

If you would like to read the framework document and/or add your opinions to the debate, please use the following link:


http://www.ion.icaew.com/itcounts/post/Spreadsheet-Capability-Framework---exposure-draft-seeking-your-comments

Whereas comments on my blog are always welcome, and will be passed on, I would recommend commenting on the IT Counts post instead to contribute to the wider debate.

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: Using Index to calculate a cumulative sum (e.g. a Year to Date total)

Let me start with an apology. My current workload has meant that my posts have been a lot less frequent in recent weeks. This may continue for a while, but I will still be aiming to post at least once a month during this busy spell.

In this post I want to show you how to do something I have shown before using OFFSET, but this time using the more efficient INDEX.

Let's look at the example of a 12 month budget spreadsheet with the monthly sales figures in cells B9 to M9 (month 1 in B9, Month 2 in C9, etc.). And let's say that the current month number is entered in cell B6.

Using OFFSET we could return the current month's sales using:

=OFFSET($B9,0,$B$6-1)

and the cumulative sales using:

=SUM(OFFSET($B9,0,0,1,$B$6))

See this earlier post, if you don't understand why.

The problem with OFFSET though, is that it is what is known as a "volatile" function. This means that it always has to be recalculated when any cell in the spreadsheet changes - as it does not specify a range that it is dependent on, therefore does not know whether a change might affect its result.

INDEX however looks at a defined range, making it significantly more efficient, and in its simplest form can be used to replace both of these examples of OFFSET.

For a range the width of a single cell (as in our example), INDEX only needs two arguments to return the current month's sales:

=INDEX($B9:$M9,$B$6)

This returns the value of the cell in position B6 in the range B9:M9.

If $B$6 is 3, this will return the value in cell D9, being the third cell in the range B9:M9.

To do the cumulative calculation, we can use the same INDEX function to return the end of  a SUM range, while fixing the start:

=SUM($B9:INDEX($B9:$M9,$B$6))

Again, if B6 contains 3, then this returns the sum of the range B9:D9.

In both cases, these will only recalculate if a cell in the range B9:M9 is edited. In a complex spreadsheet with many calculations, this can make a huge difference to calculation times.





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: Controlling what is printed from a sheet

One of the challenges that many Excel users face, is ensuring that the printed version of a spreadsheet looks as good as it did on the screen.

There are a few simple things that you need to know to help to organise what actually gets printed and how it is spread across pages.

I will cover the first of these today, and revisit some of the others in future posts.

Today we will look at how to determine how much of the sheet is actually printed.

Left to its own devices, Excel will print all of the used area on the sheet, Specifically, it will print the whole area from cell A1 to the rightmost column and the last row that are not empty.

This may not be what is required, and it is easy to change. Simply highlight the area that you do want to print, then go to the Page Layout ribbon, click the Print Area icon, and select Set Print Area.

You can even select multiple ranges (holding down the Control key, while you select the subsequent ranges) which Excel will print on different pages.

For example, you may have a Profit & Loss and Balance Sheet alongside each other on one worksheet. Just highlight the area of the Profit and Loss (say A1:H100) and then hold down the Control key while selecting the Balance Sheet range (say J1:P80) and Set Print Area as described above.

Once a Print Area has been set in this way, you can see (and edit it) using the Name Manager on the Formulas ribbon. You will see all of the Print Areas in the workbook listed there and (scope to their particular worksheet). You can then edit these ranges like any other.

You can even make them a dynamic range using functions like OFFSET and INDEX, so that the area that gets printed is determined by values in the workbook.

A particularly useful technique is to combine this ability to set a dynamic Print Area with a formula to determine the last row of data. I explained a formula to do this in this earlier post.

Let's say we put this formula in a cell that we name LastRow.

If we want the print area to be columns A to J but the height of the print area to flex to the amount of rows occupied, we can enter the following as the Print Area (in the Names Manager):

=OFFSET($A$1,0,0,LastRow,10)

This will print a range starting at A1, with a height of LastRow, and a width of 10 (A to J is 10 columns).







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: An easier way to change cell colours

Before I begin, I would like to wish a Happy New Year to all of my readers and subscribers - as this is my first post of 2016.

I am starting the year in humble mode, as I have had something very simple pointed out to me that I can't believe I had never spotted, despite it being right under my nose!

In my last post of 2015, I showed you some code to swap one cell colour for another throughout a worksheet. I then received a comment from a reader that I only know as Jim, pointing out that Excel already has a very simple way of doing this!

It can in fact be done using Find and Replace.

This amazed me because I use Find and Replace all of the time and didn't know you could do this!

It is simple as this:

1. Highlight the range which you want to apply the changes to
2. Click Find and Select (the binoculars icon) on the Home Ribbon and choose Replace from the dropdown menu
3. Don't enter anything in the Find or Replace boxes, but click the Format button next to the Find box and on the Fill Tab, select the colour that you wish to change
4. Click the Format box next to the Replace box and on the Fill Tab, select the new colour that you wish to swap for the colour you selected above
5. Click Replace All

You can even start with the colour of an existing cell, using the dropdown on the format button, however be sure to remove any other formats other than Fill colour.

The thing that really shocked me is that these two big Format buttons have been right there in front of me every time that I've used Find and Replace since Excel 2003! And that's a lot of times!

In 2016, I am going to try to make sure that I constantly keep my eyes open for new ways to do things in Excel, instead of just relying on what I think I already know! And thanks again, Jim.

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: Merry Christmas with a quick macro to swap one colour for another

First of all I would like to wish all of my readers and subscribers, a very Merry Christmas and a Happy 2016.

I know we all work hard, and I hope, like me, you all get a chance to spend some time with those you love in the coming days, whether Christmas is a festival you celebrate or not.

One hassle I sometimes have when building spreadsheets for others is changing colours.

The spreadsheet may work perfectly, but the end user might not like the colours I have chosen. This isn't too much of a problem if we are talking about large blocks of colour, but it can be a time-taking process, if I have used yellow to denote input cells throughout the whole worksheet, and the client wants orange, for example!

When this happened recently on a particularly complex spreadsheet, I decided to write a little macro to help, which turned out to be pretty straightforward and does the job in seconds.

Here is the code of the macro.

Sub ColourSwap() 

Dim Source As Variant
Dim R As Integer
Dim G As Integer
Dim B As Integer
Dim NewColour As Variant
Source = ActiveCell.Interior.Color
If MsgBox("Switch to no colour?", vbYesNo) = vbYes Then
NewColour = 0
For Each cell In ActiveSheet.Cells
If cell.Interior.Color = Source Then cell.Interior.ColorIndex = NewColour
Next
Else
R = InputBox("R?")
G = InputBox("G?")
B = InputBox("B?")
NewColour = RGB(R, G, B)
For Each cell In ActiveSheet.UsedRange.Cells
If cell.Interior.Color = Source Then cell.Interior.Color = NewColour
Next
End If 

End Sub

The easiest way to include it in your spreadsheet is to hit Alt-F8 and type the name you want to call the macro in the box at the top of the dialog. I have called it ColourSwap. Click create and you will see the green sections above already showing in the window (with your chosen name replacing ColourSwap). Just paste the yellow section in between.

You can run the macro by hitting Alt-F8 again, selecting it and choosing Run. If you prefer, you can click options instead of Run, and assign a shortcut key to run it in future.

The macro works as follows, and is applied to a single worksheet at a time.

1. Click on one of the cells that contain the colour that you wish to swap;
2. Run the Macro
3. You will be asked if you wish to "Switch to no colour?", if you select "Yes", then all of the cells on that worksheet that are the same colour as the cell you selected, will have any Fill Colour removed. This is the same as choosing No Fill if you were colouring the cell manually.
4. If you select "No", you will be faced with 3 prompts, requesting the R, G and B values for the colour you wish to swap to. Once you enter these, all of the cells on the worksheet that are the same colour as the cell you selected, will be filled with this new colour.

NB: The macro only checks the Used Range of the worksheet, so will not change any coloured cells below, or to the right of the last populated cell on the worksheet. This is to save time, as it can take quite a while to loop through every cell, when this is not necessary. If (for some reason) you have coloured cells outside of the Used Range, then these should be very easy to change manually, as they will almost certainly be in a large block.

Note that if you wish to save the Macro with the spreadsheet, you will need to save the Workbook as Macro-enabled Workbook, however this will usually be unnecessary, as once the colours have been changed, the macro is no longer needed, so that you can let it save without the macro.

I hope you find this useful, and could maybe use it to make some tired old spreadsheets look a bit more festive!

Merry Christmas!

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