# Category Archives: Simple

## Introduction

Over the past few months I’ve been doing more Excel training than usual and regularly, at about this time of year, I train the new student intake for several firms of accountants. Since they keep inviting me back year after year I assume that the firms find the training useful. However, I am sure that other organisations assume that there is no need to train new recruits as they will have gained more than adequate skills in ‘basic’ software packages through school and possibly university.

I am sure that some students do indeed acquire pretty good levels of general software skills during their time in the education system, but in my experience there are two significant issues:

• The level of software expertise varies dramatically from student to student;
• Competence in the mechanics of using a spreadsheet doesn’t necessarily result in the ability to use spreadsheets appropriately and reliably in a business environment.

This led me to considering the ‘minimum’ level of spreadsheet skills and knowledge for someone using spreadsheets in business.

Here’s my initial set of ideas – comments, additions and disagreements welcome.

## Basic formulae entry

Creating references to other cells in the same sheet, another sheet in the same workbook and a cell in a different workbook, including an appreciation of the dangers of referring from one workbook to another.

Using the basic mathematical operators – plus, minus, multiply and divide – including an understanding of the order of mathematical operations and the importance of brackets.

Understanding absolute and relative cell references, including partially absolute references.

Understanding the use of range names.

## Use of Excel functions

Understanding some basic Excel functions:

• SUM()
• IF()
• LEFT(), RIGHT() and MID()

Understanding how to use the ‘Insert function’ button to search for functions, enter function arguments correctly and read the help on specific functions.

Understanding how easy it is for functions to return incorrect answers if arguments are not entered correctly.

## Design

The dangers of spreadsheets – how the lack of structure makes spreadsheets very error-prone.

Basic design concepts – the importance of separating data and formulae, organising spreadsheet contents, cell locking and worksheet and workbook protection, input data validation.

The importance of documentation – comments for individual cells and separate sheets to document important information about the spreadsheet.

The importance of building in checks and controls and exception reports.

The importance of testing.

## Efficient use

How to create and use an Excel template.

Copy and paste and the use of the fill handle for copying and for extending lists of months and days.

Basic formatting including applying number and date formats.

Knowledge of the existence of:

• Conditional formatting
• PivotTables
• Lists (Excel 2003) and Tables (2007,2010)

## Conclusion

So is this list fair? What does it include that isn’t necessary for someone using a spreadsheet in business? What vital things have I missed out?

## Will online training replace ‘live’ training

Online training is increasingly being seen as a viable alternative to classroom or lecture-style courses. It’s an area we’ve been involved in for quite a long time now, from our work on the Courses-on- Disk Office CDs back in the early days of the millennium through to our short lunchtime learning animations and training videos for IT Counts.

The Internet seems to be ideally suited to allowing people to give away for free what they used to be able to charge for (it will be interesting to see how the Times and Sunday Times attempt to move back from free to paid-for web content goes). Giving content away is all too easy, getting paid for it, a lot more difficult.

Accordingly the value of online training is causing us some concern. Firstly, can online training be as effective as ‘real’ training and lecturing? In all probability it depends both on the subject of the training and the individual trainee – some people will prefer the flexibility of an online course accessed when they want from where they want. For others, the discipline of attending an ‘event’ combined with ‘live’ interaction, with other delegates as much as with the lecturer, might achieve better results. On the other aspect of value, people may not be prepared to pay a similar amount for online training as for attending an event but increased ‘attendance’ might more than make up for cheaper prices.

Will there always be a place for live courses and training or will online alternatives replace them entirely in time? Any views or personal experiences would be very gratefully received

## Word and numbering 1 – simple numbered lists

### Using outline numbering and styles

Coping with paragraph numbering is a common cause of problems and irritation in Word. Whilst Word’s automatic numbering will cope adequately with simple lists, once things get more involved and multi-level numbering is required, things can quickly get out of hand. Our usual advice in these situations is to use Word’s ‘Outline numbering’ facility to cope with the numbers and formatting. A recent query from one of our clients who was setting up a ‘Letter of Engagement’ template, incorporating several levels of paragraph numbering, led us to investigate the whole area in a bit more depth – and to discover a useful – and vital – feature we were previously unaware of.

So in this short series we will look at the whole subject of numbering in Word.

### Simple numbered lists

First of all, let’s look at simple numbered lists and some possible complications.

## 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

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 pane
• 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?

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:

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:

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.

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.

## Using Excel text functions to work with analysis codes – part 1

This is the first part of a two part article that was prompted by a comparatively simple query about concatenating text. As well as dealing with that query, we’ll look at some of the simpler Excel text functions for working with text. Part 2 will follow shortly and, in it, I’ll look at some slightly more advanced functions for dealing with less predictable text entries.

First of all let’s deal with the actual query, which asked how to combine text in two separate cells into a single cell. There are two principal ways to achieve this. Perhaps the simplest is to use the ‘&’ within an Excel formula:

In our example we have typed three items of text in columns A, B and C. In cell D1 we have entered the following formula to combine all three into a single cell:

=A1 & ” ” & B1 & ” ” & C1

Alternatively, there is an Excel function that concatenates text in this way. Unsurprisingly it is the ‘Concatenate’ function.

In the following screen shot we have used ‘Insert, Function’ to enter the required details. Note that in order to include spaces between the items of text, we have included “ “ between each pair of cells. Using the Insert Function screen, you just need to enter a space in the appropriate text box – Excel will add the speech marks for you:

So far so good, now let’s see how we cope with combining numbers and text. If we just want the number without worrying about the number format, then we can use exactly the same formula as for two items of text. Here we have included some ‘literal’ text in the formula together with a number in a cell:

