Excel, the LBW law, range names, form controls and logical formulae

Nearly two years ago I was running an advanced Excel course and needed to create an example of the use of Excel’s logical functions. Given that most of the people we train work in accountancy, our examples are usually based around financial data but, since it was the middle of the 2005 Ashes tour, the cricket LBW (leg before wicket) law sprang to mind. I’ve extended the example to look at the use of range names in making formulae easier to understand, and also to incorporate the use of a simple interactive form control.

Excel and the cricket lbw law

Continue reading

PowerPoint – giving a presentation – what can possibly go wrong?

Often when I’m watching other people present using PowerPoint I see them making the same errors that I’ve made (and no doubt continue to make) on many occasions. This post isn’t about the design of the presentation, just some hints and tips to help cope with what can sometimes go wrong. The golden rule is to take two of everything and have some sort of plan for if you can’t get the technology to work – even if it’s spontaneous laryngitis.

Start from current slide

PowerPoint alt-tab

Often people exit from their PowerPoint presentation in order to demonstrate another application by pressing the ‘Escape’ button. Having done what they want outside PowerPoint, they then need to resume the presentation. More often than not, they will click the menu option Slide Show, View Show or use the ‘F5’ keyboard shortcut (PowerPoint 2000 and later) to start their presentation from the first slide, then advance through each slide until they get back to the right slide. There are some more elegant ways to do this:

  • Don’t exit from the presentation with the ‘Escape’ key but use ‘Alt+tab’ to cycle through to the other application, then cycle back again when you have finished
  • Use the ‘shift-F5’ (PowerPoint 2003 and later) shortcut key which starts from the current slide rather than the first slide
  • Use the ‘Slide show from current slide’ button at the bottom of the navigation panePowerPoint - current slide button
  • If you have started from the first slide, right-click on the slide and choose ‘Go to slide’ from the shortcut menu and choose the required slide. If you have decided to use the right mouse button to go back rather than display the shortcut menu (see below), then you can access the menu from a semi-visible ‘pop up toolbar’ at the bottom left of the slide screen (this icon is completely invisible until you move the cursor into the bottom left hand corner of the slide) or use Control-s (PowerPoint 2003 and later) to display the list of slides.

Right-click to go back

Go to Tools, Options and the View tab. In the ‘Slide Show’ section you can turn off ‘Show menu on right mouse click’. The right mouse button will then operate as the ‘PageUp’ button to go back an action.

Keyboard shortcuts

As well as F5 and Shift+F5 you many find the following other PowerPoint shortcuts useful:

B or W – show a Black or White screen – useful if you suddenly notice the slide displayed is one you meant to delete or hide!

Number + Return to go to that number slide (if you know it!)

Power management settings

If you’re prone to talking a lot, you might experience that worrying feeling that something strange has just happened on the screen behind you. Often this is because your screensaver has started up. So, before you start your presentation make sure you turn your screensaver off, and also check your ‘Power Options’ in Control Panel – you should find a ‘Presentation’ option which keeps everything turned on.

Remote control

Depending where on the Bill Wyman to Mick Jagger continuum your stage presence lies, you might find it useful to invest in a device to control the presentation remotely –without the indignity of walking around holding a wireless mouse. I recently bought a USB ‘Sweex’ Wireless Media Presenter for about £15 that does the job simply and effectively and includes a laser pointer – lots of more sophisticated options are available.

Nothing on the screen?

Most of these are an insult to your intelligence – but it’s always worth checking – I’ve been guilty of a few in my time…. Continue reading

Extend list formats and formulas

Have you ever been typing in a list of items in Excel and suddenly found Excel has started automatically filling in formulas in one of the columns or copying the formatting down to each new item in the list? Have you wondered why this happens sometimes but apparently not all the time?

Extending formulas in an Excel list

The first thing you need to do is to check one of the Excel options. Go to the Tools, Options screen and select the Edit tab, see whether the ‘Extend list formats and formulas’ option is selected. Here are the screens first for Excel 2007, and below that previous versions:

Extend list formats and formulas option - 2007

Extend list formats and formulas option

For the automatic extend to work, this option must be turned on, and a rather long list of other conditions has to be satisfied. Perhaps the two most significant are:

At least three of the previous five rows must feature the formatting or contain the formula that is to be extended; and

A formula to be extended must not contain a range name (anyone know why?)

There is a far more complete description of how the option works, together with a list of situations in which it won’t work on the Microsoft site:

http://support.microsoft.com/kb/231002

Navigation short cut – double click your edges

I have a feeling I should have known this for years and that this article will highlight an embarrassing gap in my Excel expertise, but just in case I’m not the only Excel user who hasn’t spotted this before, here goes:

Navigate by double-clicking your edges

Double-click on the edge of a selected cell to quickly move to the corresponding edge of your data area. Do it while holding down the shift key to select all the intervening cells.

Navigate by double-clicking your edges - shift to select

If you prefer using the keyboard, the equivalent shortcuts are to hold down control and press one of the arrow keys to navigate, and to hold down both the control and shift keys and press an arrow key to select. Also, control+shift+spacebar selects the entire current region.

Excel Indirect() function – save hours

Excel’s Indirect function allows the creation of a formula by referring to the contents of a cell, rather than the cell reference itself. Of all the functions covered in our Excel courses, it is often Indirect() that attendees haven’t come across but find an immediate use for, often saving a great deal of time and effort in the process.

Indirect used to include references to the sheet named in cell A1

If you have several sheets, each with information for a single department for example, you may want to set up a summary sheet. Rather than creating separate formulae to refer to each sheet, Indirect() can allow you to create a single set of formulae all of which use a reference to a sheet name held in a cell – hopefully an example will make this clearer.

To refer to cell A2 on a sheet named ‘Cuddly Toys’ we would use a formula like this:

