Spreadsheets have been in use on personal computers now for some years. They are used in nearly every office environment for any number of reasons. In days gone by they were used only to perform basic math, such as adding, multiplying, subtracting and dividing. Today's spreadsheets can be significantly different and far more advanced. The leader in the spreadsheet field today is without doubt Microsoft Excel. The reasons for this become very apparent the more you learn about Excel. You will find that by following a few basic fundamentals when starting a spreadsheet you will be able to extract, change, modify, report, add and manipulate data in a way you never thought possible. Sadly most users of Excel never realise the full potential of what they are using. In fact all too many users simply use Excel as no more than a personal whiteboard, with the occasional SUM formula. This is a great shame as it is designed to do so much more than this.

I will show you how you can use Excel to achieve all you want and need. As we progress you will also find yourself wanting more and it can become an unquenchable thirst (believe me I know!). By completion you will be able achieve a lot more than you can now, but even more importantly you will have the knowledge to be able help yourself. You will notice throughout the course that I do NOT use screen shots to aid you. I do this intentionally, as I believe that by doing so forces the student to think a lot more about what they are doing.

What's it all about

Excel is spreadsheet program that hinges entirely on three very important aspects.

1. The Workbook
2. Worksheets
3. Cells

Remove anyone of these aspects and Excel can no longer function. Lets look at each of these is turn.

This is like the outer shell of the spreadsheet contained within it will be Worksheet(s) and cells. There are many formats that Excel can save itself as, but by far the most common is the default *.xls. Some of the other popular formats are the *.xlt (Template) and the *.txt (Tab delimited). When you save a Workbook as a *.xls. you may notice that there are different choices of the .xls types. These different types are for backward compatibility with previous versions of Excel. You can also save the Workbook as multiple versions eg; 2000 and 97. I strongly recommend doing this ONLY if you need to as it will increase the file size by 10-50%.


Worksheets with a Workbook are like pages in a book. The default for the amount of Worksheets within a new Workbook is three. We can change this by going to Tool>Options/General and changing Sheets in new Workbook number. The number of Worksheets within a Workbook is limited only by the amount of available memory on your computer. Adding Worksheets to a Workbook can be done in one of four ways.

1. Go to Insert>Worksheet
2. Right click on another Sheet name tab and select Insert and then selecting Worksheet
3. By pushing F10 the I then W

The fourth way is not exactly inserting a Worksheet, but rather making a copy of an existing Worksheet. To do this select the Sheet you want to copy then left click on the name tab and holding down the Ctrl key drag it to one side, then release. This is a great time saver when you have spent hours setting up a Worksheet with all the formatting you want.