As you can see the format is not ideal:

In the following example we have used the ‘Text’ function to format the number in the cell:

Note that you can also use named ranges. So if we name cell B3 as ‘profit’ we could write the formula as:

=”Profit is ” & TEXT(profit,”£#,##0″)

Note that we have included a space after the ‘is’ and before the “ so that the number does not follow on immediately from the text.

To see the text functions available in Word, select Insert, Function and then choose the ‘Text’ category (note that the examples shown are from Excel XP, other versions’ screens are slightly different):

As you can see there are lots of text functions, we will look at a few in detail, but if you want to explore all of them, just scroll through the list using the down arrow key. As you select each function in turn you will see a brief description of what it does towards the bottom of the screen. For more details, click on the ‘Help on this function’ link:

This group of functions can be used to return specific sets of characters from a text string. As you would imagine, Left is used to return a certain number of characters from the beginning of a text string, Right is used to return characters from the end and Mid to return characters from anywhere within the text string.

As an example, we will look at some nominal ledger codes. We will assume that the first two characters represent the company, the next three the branch, and the last four the type of expense or income:

First of all we will use the Left function to list the first two characters in the company column. We can either use Insert, Function or just type the function in directly if we know the required syntax:

=LEFT(A7,2)

Now let’s use Right in a similar way to sort out the four characters from the end of the code:

=RIGHT(A7,4)

As you can see, the syntax of Left and Right are very similar, just referring to the cell holding the code and the number of characters. The final function that we will look at in this section is ‘Mid’ and the syntax for this one is slightly more involved because we need not only to specify the number of characters, but also from which character to start. For this reason you may find it easier to use Insert, Function:

This should create the following formula:

If we now copy the three formulae down to the end of our list, we can see how our text string has been split into the three different sections:

Rounding can often be a problem in a spreadsheet. We’ll start with some relatively simple background stuff then move on to some lesser-known aspects of rounding and displaying rounded values.

What you see isn’t what you calculate – mostly!

The first thing to grasp is that the way Excel displays the contents of a cell generally has no effect on the actual contents of that cell as used in calculations. So a value of 100.05678 will remain as 100.05678 even if the format of the cell is set to display no decimals. There is one important exception to this. There is an option in the ‘Calculations’ tab of the Tools, Options screen that forces Excel to change the contents of the cell to match the ‘precision’ of the format. So if the cell is formatted to display no decimal places, the contents will be permanently rounded to no decimal places – our 100.05678 would become 100.00000:

Before turning on ‘Precision as displayed’

and after:

This is an option to be used with great care. It applies to the entire workbook and as soon as it is turned on potentially decreases the accuracy of any figure displayed to fewer decimal places than it actually contains.

Round function

A less drastic method of avoiding rounding errors is to use Excel’s ROUND() function. This allows you to round a number to a specific number of decimal places for use in calculations, without changing the original number. Round takes two arguments – the number to be rounded and the number of decimal places to round to. In this case we have used =ROUND(A2,0) to round to round pounds:

The ‘sum’ formula in cell C5 sums the rounded numbers in C2 to C4, so returns 99 rather than 100. If you need the total to be a particular figure, for example if the 100 was a profit share, then you could set the final rounded figure to be the total minus the sum of the other rounded figures. In this case we have entered a figure of 100 in cell E5, then rounded the calculation of the two shares in E2 andE3, with E4 being =E5-SUM(E2:E3)

More fun with rounding

As well as 0 for no decimal places or a positive number for that number of decimal places, you can also enter a negative number as the decimal place argument of the Round function. For example you could enter -3 to round to thousands:

To change the format to display numbers rounded to thousands you would use the following custom format:

#,###,

for 1,235k

#,###,k

for millions

#,###,,

for 1m

#,###,,”m”

The speech marks are necessary for the m, but not the k, because m is used in formatting to signify a month format.

## Peril-sensitive formatting in Excel

Well, given the name of the site I had to do this one sooner rather than later!

Some of you may remember the peril sensitive sunglasses worn by Zaphod Beeblebrox in the Hitchhiker’s guide to the galaxy. Excel has its own version of this useful accessory which it refers to, rather strangely, as ‘Conditional formatting’.

The point of peril sensitive sunglasses was to protect the wearer from danger by turning completely opaque when any threat was imminent. This concept is highly relevant to spreadsheets which often communicate financial or other information that may be upsetting to the user. In this example we are listing the weekly turnover figures for 7 shops, and also including a ‘peril’ level. Results below the peril level will trigger alarm and dismay, so we will use peril sensitive formatting to hide them. Select the cells containing the values – B4 to B11 and then choose Format, Conditional Formatting. Choose ‘Cell Value Is’, ‘less than’ and then refer to our peril level cell, B1. Then click on the Format button and set the ‘Patterns’ to a black background, and the Font to a black font colour:

Once the ‘Pattern’ and the font have been changed to black the screen should look as follows:

If we click the ‘OK’ button, and then click outside our selected area, we can see the effect of the peril sensitive formatting:

The formatting is dynamic, so if we enter new values, or change the peril level, the formatting will change accordingly:

You can set up to three different formats on a cell or range of cells (press the ‘add’ button on the conditional formatting screen to add a second or third format). So you could, for example, use a ‘traffic lights’ system to set the background colour of all cells with values below a ‘minimum’ level to red, those with values above a ‘maximum’ value to green and the ones in between to amber. In Excel 2007 conditional formatting has been greatly enhanced to enable the use of a much wider range of formats and icons to highlight certain values.