page contents IT Training Archives - Base2 Training

Category Archives: IT Training

The Wrong Course

Getting it Right

We like to think of ourselves as a training company that cares about its attendees, so you can perhaps understand how passionate we are about making sure what we deliver matches what the delegate wants and needs.

I’m sure many of us have been sent on a course where the tutor has a thick manual of topics to deliver, and that’s what you’re going to get (whether you like it or not !). Not an ideal scenario.

So where does the responsibility lie for making sure the course content matches the attendee needs ?

(more…)

Microsoft Excel VLOOKUP – What would I use that for?

Exploring this Excel Function

At the start of a Microsoft Excel course I always ask people  – “What do you Need to Know”.  I’m trying to find out what each attendee wants to achieve from their time spent with me.

Often the VLOOKUP function is mentioned.  “I’ve heard other people talk about it, but I’m not really sure what it does”.

You won’t be surprised to learn that it is designed to look things up!  However, there are two distinctly different ways that it can be used.

(more…)

Project Management – Excel or Project

Which should I use?

Once upon a time, a customer phoned me and said “Paul, I’ve seen this amazing project plan done in Microsoft Excel.  All you do is change the numbers and everything updates.  Can you show me how to set one up.”

I have to admit I was sceptical.  I had seen Excel Workbooks which emulate a Gantt Chart.  Some used a Bar Chart to do that, others used the Formatting features.  However to make the thing “sing and dance”, surely you need proper Project Management software.

Nevertheless, it set me thinking.  When would you use Microsoft Excel to plan projects?  In what circumstances is Microsoft Project the right way to go?

(more…)

Microsoft PowerPoint – Theme Fonts

Where are we up to?

This is the third in a series of blogs on how to impose a corporate look or create a personalised style for your presentations.

In Microsoft PowerPoint – Templates and Themes we saw that a Template is concerned with the Layout of slides; a Theme affects their Formatting.

An important step in designing your own Theme is settling on an appropriate Colour Scheme.  This was discussed in Microsoft PowerPoint – Theme Colours.

The next thing to consider is choosing the correct Fonts.

(more…)

Microsoft Excel – Working with Dates

Serial Numbers

Our blog Microsoft Excel – Understanding Dates looked at how the program stores dates behind the scenes.  We found that the date 30/06/2016 (which just happens to be my birthday) is actually stored as the number 42551.

This Serial Number turned out to be the number of days since 1st January 1900.  Serial Numbers make it easy to do arithmetic with dates.  Want to know what the date will be in thirty days time?  Just add 30 to today’s date and Excel will work it out for you.

This time we are going to look at some the Functions Excel provides which work with dates.  We will also explore some tricks to change the way a date looks.

(more…)

Microsoft Word – Getting Control

Who is in control here?

I did a workshop for three ladies from a local organisation.  They had inherited a set of Microsoft Word documents which detailed the procedures that their department had to follow.

The trouble was that they had little experience of the program.  Looking at the documents, it was clear to me that the person who created them didn’t know much about it either!

You see, if Microsoft Word documents are well designed, they are a dream to work with.  However, if they are badly put together, altering them can be a nightmare.  It seems as if the program is in control, not you.

(more…)

Microsoft PowerPoint – Theme Colours

The story so far…

In a recent blog – Microsoft PowerPoint – Templates and Themes – we explored the difference between these two concepts.  We found that a Template defines the layout and positioning of the objects on the various types of Slide in a presentation, whereas a Theme sets the formatting of those objects.

Furthermore, we discovered that a Theme allows you to change Colours, Fonts and Effects to create a corporate look or personalised style for your presentations.

This time we will concentrate on features to do with colour.

(more…)

Microsoft Access – Hide Footer Entries

I’m here to help

James contacted me as part of our Training PLUS service.  This allows people who have attended one of our courses to ask for help with any problems they encounter when trying to put the training into practice. [The Training PLUS service is entirely free!]

In James’s case, he was attempting to create a report in Microsoft Access. He wanted the elements he had put in the Page Footer area to appear only on the first page of the report, and this was proving difficult to achieve.
(more…)

Microsoft Excel – Understanding Dates

An Interesting Problem

A while back a client got in touch with me.