These are without doubt the very backbone of Excel. On each Worksheet there are 16777216 cells. These are divided into 65536 Rows and 256 Columns. The number of Rows and Columns in a Worksheet are fixed, which means we cannot add more and we cannot take any away. So as a consequence the number of cells are also fixed. This should never become a problem as we can add a lot of Workheets each with 16777216 cells - this should give us plenty! The method that Excel uses to reference these cells is called the A1 style reference. It also supports R1C1 style reference ("R"=Row, "C"= Column) for backward compatibility. These two styles of references are very simple and effective and are the same method you would use to locate a Street on a road map ie; grid pattern. The default for Excel is now the A1 style reference, but we can change this by going to Tools>Options\General and checking the R1C1 reference style box. Personally I much prefer the A1 style (I guess most do that's why they changed it). When we refer to particular cell we use it's Address to nominate the cell.

There are two methods we can use to reference a single cell or a range of cells, these are Absolute Reference ($A$1) and Relative Reference (A1). The dollar symbol is used to Absolute the Column ($A) and to Absolute the Row ($1). What this means is if we put: =$A$1 in cell B1 and then copied this to cell C2, the cell C2 would contain the exact same ie; =$A$1. If we did the same with the Relative reference cell C2 would contain ². To rotate through changing a cell reference from Absolute to Relative, type ¡ in any cell then place you mouse insertion point in the Formula bar within the address (or press F2) and push F4. This will rotate the cell through Relative to Absolute.

When we refer to a range of cells we are talking about more than one cell. There are two types of ranges in Excel, these are a Contiguous ranges and a Non-contiguous ranges. A contiguous range of cells is also known as an Area. A contiguous range is an area of cells whose cells borders connect. While a Non-contiguous range is more than one area whose cell borders do not connect.

While it is true that we can refer to cells using the cell address there is another method we can use (and should often) this is naming a range. We can do this in a lot of different ways. The easiest is to select you want to name then type a one word name in the Name box (this is the box to the left of the Formula bar). Naming ranges is a good idea as it can make our formulas much easier to read.


All the above information is the least you should know to be able to use Excel effectively. While it is true many users can get by knowing a lot less, that is all they are doing "getting by"! Now that we know about the foundations of Excel we can now move on to learning how we should set up a spreadsheet. This becomes very important as you learn more about Excel, as you will find yourself delving deeper and deeper into it's capabilities. If you have not set up your data correctly initially you will either not be able to do as much as you want or give up trying.


We now know that Excel has far more Rows than Columns (65280 more to be exact) and this something we should keep in mind when we set up any spreadsheet. You will also find as you delve deeper into Excels functions that they are set up for spreadsheets, which have been set up with Columns as headings and Rows holding the data. Below are a few rules that you should try to adhere to.

1. When putting in headings bold the font. This will help Excel recognise them as headings when you use one of its functions eg; Data>Sort.
2. When putting data into the data area of your spreadsheet try to avoid blank rows and columns if possible. This is because a lot of Excels built in features will assume a blank row or column is the end of your data. It also helps with a lot of Excels formulas.
3. Have your data sorted if possible. Excel is very rich in what is known in Lookup & Reference formulas and many of these rely on your data being sorted in a logical order.
4. Use real dates for headings and format them appropriately. By this I mean if you want the names of the months as headings type them in as 1/1/2001 , 1/2/2001 , 1/3/2001 etc then format them as "mmmm". This is a very simple procedure that is all too often overlooked by many. If you have real dates as headings life will be much easier further down the road when you need to use them in formulas.
5. Avoid merging cells if possible. Use "Centre across selection" instead. This can be found by going to Format>Cells/Alignment and select it from the Horizontal box. Merging cells can cause all sorts of problems later on that that will have you scratching your head.
6. Don't put in one cell what could go in more than one cell. Say you have the names of 100 people to put in your spreadsheet don’t put their full name in one cell. Instead put the First name in one cell then their surname in the next cell to the right. If you need to place them into one single cell at a later stage, this can be done very easily - the same cannot always be said for the reverse.

Just by following these six rules you will find that you will be able to easily reference, manipulate and make use of Excels built in features. While you are building your spreadsheet keep in mind that you will need to change it as time goes on. I tell you this because while formatting a well-constructed spreadsheet can make it easier to read, the formatting should be one of the last things we do. I have seen people spend hours formatting their spreadsheet until it looks perfect, only to find they need to make a small change that renders their hours of work useless. All the above rules can be broken and we can possibly still end up with the results we want, but the road to get there will most likely be very long and winding.


I previously mentioned earlier about naming ranges to make your formulas easier to read. We can take this to yet another step in Excel and also give names to any constants. A constant in this context is a numeric value that does not change. Let's assume you need to set up a spreadsheet that constantly refers to a specific tax rate eg; 36%. You could type the rate (36% or 0.36) in all cells that need to use this value or we could give this value a name eg; TaxRate. To do this we need to use the Define Name dialogue box. We access this by going to Insert>Name>Define, or by pushing Ctrl + F3. In the Names in workbook box type the word TaxRate. In the Refers to box type: 0.36 or 36%. Click Add then click Ok. Now in any cell put: *TaxRate you should get the result: 3.6.

Not only does this make our formulas easier to read, but it will also allow us to change the value of TaxRate in one spot. This can save a lot of time and is much easier than trying to locate all cells that use the 36% had we simply just used the number.


Dates and times are a very important part of a lot of spreadsheets and as such it is very important you understand how Excel interprets them. Excel for Windows uses the 1900 date system. Excel for the Macintosh uses the 1904 date system. The 1900 system can be changed by going to Tools>Options\Calculation and checking the 1904 date system. If you are using Widows there is no need to change this. The 1900 date system starts from 1 January 1900 and has a numeric value of 1. This is how Excel sees dates, as numbers you can see this by typing the date 19-Apr-2001 in any cell. Now format the cell as a General number format, it will display the value 37000. This simply means that 19-Apr-2001 is exactly 37000 days from 1-Jan-1900. By using this method Excel can perform calculations on dates. The term that is used to describe these numbers is serial numbers.

We now know that Excel uses what is known as serial numbers to store dates. It also uses a very similar system to store times. It uses what is known as decimal fractions. In Excel 24 hours is equal to the whole number one. 12 hours is equal to 0.5 and six hours equal to 0.25. So Excel stores times as a portion of one, with one being equal to 24 hours. Again you can see the decimal fraction of a time by typing any time in any cell and format it as general. To now combine this we can see how Excel would interpret a date and time in a cell. Lets stick with the 19-Apr-2001 example and modify it to also include a time, let's say 12:00. We would enter this into a cell as: 19-Apr-2001 12:00. If we now format this cell as general we will see: 37000.5 with .5 representing the time portion.

Once you understand Excels method of storing dates and times it should no longer be a mystery on how Excel uses dates and times in calculations. While it is a very simple method it is also very effective.

A common problem that people run into when working with times is when they need to use hours greater than 24. Let's say in cells A1:A5 you have the hours 8:00 , 8:00, 10:00, 7:00, 7:30 respectively. These hours represent the hours worked in one week for an employee. We need to know the total hours worked for that week, so in cell A6 we put: =SUM(A1:A5). You will see that we do not get the result we expected, we end up with: 16:30. Why does Excel do this? Well when you use a formula that references other cells our result cell will automatically take on the format of the cell(s) it is referencing. Excel sees that we have times in cells A1:A5 and so assumes we want the same in our result or total cell and so formats it as h:mm. More often than not Excel gets it right, but as you can see in this instance it hasn't. But why the result of 16:30? Lets step through this to explain why.

If you add A1 and A2 (8:00+8:00) together you get 16:00.

If you now add this 16:00 to cell A3 (10:00) you don't get 26:00 you actually get 2:00.

What happens is, if you add 8:00 of the 10:00 hours to 16:00 you would get 0:00 (Midnight). If you then add the remaining two hours you of course end up with 2:00, the result we got initially.

So we now know that when Excel adds 8:00+8:00+10:00 we get 2:00

If we now add the remaining times 7:00 + 7:30 we get 14:30.

Finally we add 2:00 to 14:30 and we get the result that Excel got, ie; 16:30

Ok this is all fine, but we don't want that, we want to force Excel to keep going once it reaches 24:00 hours. We can do this by using a Time format of "37:30:55" or a Custom format of "[h]:mm" we then get our expected result of 40:30:00


No matter how proficient a person is in using Excel there will be times when you need to get help. Excel has a huge help file, it offers help on each and every aspect of Excel. While this is needed, the size of help file can often seem overwhelming or make it very hard to find help on the topic you want. Most people often snub their nose at using that annoying paper clip (Clippit). Don't become one of them as this help is always on hand and once you learn more it becomes easier to get straight to where you want to go.

There is also another method to get help and it's one I tend to use as my first port of call. Go to Help>Contents and index. This dialog box has three page tabs, Contents, Index and Find. Click the Find Tab, if this is the first time this has been used another dialog box will appear. If it is not the first time click Rebuild. The dialog box will give you three options, choose the third one Customize search capabilities. Now click Next and then Select all and click Next. Now select Include untitled topics and click Next and then Include phrase searching and Next again. Select Display matching phrases and then Next. Now select Support similarity searches and click Next then Finish. It may take a minute to build the help files for the options we chose.

You now have all the help files available to Excel. You will probably find the Find page the most helpful as it is a Key word search. Ok, lets assume we would like to find out how we could join two or more words together in the same cell. Type in the word: join in and in the box directly below you will see the word join and other similar words. You may also notice that some words are the same with the only difference being the first letter being a capital. If the word starts with a capital it means that the word is the very first word in the help topic, which often means it's the one you want. As you click on each word in the box the bottom box will list all help topics associated with the select word. Let's now type in the word text to the right of join Excel will now have ALL the help that contain the words join text. Click CONCATENATE, you will notice that the Find Similar… buttons is now enabled, click this and you will see a list of similar topics. In this case we have no need for this, so click Close, then click CONCATENATE again then Display. As you will see we have found what we want and all words we used in our search are highlighted. Let's say you have read this and have understood it, but you want to add a few of your own tips, prompts and help in your own words. Click Options then Annotate in here you can put in your own words. Once finished click Save and you will see a small paperclip in the top left of the help topic. This will stay here so next time you come here you know you added some other information for your own benefit.

Once you get into the habit of using this help system you will be able to find exactly what it is you want every time with minimal effort.


While Excel is without doubt the number 1 spreadsheet package in the World today it can do a lot more than just crunch numbers it's can easily manipulate text just as well. Most users of Excel unfortunately never come to realise anywhere near its full potential. Even though Excel can be found in nearly every office worldwide most are using it for no more than a big notepad with a few basic formulas in it.

Always keep in mind the three vital aspects of Excel, the Workbook, Worksheet and the Cell. Once you are in Excel, the cell becomes the very backbone of all that you do. Always keep in mind that there are many more rows than there are columns and set up all spreadsheets accordingly.

When starting a spreadsheet take the time to think it through and plan, if you get the foundations correct everything else will flow on. Adhere as much as possible to the 6 rules, it may mean a bit of short-term pain, but believe me this will be outweighed by the long-term gain.

Use range names where possible and define constant values. This may seem a bit more time consuming initially, but it will make life a lot easier further down the road.

Familiarise yourself with how Excel interprets dates and times. While not every spreadsheet uses them, most do. Excel itself recognises the fact a lot of spreadsheets will involve dates and times and so has numerous date and time functions available to the user. All of these functions rely on the serial numbers of dates and the decimal fractions of times and quite often the combination of the two.

Last but by no means least, use the help often so you will understand the way in which it works. Once you have found the answer to a problem that is complex, make use of the Annotate feature and you will thank yourself later.