Excel – requirements for competence


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.


Appropriate uses of spreadsheets and what not to use spreadsheets for.

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.

Additional Excel features

Knowledge of the existence of:

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


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

Sky Broadband respond

2 October 2007

I’ve just had a phone call from Sky Broadband customer services. They suggested that the original problem was not mainly their fault, in that BT have a direct number they should have used to resolve the problem without having to involve the customer. They also said this lapse in communications between the two organisations was a very rare occurrence. They did however recognise some room for improvement in their procedures and did apologise for any shortcomings. Strangely – without prompting – the man from Sky mentioned the need for common sense to play a part in the technical support process.

Hopefully an instructive – though deeply irritating – experience for all those concerned.

BT ride to the rescue

This is the latest update to my Sky Broadband v. BT saga. At about 7pm on Saturday 29th September my contact at BT phoned to say they had managed to contact Sky directly and hope to be able to resolve the problem soon. At 11am on Sunday he phoned again to report that all should now be working. A few minutes later I received an email from by father to prove that this was indeed the case. Whilst BT had been sorting the problem out I had had two further emails from Sky Broadband. The first said my original email would not be received and that I would have to resubmit the information via a web form (there does seem to be a bit of a contradiction there…) followed 45 minutes later (and without me doing anything in the meantime) by a second email which thanked me for my email (presumably the one that hadn’t/wasn’t going to be received) and promising to reply soon.

I’ve learnt a few lessons, or more accurately relearnt a few quite old lessons:

  • Free or cheap services often cost the most
  • The likelihood of a quick resolution to a technology problem decreases exponentially in line with the number of organisations involved
  • Any problem resolution system that has a rigid set of procedures with no ability to use judgement or common sense can be disastrous for both the customer and the poor unfortunates operating the system
  • Almost whatever you’ve done to a customer, if you make an honest and concerted effort to sort the situation out, you’ve a chance of recovering the situation.

ADS hell – how it feels to be a football in a match between BT and SKY

This is a bit of a departure from the normal Beancounter’s guide articles but, as Sherlock Holmes might have said, it does promise to address some instructive issues…

On about 11 September my father’s phone line ceased functioning. BT eventually gave me a repair forecast of 15 September. This date passed with no resolution.

After repeated phone calls and struggling with the automated fault report services and the internet-based fault report service which twice told me there appeared to be a problem and then automatically closed the query without doing anything, I eventually spoke to someone who said they would find out what was going on and resolve the problem within the next 24 hours. They were courteous and helpful and the problem was apparently resolved well within the time period specified. During the conversation they mentioned an issue with the broadband on the line, but gave no further information.

Whilst the phone line itself then worked, the Internet light on the SKY broadband router showed absolutely no sign of life. I telephoned the SKY support line and related the situation regarding the problem with the phone line. The support person claimed to have checked the line and reported ‘the good news is that there is no problem at the exchange’.

We then spent ages disconnecting, reconnecting, changing filters etc. etc. to no avail. This morning I took a laptop to my father’s house so that I could have access to router, phone and computer as suggested by SKY support.

We went through a similar process with SKY support again, including removing the front of the BT master socket and connecting the router directly to the test socket. Still absolutely no response from the Internet light on the router. Convinced by now that it was an exchange problem, I asked SKY support to confirm that the line was OK at the exchange – they said they had and it was.

Given connecting directly to the test socket of the master socket hadn’t worked, I was even more convinced that the problem had to be at the exchange. I phoned BT and, after ages fighting my way through a voice mail system determined to prevent me talking to a human being, eventually spoke to someone who said that there had been a problem with the SKY ADSL equipment at the exchange, SKY had been given the standard 48 hours notice to sort this out, in fact additional time had been given, but BT had then returned the line to a normal PSTN line to restore the phone service. They said I would have to phone SKY to sort this out.

I phoned SKY again and after more voice mail and a hold system that seemed designed to convince you that you had been cut off (near silence, very quiet music well in the background) I was referred from one support technician to another and once more went through the problem. They said the line in question was not a BT line, BT had not updated their database and I would have to contact BT again. By this stage I had become somewhat fed up and suggested that I did not intend to phone BT or SKY anymore and suggested that they just get the issue sorted out.

