Claire's IT Training and Solutions blog

Recently, I have been training on behalf of a major training organisation at locations in the City and more locally in Essex. I have had a great time – challenging courses and high-flying clients. BUT there have been some issues of communication that have made it hard to deliver the professional standards that this training organisation requires from me and promises the client.

I have written at length about the City job, where a lack of communication made things quite difficult for me – see my blog Compare and Contrast… Two training days

This week, I trained a local client for this same training organisation. This time, I received a very vague outline “Morning session to consolidate existing knowledge of Excel 2010; afternoon for more advanced topics”. I went equipped with my laptop and all my exercises on a USB stick, ready to “wing it”. On arrival, I checked the paperwork that had been sent to the client – and lo and behold, there was a perfectly useful course specification, tailored to the client’s requirements.

On both occasions, I was able to deliver good quality training in spite of the communication issues, but I felt that I would have come across as more professional and confident if I hadn’t had to spend the first few minutes of each day trying to work out what I would actually be doing.

Is it so difficult to forward an email?!

Last week, I delivered an Introductory Excel course at a company in Maldon. I am also booked to go back there in the last week of June to deliver the Advanced course. While I was setting up, one of the delegates for the Advanced course popped in to discuss her requirements. Where possible, I do like to tailor my course material so that it is relevant to the delegates, making it more memorable, and with three weeks’ notice it seemed like a good idea to pinpoint anything they particularly wanted.

Part of the Advanced course looks at programming macros. My current exercise looks at the problems inherent in maintaining a bank balance workbook with a running total balance. However, in this firm, they have a particular need to do data file manipulation. Reports come into their system from an external database, and then need considerable manipulation to convert them into something that Excel can analyse. My delegate takes a whole afternoon to process this report, every week, as it runs to hundreds of rows, sometimes thousands.

I asked for a sample “before and after” set, anonymised with “Client 1”, “Client 2” etc replacing real names. I received the files (via my training agency) the same evening. I have now had a look at the spreadsheet and found that the processing is, as I had hoped, a fairly straightforward job, though very repetitive – an ideal candidate for a macro.

I shall be able to guide the delegates in creating a macro to delete an un-needed column, then locate the first data record, copy data into blank cells, and delete rows containing header information (which is repeated for each record in the source data). We will need to tweak the macro to deal with records containing different numbers of rows, and find out what happens when we reach the bottom, and deal with that. We will need a Do While… or Do Until… loop to handle the repetition, perhaps with a message box to say when the records have been processed. We can finish with a bit of further tidying up at the end, then move on to using pivot tables and charts for analysis, also in the course outline.

Developing the macro took me about an hour. Once created, it takes about 20 seconds to run: it would be a lot quicker if I turned off screen refresh, but I wanted to watch!

Normally, I would charge consultancy rates for macro creation, but this is such a common requirement that, in this case, I feel justified in calling it course development. It will come in very handy as an advanced macro exercise.

We’ve come to the end of the tax year so I thought, as I had a little time spare this afternoon, that I would see if my intelligent worklist/invoicing spreadsheet would speed up the preparation of the end-of-year report I send to my accountant.

This job normally takes me a couple of days, and I have just done it in less than an hour.

I made a copy of my work schedule, then deleted those entries that fell before the start or after the end of the tax year. At the ends of the year, I checked a few figures and kept those entries where I had invoiced within the 14-15 year, or where work had taken place then, even though invoiced before or after the year. (I have some clients who buy a block of training lessons, so it is quite common that an invoice will cover work spanning a period of several months.)

Having decided which records I needed, I refreshed my various pivot table reports, including my breakdowns of expenses and mileage claims, then filtered these so that only then entries within the tax year were included.

I copied all the relevant invoices into the same folder, and edited the hyperlinks in my invoice summary to point to these in their new location. So the accountant will have all my invoices as well.

I copied my main data table to its own location, replacing formulae with values, so that I was able to delete my lookup tables sheet.

Finally, I stripped out the code that I use to generate my invoices, and saved the workbook as a macro-free .xlsx file.

Job done!

This week, I was engaged to work on Wednesday and Thursday for a very prestigious end client based in Canary Wharf. It was a mixed brief, as they are training up staff at all levels, and the modules included brief taster sessions on Windows 7 and Office 2010, as well as half days on PowerPoint and Excel (Excel at Intermediate and Advanced level). I was booked for the two days this week and a further two days in a few weeks’ time, presenting these sessions to groups of 10-12 delegates at a time

Due to other commitments, I had a very tight time window for course preparation, really just Tuesday, the day before my first day. Up until the end of the previous week, all I had been told was that I would be doing Windows/Office taster sessions on the Wednesday, but not what they wanted on Thursday. I am highly experienced in Excel and train it often, so would only need brief preparation for that, but I train PowerPoint less often so would need to brush up my course presentation and tweak it to their outline, so that was my plan for Tuesday.

