page contents Microsoft Excel Archives - Base2 Training

Category Archives: Microsoft Excel

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 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 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 Excel – Data Validation

You are at their mercy!

When you create a spreadsheet for your colleagues to use, they could type in absolutely anything.  Incorrect entries will make the calculations, which you slaved long and hard over, generate the wrong answers.  As the old saying goes – “Garbage In, Garbage Out”.

Whilst you can’t be looking over their shoulders all the time, Microsoft Excel has a nifty feature to help ensure that the data typed into a spreadsheet is at least in the correct “ball-park”.

You can force them to enter a Whole Number or a Decimal, insist that the entry is a Date or a Time along with a variety of other settings. It is even possible to provide a fixed List of choices for them to pick from.

(more…)

Microsoft Excel – Evaluate Formula

What does this Spreadsheet Do?

Chris asked me to help him with a Microsoft Excel spreadsheet the other day. It was a “Profit and Loss” calculator which had been created by someone else.  It didn’t quite to do what Chris wanted and he needed to know how it worked.

Now, the thing about complex spreadsheets is that the author tends to build it up piece by piece.  Working out what the whole thing does needs a good deal of detective work.  Chris was going to have to act like Sherlock Holmes (which I guess made me Joan Watson).

To start with, the Workbook appeared to have four Worksheets in it.  However, we soon found references to sheets that we couldn’t see.  That one wasn’t too tricky.  The author had hidden some of the sheets.  Right-clicking on any of the Sheet Tabs leads to a menu with an Unhide… choice.  There were actually eleven Worksheets in the file and we were able make them all visible in no time.

(more…)

Microsoft Excel – Absolute References

Different types of References

Ever inherited a Microsoft Excel spreadsheet from someone, looked at the formulas and thought “what are all these dollar signs about?”.

Well, they are Excel’s way of defining what will happen to Cell References if you ever copy that formula to a different location.

In fact, there are three types of references available – Relative ReferencesAbsolute References and Mixed References.

(more…)