Sarah had downloaded a file of financial transactions from her bank and loaded them into a Microsoft Excel workbook. When she looked at the dates against each transaction, something strange had happened. Every date was exactly four years and one day earlier than it should have been. What on earth was going on?

How are Dates Stored?

This turns out to be all to do with the way Excel stores dates behind the scenes.  In the following illustration, I’ve typed the same date twice (which just happens to be my birthday!).  The version on the left is formatted to look like a Date, the one on the right is formatted as a General number:

 

When you enter (say) 30/06/2016, Excel doesn’t actually store those characters.  Instead, it stores a Serial Number which represents that date.  The Serial Number for 30th June 2016 is 42551.

Where does that number come from?  It’s the number of days since 1st January 1900 – honest!

You can prove that by trying it the other way around.  Type 10 into an Excel worksheet and then format it to look like a date.  You’ll get 10/01/1900.  Entering 10000 will give you 18/05/1927, while 20000 equates to 03/10/1954.

Why is it done that way?

The developers of spreadsheet programs decided to use this system so that arithmetic with dates was easier to organise – [incidentally, this is a concept which pre-dates Microsoft Excel].

If the dates were stored using the characters that you type (i.e. 30/06/2016), a formula to calculate an entry for the following day would be quite complex.   Issues like “thirty days hath September” would have to be taken into account.

When a date is stored as a Serial Number, all you have to do is add 1 to that number.  Microsoft Excel has built-in coding to convert the new number into the correct date:

 

What has this got to do with Sarah’s problem?

There is a little known option in Microsoft Excel called – Use 1904 date system.  When this is turned on, Serial numbers are calculated starting from 1st January 1904.  [Why have two difference systems? – I’ll explain that later in this blog]

If you type 30/06/2016 into a workbook which is set to use that system, the serial number stored is 41089:

 

There is no problem in having two systems, as long as you are consistent.  The option affects an entire workbook so copying dates between sheets in the same workbook is fine.

However, try copying a cell containing 30/06/2016 from a workbook using the 1904 system, to a workbook using the 1900 system.  What you are actually copying is the Serial Number 41089.  In the 1900 system this number gets translated to 29/06/2012.

This is exactly four years and one day earlier than it should be – which was precisely Sarah’s problem!

A Bit of History

The earliest versions of Excel for the Mac only had the 1904 date system – (in fact hearing that Sarah was using an Apple Mac computer was my first clue towards solving the problem).  Later versions gave you the option of choosing which system you wanted to use, as do Windows PC versions.

The Mac designers remembered that the year 1900 wasn’t a Leap Year.  The rules say that the first year of a new century is only a Leap Year if it is divisible by 400 – the year 2000 was one, 2100 won’t be.  By staring their Serial Numbers at the 1st January 1904, they could avoid any complications arising from that fact.

Interestingly, Lotus 1-2-3 (the market leading spreadsheet program of the mid-1980’s) allowed you to enter 29/02/1900 as a valid date.  Its developers forgot about the 400 year rule.

This gave Microsoft a dilemma when creating Excel.  On the one hand they wanted the program to be accurate.  On the other hand they wanted the two programs to work the same way – (so that they could lure the maximum number of users to move from Lotus 1-2-3 to Excel).

They decided to deliberately leave the “bug” in place.  If you format an Excel date to show the day of the week, it gets it wrong for dates before 1st March 1900.

How did I get the date 01/03/1900 to display Thursday?  Find out about that (and a few more tricks for manipulating dates) in our blog Microsoft Excel – Working with Dates.

Microsoft OneNote – ever thought of using it?

What is it?

Even in this modern, digital age, I still get a lot of paper-based information through the letterbox. To keep from drowning in it, I do like to be organised (to a degree). I have ring-binders with Bank and Credit Card Statements in them. Others containing letters and offers. There is a folder with handwritten notes in it about the “New Garden Shed” project.  Maybe it is an age thing, but I just feel more comfortable doing it that way.

However, I’m a computer person as well.  I’ve been tracking my Bank Statement in an Excel spreadsheet for years (it does do negative numbers!).  Excel is very structured; its rows and columns layout is well suited to storing financial transactions.

The file of information about the shed isn’t structured at all, so finding things is tricky.  I could type it all into a Word document, but the layout would be too formal.

This is where Microsoft OneNote could come in useful.

(more…)

1 2 3