On Tuesday, I was informed that the Wednesday training had been altered so that, while I would still be doing the Windows/Office tasters in the morning, they would require a half-day PowerPoint in the afternoon. Also (at last!) that the Thursday sessions would be Excel Intermediate in the morning, with PowerPoint again in the afternoon. So I spent the day gathering my files and notes for the tasters and the PowerPoint, and making sure that I had checked it all against the client’s course outline and covered all topics. I had to create some new exercises in PowerPoint, so it was quite a busy day.

My other problem was getting there. I seldom work in that part of London, so hadn’t yet worked out the optimal route. I considered driving in to Stratford, but chickened out at the last minute on Wednesday morning, instead driving in to my usual station to get a train in and use a mixture of tube, overground and DLR to get to Canary Wharf. As it turned out, my choices were pretty poor and, although I had left in plenty of time, I used up pretty much all my leeway getting there, so arrived just barely on time, only to be held up in reception while they sorted out who was going to admit me to the building.

Upstairs (27th floor – fantastic view of iconic buildings) I was shown to the training room. Here, I was informed that the PowerPoint session in the afternoon would not run according to the (introductory level) outline, but instead would be at a higher level and “tailored to the delegates’ requirements”. Evidently the client had provided a new outline, but somewhere along the chain this had not been passed to me, so I would need to “wing it”. This happens (very) occasionally, but it’s a horrible situation for a trainer to be in.

I then needed IT so set up the AV gear and create shared folders for my course files, but there really wasn’t sufficient time to do the folders by then, so my first taster session was largely a demo, rather than being fully hands-on, though it was well received and the delegates tried things out using blank documents or their own files. The second taster went much better, though, as the delegates were able to experiment using the files I had provided for the purpose.

The afternoon followed on swiftly from the morning. Because they were mixing taster sessions (late lunch break) with a half day session (earlier lunch break), there wasn’t time for me to get anything to eat – I just had to go straight from one thing to the next. But the afternoon PowerPoint session went very well in spite of my worries, and showed that my preparation had been worthwhile. I was able to use my introductory exercise, but went into more depth on the topics around structuring presentations, using master slides, and generally following up on delegates’ questions with practical demonstrations and extensions that I had to make up on the spot.

When the day was ended, I travelled home and arrived famished, having not had anything all day except for a biscuit and two cups of coffee. Fortunately, my husband had cooked a lovely meal which was virtually ready when I got back! After dinner, I just had to gather together the files for Thursday’s Excel session as I hadn’t had time the previous day.

Thursday was altogether a different story. I travelled to a different station and was able to get to Stratford direct (though I did have to stand all the way) and then the DLR to Canary Wharf (though I did have to stand all the way). In spite of leaving somewhat later in the morning, I arrived a good hour earlier, and had a leisurely breakfast before going to the client’s premises. Another wait at reception, but once admitted to the building, I knew where I was going and everything was set up already from the day before, so all I had to do was copy my Excel files over. I even had time to add a few new slides to my Excel presentation!

The Excel half-day went very well. It was just intermediate level, so I could pretty much deliver it in my sleep, but as always some interesting questions and points came up. And in the afternoon, I repeated the PowerPoint exercise of the day before – this time as originally planned, concentrating on the beginner-level topics.

A swift and comfortable journey home – and I even got to sit down!

The first day of training at a new location is always harder, when you don’t know the way or quite what to expect when you get there. This time, it was made much worse by my poor travel decisions the first day, and by my very limited preparation time. The client made it even more difficult by getting information to me so late, and the last straw was the communication breakdown that kept me in the dark about the final, last-minute changes. In the end I feel pretty proud of the fact that I was able to deliver high-quality training to some two dozen delegates on that Wednesday and keep everybody happy.

About two years ago, a visitor to my networking group, BSI Dunmow, spoke to me at some length about the work I do, specifically asking for some ideas on a complex mailmerge that she was doing using Microsoft Word. She comes from a fair way out of the area, so decided not to join us, but kept my card and recently got in touch again.

She produces legal documents for a solicitor which, up to now, she has been producing in Microsoft Word. These include a schedule of work done (stored as raw data in a Word table), then merged documents to produce a fair copy of the schedule, with a breakdown of total costs at the end typed in by hand, and individual information sheets for each item on the schedule and for each item in the totals. She also had a set of equivalent files for personal attendances.

Recently, it has been decided in the practice that they need to analyse the costs of items that fall into different categories – information that was not even in the raw data document. She rang me to talk about this, and I felt that now would be a good time to move the data part of the requirement into Excel so that it can be analysed.

Excel is somewhat lacking as a word processor, but I found I was able to structure the “raw data” neatly enough to be able to print out in the same way as her original merged schedule and attendance documents. With the help of some pivot tables, and some careful text manipulation using functions, I was able to add the breakdown of total costs at the end of each report, and another pivot table gave the category analysis. Finally, I tweaked her original merge document and got it to reference the new spreadsheet as the data source to produce the item-by-item pages.

