Search Not Just Numbers

Tuesday, 26 February 2019

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:


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

Friday, 6 October 2017

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

Wednesday, 27 September 2017

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