=’Cuddly toys’!A2

However, sometimes it would be useful to be able to change a whole series of references to, for example, a different sheet.

We could type the sheet name into a cell on our main sheet, say A1. We could then write a formula to refer to cell A2 on the sheet typed into cell A1.

If we simply type:

=A1!A2

Excel, not unreasonably, looks for a sheet named A1 and fails to find it.

However, we can use the Indirect function instead. Here is the screen from the Paste Function dialog for Indirect:

Indirect 1

& $A$1 & ‘!A2

Our Ref_text entry is a little confusing, so we have highlighted the pairs of speech marks in different colours. We have two items of text, and sandwiched in between them, an absolute reference to the contents of cell A1 – as you can see this correctly returns the contents of that cell – Cuddly toys. The ampersands are used to join the 3 elements of our Ref_Text together. The first text section simply holds a single apostrophe – this is necessary because, if our sheet name contains a space, it must be surrounded by apostrophes to be correctly identified. The second section contains an absolute reference to cell A1 – the cell where we type the name of our sheet. The third text section contains the closing apostrophe for the sheet name, together with the exclamation mark that separates sheet name from cell reference, and the cell reference itself – A2.

This works well to return the contents of cell A2 on our cuddly toys sheet, and if we were to type in ‘Boardgames’ for example, it would automatically return the contents of cell A2 on a sheet named ‘Boardgames’.

However we do have a problem left to solve. We need to refer to many cells on the price list sheets, but if we copy our Indirect cell, the reference to A2 doesn’t change, because it is just text. We can solve this by using a row/column style reference instead of A2:

Indirect 2

& $A$1 & ‘!RC

Note that we have to set the ‘A1’ argument of the function to ‘False’ to use this reference. RC will return the current row and column – so a formula in cell A2 will refer to A2 on cuddly toys, A3 to A3 and so on. If we need to refer to a different cell we would add numbers in square brackets after R and C. So R[1]C[1] would look at the cell one row down and one column right for example.

Using Excel text functions – part 2

Now let’s consider a slightly more difficult situation. In the following example we have a description and an amount in the same cell, but the two are always separated by a hyphen:

Excel text functions - search, len and value

Because neither the length of the text or the figure are necessarily consistent, we can’t use Left, Right or Mid. However, we can instead use the hyphen to work out where the description ends and the number begins. To do this we must first identify how many characters from the left there are before the hyphen.

To do this we use the ‘Search’ function. Here is the function screen for Search:

Search function

Note again that we can just type the hyphen into the ‘Find_text’ box and Excel will automatically add the speech marks. Note also that there is the option to specify the character position at which you want to start the search. This is useful if you need to locate more than one similar character – once you have found the first, you can start the next search from one character position higher. Our example is a simple one that doesn’t use the ‘Start_num’ argument and, as you can see, it returns the position of the hyphen as character 6.

=SEARCH(“-“,A13)

We can now ‘nest’ the Search function within the ‘Left’ function to retrieve the description:

=LEFT(A13,SEARCH(“-“,A13)-1)

In order to exclude the hyphen itself we have subtracted 1 from the result of search. If we copy this formula down our list we can see that it achieves the desired result:

Excel text functions - search

Now to deal with the amount. Whilst we can use Search to find the starting position, we don’t yet know how long the amount is. We can work this out using the ‘Len’ function. ‘Len’ is a very simple function with just one argument – the text string, or cell containing the text string, that we wish to find the length of:

=LEN(A13)

This tells us how long the text is in total, and we have already used Search once to find the position of the hyphen. By combining Len and Search we can calculate how many characters follow the hyphen:

=LEN(A13)-(SEARCH(“-“,A13))

In the case of “Sales-10000” Len will return 11, the hyphen is at position 6, so 11-6 = 5, the number of characters in the amount.

We can use this with the ‘Right’ function to pick out the amount:

=RIGHT(A13,LEN(A13)-(SEARCH(“-“,A13)))

Again we can copy this formula down the list:

Excel text functions - search and len

As you can see above, whilst we have indeed separated out the amount characters, Excel is still treating our text as text and if we used Sum to total column C we would get zero:

We need to convert the text ‘amounts’ into proper numbers. To do this we use the function Value. We will use the value function to convert the three items in our list to numbers. Here is the formula for cell D13:

=VALUE(C13)

We can now copy this down our list and use Sum again to total our new column:

Excel text functions - value

As you can see, the text values are now treated as numbers and Sum works correctly.

These two functions can be used to remove unwanted characters from text. Sometimes, if you import text from other sources, you may end up with non-printing characters, such as carriage returns – Clean will remove these. Trim can be used to get rid of extraneous spaces:

In the following example we have part of an address that includes multiple spaces between ‘High’ and ‘Street’ and a carriage return character to separate the lines of the address.

In column B we have used Trim to get rid of the extra spaces:

=TRIM(A19)

and then in column C we have used Clean on the result to remove the carriage return:

=CLEAN(B19)

Excel text functions - trim and clean

Note that the Trim function leaves a single space between High and Street, but that the Clean function removes the carriage return entirely.

Word 2007 – lost in the maze and found

Has anyone found the options to turn on either AutoText/Building blocks AutoComplete, or to display text boundaries – surely they’re not really lost and gone forever in the latest version are they? In fact, inspired by writing this post, I’ve just found one of them! The text boundaries option is in Word Options, Advanced, ‘Show document content’ rather than in Word Options, Display where I was looking.

In a way, I’m finding getting to grips with Office 2007 at bit like one of those old-fashioned adventure games where you blunder around in a dark maze and then occasionally triumphantly find something you’ve been looking for for ages. Help is a bit like the cryptic clues you used to get. Great fun, but not currently helping my productivity a great deal…