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 will 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 calculation (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.

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

• The Workbook (the spreadsheet in its entirety)
• Worksheets (pages or sheets within the Workbook)
• Cells (contained on all Worksheets)

Remove anyone of these aspects and Excel can no longer function.

Before we go into any detail at all about Excel we need to know how to start Excel. Starting Excel is no different to starting any other Microsoft applications (Word, PowerPoint etc) and can be done in any number of ways.

The three most common ways to start Excel are:

• From the Microsoft Office Toolbar
• From a shortcut icon on your desktop
• By selecting the Start button located at the bottom left hand side of your screen, then selecting Programs then Microsoft Excel.

Options 1 and 2 may not be relevant to you if your computer is on a network, so if you don’t have these options available to you, select Option 3. (If you wish to set up a shortcut to your desktop, or install the Microsoft Office Toolbar, please let me know and I will send you instructions on how to do so).

Lets now open Excel by selecting the Start button located at the bottom left hand side of your keyboard, then selecting Programs then Microsoft Excel and have a look at the screen in front of us.

Every time you start Excel, you are by default creating a new blank Workbook. The name of your Workbook has the default name of Book1, until you save the Workbook as a different name. A Workbook is like the outer shell of the spreadsheet and contained within it will be the Worksheet(s) and cells.

When you start Excel you should first see a blue bar at the very top of your screen. This is called the Title Bar. The title bar is where you will see the name of your Workbook once you have saved it. (Note that if you have not yet saved a Workbook, the default will probably be Book1). As you are familiar with Windows, you will notice to the right of the Title Bar the Minimise (hyphen), Restore (two squares) and Close (X) buttons.

There are many formats that we can save Excel as, but by far the most common is the default *.xls. (Replace the asterisk with your chosen file name). Some of the other popular formats are the *.xlt (Template) and the *.txt (Tab delimited). To see what I mean go to File>Save as and Excel will display the Save as dialog box (dialog boxes will be discussed later). This is where you initially give your Workbook a name (File name:), nominate a file type (Save as type:) and a location (Save in:). I say "initially" as once you have saved a Workbook for the first time it will by default always save as that File name - type and location you have chosen each time you save. If you select the drop arrow to the right of the Save as type: box you will see a list of all different types of formats an Excel Workbook can be saved as. 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, 97 and 95. I strongly recommend doing this ONLY if you need to, as it will increase the file size by 10-50% and render some of Excel’s newer features as not working.

When you are saving a Workbook for the first time Excel will display the Save as: dialog box no matter which method we use to save. The four methods that can be used are:

1. Go to File>Save
2. Push Ctrl + S
3. Click the floppy disk icon on the Standard toolbar (discussed below)
4. Go to File>Save as

If we close a Workbook that we have made changes to, Excel will ask us (via a message box) if we wish to save the changes we have made. If we close a Workbook without making any changes, no message will appear and Excel will close without saving. We will look at closing a Workbook below.

Just because we have one Workbook open does not mean we cannot create more Workbooks, in fact the only limit to the amount of Workbooks we can have open is restricted only by the available memory of the PC. There are three methods that can be used to create more Workbooks.

1. Go to File>New
2. Push Ctrl + N
3. Click the New icon on the far left of the Standard toolbar. It looks like a sheet with the top right corner folded over. Don't worry if you cannot see this, as we will be looking at Toolbars soon.

Whichever method we use Excel will create a new Workbook and the name (by default) will be Book2 or the next number in the sequence. As soon as we have created our new Workbook it will automatically become what is known as the Active Workbook. The other Workbook will still be open, but NOT Active! This is because there can only ever be one Active Workbook at any one time. To switch between open Workbooks, go to Window on the worksheet menu bar and you will see the names of all open Workbooks. To activate one of the other open Workbooks, simply select its name and it will become the active workbook. Close Book2 now so that we only have one Workbook open. As mentioned above you will not be asked if you wish to save, unless changes have been made.

Toolbars in Excel are where all of Excel’s features can be accessed. They contain both icons (pictures) and text. The main toolbar in Excel is called the Worksheet menu bar and is normally found under the Title bar. On this bar you will find nearly all the options available for you to use in Excel under the headings File, Edit, View, Insert, Format, Tools, Table, Window, Help. If you click on any of these options, a menu will drop down giving you the many other options available to you.

Take note of the short cut keys next to some of the menu options! Once you have made a selection from the Worksheet Menu Bar, Excel will most likely display what is known as a Dialogue box (as with Save as). To see what I mean by a Dialogue box, go to Edit>Go To, and Excel will display the Go To dialogue box. Excel has over one hundred of these dialog boxes contained within it. While here, take note of the question mark button on the top left hand side of the box, next to the cross (X). If you click on this and then select any part of the dialog box, Excel will display a brief description of what that part of the dialog box is designed for (give it a try). This is the same for all dialogue boxes. The cross (X) will close the dialog box. Close the dialog box.

As a standard, most people work with what are known as the Standard Toolbar and Formatting Toolbar in view, and during these lessons we will work with both of these toolbars showing.

To ensure that both of these toolbars are in view, go to View>Toolbars. Another menu option should open. It is on here that we can show and hide our most commonly used menu bars. A tick means the toolbar is visible, while no tick means it is not. Another method that can be used to do the same thing is to right click in the grey area to the top of your screen, immediately under the blue Title Bar.

Let’s ensure that both Standard and Formatting have a tick next to them, by selecting them with your left mouse button.

NOTE: You will not find a toolbar called Worksheet Menu Bar under View>Toolbars. This is because Excel will always open with this toolbar showing. It cannot be hidden via the Excel interface.

To quickly get a brief description of any menu option, simply push Shift+F1. Your Mouse pointer should change to a ? (question mark). Now click on any menu option and Excel will display a brief description of its function. Once you have selected the menu option your mouse pointer will return back to normal. To find the name of any menu option simply hover your mouse pointer over any icon.

Most Excel toolbars are what is known as "docked" by default. This simply means they are situated at the top of your screen directly underneath the Title bar. They can, however, be what is known as "floating" by undocking them. To see what I mean, notice a small raised, vertical ridge on the very far left of each toolbar. If you left click on one of these, and holding down your left mouse button drag the toolbar down until it becomes detached (undocked). This is what's known as a "floating" toolbar. You can now drag the toolbar and dock it to the left, right or bottom of your screen. To place the toolbar back at the top, either drag it back the same way, or double click its blue title bar.

Most of the time toolbars are docked underneath each other, but at times they may become docked side-by-side. This isn't too easy to spot, but basically if there are two Toolbars side-by-side the raised ridge for one of them will be somewhere other than the far left. If there are two side-by-side, simply drag it off (undock it) then drag to its new location

Once you have both the Standard and Formatting toolbars visible and in place have a brief look over all the icons and hover your mouse pointer over each icon to see its name. It is important to note that most of the options on these two toolbars are simply duplications of what can be found on the main Excel toolbar (Worksheet menu bar). Having these two toolbars visible makes it much easier and quicker to access the required option.

There is also another menu that makes working in Excel much easier, this is the right click menu. We will look at this when we look at Cells.

The Formula bar in Excel is where most users would do any editing of formulas or any entries that are within a cell. The Formula bar is a long white bar with an = (equal sign) on the left and the "Name box" (discussed below) on the right. If you cannot see the Formula bar, it may be hidden, go to View>Formula bar.

All Workbooks must contain at least one Worksheet. The maximum number of Worksheets a Workbook can contain is limited only by the available memory of the PC. As a default, Excel places three blank Worksheets in front of you. Each one of these has the default name Sheet1, Sheet2, Sheet3. By default all Worksheets are identical in that they contain Rows and Columns and Cells (all discussed below).

NOTE: You can change the default of three blank Worksheets by going to Tool>Options/General and changing Sheets in new workbook: typing in the number you require.

All Worksheets contains 256 columns and 65,536 rows. Each separate rectangle within the Worksheet is called a Cell. You will notice letters across the top of your Worksheet. These are the names of the columns and go from A through to IV. The numbers down the left hand side of your sheet of graph paper symbolise row headings and go from 1 through to 65,536.

Adding new Worksheets to a Workbook can be done in one of four ways.

• Go to Insert>Worksheet
• Right click on another Sheet name tab and select Insert and then select Worksheet.
• By pushing F10 the shift + I then W

The fourth way is not exactly inserting a new 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, then 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. You will notice that Excel will place a number after the copied sheets name in brackets. This is because we cannot have two Worksheets of the same name in the same Workbook. Name and Renaming Worksheets is covered later.

The logical opposite to inserting Worksheets is Deleting them. This can be done in any of the following ways.

• Right click on the Worksheets name tab and select Delete
• Go to Edit>Delete Sheet, a dialogue box appears to verify your request.

Whichever method we use, Excel will display a warning message letting us know that the deletion cannot be undone. This is not technically true as we could always close our Workbook WITHOUT saving and then re-open the Workbook, but of course this means we would loose any other changes we had made.

We can also name our Worksheets with meaningful names (which is a good idea!). The only restrictions we have with Worksheet names is:

• They cannot exceed 31 characters.
• They cannot contain the following characters: \ / ? * [ ].
• The name cannot be the same as another Worksheet in the Workbook.

While it is not totally necessary, try to AVOID using spaces between words. For example, if you wanted to name a Worksheet Bills Hours use BillsHours instead! The reason for this is that some of Excels formulas have slight problems when they reference Worksheets that contain spaces.

To name a Worksheet we can do so in any of the three methods below:

• Double click the Worksheet name tab. This will allow you to now type the name. Once finished, push Enter or click any cell.
• Right click on the Worksheet name tab and select Rename from the shortcut menu. Then type the name.
• Go to Format>Sheet>Rename and then type the chosen name.

At times we may need or wish to move a Worksheet to another position in the Workbook or even to another Workbook. We have already looked at how we can make a copy of a Worksheet, 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. If we do not want to copy but rather move the entire Worksheet to another position in the Workbook we can simply left click the Worksheet name tab, then holding down the mouse button drag it to it's new position and release. The other method is to use the Move or Copy dialogue box. This can be accessed in two ways.

• Right click on the Worksheet name tab and select Move or Copy
• Go to Edit>Move or Copy Sheet…

It makes no difference which method we use! You will see on this dialogue box a To book: box with a small drop arrow to the right. The selection in this box will determine where our sheet will be moved or copied to. The default name in this box will be the active Workbook name (the current Workbook we are in). There will also always be another choice, if we click the drop arrow. This is (new book), if we choose this option Excel will automatically create a new Workbook for the sheet to be moved or copied. Within this box will also be the names of any other open Workbooks, should there be any.

Directly below the To book: box is the Before sheet: box. Our selection in this box will decide which position our sheet will be moved or copied to. Last of all is the Create a copy checkbox, if we check this box Excel will create a copy of our moved Worksheet. In other words our original sheet will remain where it is and a copy of it will be moved to our chosen location. If we leave it unchecked (default) the Worksheet will be moved and not copied. The best way (as with most of excel) is to jump straight in and try out different options - you cannot do any harm!

Let’s now have a look at the cells on our Worksheet in more detail.

These are without doubt the very backbone of Excel. On each Worksheet there are 16777216 cells. These are divided into 65,536 Rows and 256 Columns, as I mentioned earlier. 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 Worksheets if we need to, each with 16777216 cells, this should give us plenty! A cell is defined as the intersection of where a column and row meet, just like the coordinates on a road map.

The method that Excel uses to reference these cells (as a default) is called the A1 style reference. When we refer to a particular cell we use it's cell Address (or location) to nominate the cell. The method excel uses to reference cells is a very simple, but highly effective method, often referred to as the grid pattern method this is exactly the same as the method used to locate a street on a road map!

On the blank Worksheet in front of you click in cell C7 with your left mouse button. You will know you are in C7 because C7 will have a black border around it and also a small black square in the bottom right (Fill handle, discussed later). The C in the column co-ordinate will be bolded and raised, and the 7 in the row co-ordinate will be bolded and raised. This cell (C7) is now the Active cell. You will also note that the cell address (C7) will be in the Name box (to the left of the Formula bar).

In the situation above we have selected one cell only. We can select many more cells at one time if we wish. When we do so, we have selected what is known as Range of cells.

To select a range of cells we simply select any one cell (position the cursor so a cross appears), then holding down our left mouse button, drag in the direction we wish to select. It is important to note here that although we have more than one cell selected (a range) there is only one active cell and this will always be the first cell we select. There is no limit to the number of cells we can select on a Worksheet, to select all cells either push Ctrl + A or the Select all button, (the empty grey rectangle in the upper-left corner of a Worksheet where the row and column headings meet). We can also select entire columns or rows by simply left clicking on the column letter or row number.

I mentioned above while looking at Toolbars that there is another short cut menu in Excel, the right click menu. To see this simply select any cell and (you guessed it!) right click. This will display a list of arguably the most commonly used features of Excel. We won't go into any detail on these at this stage but we most certainly will in later lessons.

While we are on the subject of the right click, you will notice as we go into Excel deeper that it has many of these right click menus. The menu that we are presented with is wholly dependent on the area of excel we are in at the time.

There are many ways that can be used to move around a Worksheet and the method used is purely user preference.

At the far right of your Worksheet window is what is known as a Vertical scroll bar. This bar has an upward pointing arrow at the top of it and a downward pointing arrow at the bottom of it. You can vertically scroll up and down through your document in one of the following ways:

• Place your left mouse button on the downward (or upwards) pointing arrow and click once to scroll slowly through your document. This will move you down one row at a time.
• Hold the left mouse button down on the downward (or upwards) pointing arrow to move more quickly through the document
• Hold your left mouse button down and drag the grey sliding button between the black upward and downwards arrows. Notice when you select the last option a yellow box with the row number will appear. This is because you will not move to the location until you release the mouse button.
• Select any cell and then drag down. You may notice that doing this will display a small yellow box that informs you of how many rows you have selected and how many columns. This will only appear after the active cell has scrolled from view. You can also do the same by selecting any cell and holding down the Down arrow on your keyboard while holding down your Shift key.
• Use the Page Up and Page Down keys on your keyboard.

To get quickly back to the top of your Worksheet, push Ctrl + Home. This will always take you to cell A1.

At the bottom of your Worksheet window to the right is your Horizontal scroll Bar. The use of this is the same as with the Vertical scroll bar, except of course you move horizontally and you cannot use Page Up and Page Down.

If you happen to know the address of the cell you wish to go to we can use the Go To dialogue box we looked at briefly before.

1. Go back to cell A1 (Ctrl + Home)
2. Select Edit>Go To (or push your F5 key, or push Ctrl + G)

What has popped up in front of your now is the Go to: dialogue box. Note that your cursor is flashing in the Reference section of the box. This is where you type the cell reference that you wish to Go to. The big box (Go To) is where Excels stores the last four references used. If the reference is in here you would simply select it and click OK

Type in HS3156 in the Reference box and select OK (or hit Enter on your keyboard if you prefer). Excel will take us straight to the cell reference we typed.

To get back to the top left of the Worksheet, push Ctrl + Home. The Ctrl + Home method is simply a short cut key for typing A1 in the Reference box of the Go To dialog box.

While the Go To dialogue box can be handy for moving straight to any cell, there is an easier way! To see what I mean left click in the Name box (left of the Formula bar), type any cell address and push enter.

You are no doubt wondering why this is called the Name box? We will cover this in detail, but not in this lesson.

The only area of navigation we need to look at now is the Worksheets themselves. This can be done in one of two ways. The most popular and probably the simplest method is to simply left click on the sheet name tab of the sheet you want. This will automatically activate the appropriate sheet.

The second method is probably best suited when you have a lot of worksheets in the same Workbook.

You will notice to the far left of the sheet name tabs, you have four arrows. Place your mouse pointer over any one of these and then right click. You will see a pop up menu containing the names of all the Worksheets within the Workbook. You simply select the one you want with your left mouse button. The four arrows to the left of the sheet name tab are called Tab Scrolling Buttons. These four arrow buttons are to allow you to scroll through the Worksheet name tabs should your workbook contain more Worksheets than are currently visible. If you click the arrow with the vertical line after it, you will scroll so that your far right hand Worksheet name tab is visible. If you click the arrow without the vertical line, you will basically scroll one Worksheet tab at a time.

There are two ways to exit Microsoft Excel and close the program down. These are:

• By selecting the cross (X) at the top right hand side of your screen
• By selecting File, then Exit

Select either of these options to close down Excel. If we wished to Exit Excel and we were working in a Workbook that we have made changes to, Excel will ask us (via a message box) if we wish to save the changes we have made. If we wished to Exit Excel and we had a Workbook that we had not made any changes to, no message will appear and Excel will Exit without saving.

So in summary, we have discussed the three most important aspects of Excel, these are:

• The Workbook
• The Worksheets and
• The cells

We have looked at the definition of each of these aspects in detail. We have also covered:

• How to save Workbooks
• Create new Workbooks
• To insert and delete Worksheets
• Name, move and create new Worksheets in a variety of ways.

We have looked at the many ways of moving through the cells within your Worksheet, using a multitude of options, including the Go To option located under the Edit menu item.

We have looked at our Excel screen in detail, the toolbars and how to dock them and the formula bar.

We learnt how to get help on a specific menu item by using the Shift + F1 option.

And finally, we learnt how to start and Exit Excel in the correct way.