There was one disproportionally difficult part of this. Because the work is done for a legal firm, dates and times are separated by full stops, not slashes and colons as they are in Windows. Formatting these in Excel was easy enough, but the number formatting in Word doesn’t work in the same way and it took me a while to work out the correct formatting for times – especially when I wanted to total time and it tried to show the result as days and hours!

I have had a course in my diary for a couple of weeks: one of my “soft skills” ones, Business and Report Writing. As the date of the course approached, I contacted the manager of the training organisation in the normal way to check whether there was anything special I should know about the course in advance. I also wanted to confirm that the client would have the necessary facilities on site: a projector and computer for my slideshow being the main requirements.

When the manager got back to me, I was somewhat shocked. The course outline as supplied to the client organisation was considerably different from the one I had worked with previously, and I was told that there would be a projector but no computer. Also, as I would be working through a third party organisation, all my material would have to be branded with their corporate identity. Finally, the majority of the delegates would not have English as a first language!

As the course was to be held on the client site in an industrial estate in an outer area of London, I was not happy about taking my laptop. I would have to lug it into and across London, then catch a train out to the nearest station and walk over a mile to the location. I was already burdened, as I had a social appointment in London that evening and so needed to bring a bag containing my “posh togs”. I rang the manager in some consternation and told her of my difficulties, and that I feared that the changes in the course might lead to me not being able to deliver good quality training.

She reassured me that the outline wasn’t as different as it looked – really, it was more a change in emphasis than in content. I hadn’t taken the time to work this out for myself as it was quite short notice and I had only really glanced at the different wording. She said that she would tell them I absolutely couldn’t take a laptop in myself, and they would need to find one somewhere. She had already arranged for a late start time to allow for the difficult journey. Finally, she told me that she was perfectly confident that I would deliver a good day’s training, as she knows the quality of my work.

Somewhat mollified, I began making my preparations for the course. I branded my material and sent it in for printing and delivery to the client, and tweaked some of my exercises, adding one aimed specifically at people who did not learn English as their mother tongue.

I delivered the course yesterday. The travel there and back wasn’t much fun, and even with the adjusted start time I was very nearly late due to train delays. But I made it – just – and everything else went fine. There was a laptop hooked up to the projector for my slideshow, and my handouts had been delivered. The course delegates were hard-working and enthusiastic and all spoke pretty good English – much better than my French! They still had problems with some aspects of our very complex language, though, and appreciated my language-specific exercises.

I received very good feedback from the course delegates, and was able to send my manager some very positive course appraisals this morning.

And I had a brilliant evening too!

A friend from my networking group sent me an interesting article from moneyweek.com, entitled “The hidden evils of spreadsheets”. A financial modelling firm has found that in nearly one in five large businesses, spreadsheet errors led to poor business decisions and huge financial losses, with perhaps only a third of users having had any training in using these powerful programs.

The article looks at big businesses, and presumably the proportion of trained users will be even less among small business users.

Spreadsheets are a very powerful tool, but in order to get the best out of them, you need to set them up properly and understand their operation. Many people make the mistake of thinking that, because the computer will do the maths, they don’t need to! But you still need to know what you are doing: the computer may get the answer right, but you need to get the question right in the first place. For example, if you enter a formula to calculate 2+3*4, you may presume the answer to be 20 – and be surprised when your spreadsheet informs you that the correct answer is, in fact, 14! If you get the question wrong, the answer will not be the one you expect.

I have been working with spreadsheets since they first came into existence – I started with VisiCalc in 1979! I know how to set them up neatly and efficiently, with properly constructed formulae. I can work with powerful features like tables, charts, pivot tables, macros and VBA programming in order to automate complex tasks. I can train clients to have a good understanding of how they work, and design spreadsheets that are easy to use, for a variety of different needs – not all maths based!

Contact me if you would like training, or if you would like me to look at your existing spreadsheets with a view to optimising them and ensuring that your business decisions will be based on the right facts!

One of my clients accepts credit card payments. He has to have his card reader verified periodically, and checked for online security. This is a complicated process, involving a lengthy self-certification questionnaire and a remote security scan carried out by the compliance authority.

I originally sorted out his verification in April last year, over a period of several weeks, with multiple failed scans and highly technical telephone discussions with the verification agency and his broadband equipment supplier. This time, I at least had a rough idea of what to expect when he called me back in.

The first job was to check the security of his IP address. This is done a scan run by the remote site, so all I could do was kick it off and wait. It took a few days for him to be notified that the scan had been run – and irritatingly, the email didn’t actually say whether it had passed or failed. But I went in to see him again today, logged onto the scan site, and found that it had passed. Then it was just a matter of submitting the scan result to the authorising agency, and completing their questionnaire on other security matters.

Not the sort of job I envisaged when I first took on this client – but I can see why he wanted me to take it over for him! It will be an on-going commitment, as the system must be rescanned and his procedures verified quarterly, with a major annual update. So I have booked him in for a date just short of three months away, and hope I will remember all the hoops to jump through by the time June rolls around.