Category Archives: Training

Excel – requirements for competence

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

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)

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