I decided to give email a try and on the Internet found a customer service email for Sky Broadband, and the email address of the BT CEO – I sent them an email recounting the above story (with copies to OTELO and BBC Watchdog – well you never know…)

28 September 2007

Seconds out, round 2.

Following an email to the CEO of BT and a customer service email address at Sky Broadband, within minutes I had had an automated response from Sky Broadband as follows – well I would include it, but the lengthy disclaimer at the bottom prohibits me from disclosing its contents to anyone. Anyway it suggested that they would reply soon. In contrast, a team manager from BT Openreach’s High Level Complaints department phoned me within a couple of hours promising to send an engineer to try to sort the problem out the next day. He gave me his mobile phone number, and sent an email confirming the details of the phone message and apologising that the problem had arisen in the first place.

Definitely BT 3, SKY 0.

29 September 2007 – 12:15pm

I received a phone call on my mobile phone from a BT manager who had organised an engineer’s visit to the exchange who confirmed that the issue was with the SKY equipment at the exchange and unfortunately, he was not allowed to touch that equipment without first talking to SKY (or something like that). Apparently, the only thing to do was to phone SKY (again) and ask them to contact BT and do whatever had to be done to sort the problem out.

With a feeling of immense trepidation and resignation I phoned SKY. I didn’t think it was going to be easy, but I really hadn’t thought it would be as bad as it was. Now firstly, for all the good it will do, I’d like to apologise to those people at the SKY call centre that I spoke to if I wasn’t as calm and courteous as perhaps I should have been. However, organisations that inflict a support system on their customers, and their staff, that seems to have no mechanism for using judgement or common sense bear a heavy responsibility for the effect on both customers and staff. I first spoke to someone at technical support, explained the situation and asked to speak to someone who could deal with the issue, they put me through to someone who seemed unaware that SKY actually had any equipment in BT exchanges, they said all they could do was to put me through to …..(ominous organ music)

Technical support.

Now, as soon as that happens you know that you might as well put the phone down, crawl into a corner, roll yourself into a small ball and whimper a bit. I wish I had, but instead I allowed myself to be transferred back to technical support. I feared that I would spend my life in some terrible loop – a bit like the one in a Doctor Who episode where Romana (or was it the Doctor) had to wiggle K9’s tail at exactly the right time to break free. This didn’t happen. Instead I got nowhere at all. In spite of explaining clearly, very clearly, even more clearly and then e x t r e m e l y c l e a r l y, that I knew what the problem was, I just needed to talk to the right person to explain it to, the technical support person said they had to check the line before they could pass the query to ‘level 3 support’ (I wonder how many levels there are?). That would have been OK, even though as I explained, explained and explained again, SKY had apparently checked the line twice in the last week without finding a problem – strange when BT claim they converted it back from an ADSL to a PSTN line two weeks ago. However, they couldn’t check the line unless I had access to the problem phone line – which I was 40 miles away from. No argument would persuade the support person to put me through to anyone else. Apparently, all I could do was to drive to my father’s house and phone SKY from there – in spite of having already done exactly that twice before in the last week. Before I ended up crushing my mobile phone like Popeye and a spinach can, I ended the call and took a few deep breaths.

A few minutes later, I phoned my BT contact, and recounted the story of my lack of progress with SKY. He said he was sorry but he could see no other solution than to do as SKY had demanded, but he did give me a contact name, phone number and a job reference.

BT 6 – SKY -435

The story continues….

Word and numbering 2 – outline numbering

In the first part of this short series we looked at simple numbered lists in Word. In this concluding part we will look at some of the important issues surrounding the use of Outline Numbering.

First of all, some information on the general uses of Word outlines. Word outlines involve allocating paragraphs to different levels. So a main heading might be level 1, the sub-heading level 2, sub-subheading level 3 and so on to level 9. Standard paragraphs of text would not have a level, but would be ‘body’ text. Once these levels are established, they can be used to quickly re-arrange a document or to automatically create a table of contents. In addition, and with particular relevance to our numbering issue, they can be used to automatically create and maintain numbering throughout an entire document.

Outline example

Continue reading

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.

Continue reading

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: