Section 1 Starting and finishing Excel
From time to time in the course of working through these sheets you will be told to save your work on a floppy disk or to load workbooks from the floppy disk. You will be provided with a disk for this purpose. When working on your own computer you will probably keep your work on your hard disk.
Starting Excel
The classroom PCs have an Excel shortcut visible on the desktop
From the Start menu choose IT Services Apps and then Microsoft Office and then Excel
If the “Office assistant” appears, click the button labelled Start using Microsoft Excel
Once the application has started, the screen changes to display a blank sheet in a new Excel workbook. The sheet is ruled into columns and rows.
The intersection of each row and column makes a box, called a cell, and it is into these cells that you will type your data.
Cells
Each cell is referenced individually by a column letter and a row number which together create a cell address e.g. A1 is the first row of column A; B7 is the seventh row of column B and so on.
One of these cells (cell A1 in the illustration) is highlighted – that is it is shown with a thick border round it. The highlighted cell is known as the active cell. You can move around the worksheet making different cells active by means of the arrow keys. Try this and notice how the highlighting moves as different cells are made active. The Tab and Return keys also have the effect of moving the highlight on to make a different cell active.
You can also make a cell active by clicking on it with the mouse. Move the pointer over cell E5 and click. Notice how the mouse pointer is in the form of a cross when it is over one of the cells in the worksheet.
You can use upper or lower case for the letters in the cell-addresses.
Menu bar, toolbars and formula bar
At the top of the screen is the title bar containing the words Microsoft Excel -Book1. Below that comes the menu bar, which gives you access to Excel’s commands. To use a command, click on a menu name to display a list of commands, and then choose what you want from the list. You are probably familiar with this from using Word: if not, ask.
Beneath the menu bar come the toolbars. There are a number of different toolbars in Excel. To see a list, click on Toolbars in the Viewmenu. At startup, you should have two toolbars visible: the standard and formatting toolbars, as shown in the illustration on page1.
Below the toolbars, immediately above the worksheet window is a long thin area called the Formula Bar. Notice the equals sign in the formula bar. To the left of the equals sign is a box containing the address of the currently active cell.
Before proceeding, you will learn how to exit from Excel.
Quitting Excel
To quit Excel completely, you choose Exit from the File menu. If you have entered data that you have not saved, Excel will display a pop-up dialogue box asking if you want to save changes to your worksheet. You then have the option of saving and then quitting, quitting without saving, or cancelling the quit altogether, so that you are left in Excel.
Exit now, so that you know how to do it, and then start up Excel again.
Getting help
There are several ways of getting help in Excel:
Use the Help menu:
choose Contents and Index and then click on Index
scroll through the alphabetical list of topics, or type the topic you want in the box provided
Click on the question-mark icon on the right-hand-side of the standard toolbar to launch the Office Assistant
type a topic (such as “calculate total”) and then click Search
Most of the pop-up dialogue boxes also provide a Help button ( question mark in the top right corner) which gives information relevant to the task in hand
As you move the pointer over the toolbar buttons, a brief explanation of what each button does is displayed at the foot of the screen
Changing the layout of the screen
The toolbars
Look back at the illustration on page2, and compare it with what you can see on the screen. In particular, make sure you have got the same toolbars displayed. If in doubt, choose Toolbars from the View menu, and in the list that is displayed make sure that the Standard toolbar and theFormatting toolbar are selected (they should have a tick beside them). If you have difficulties, ask for help.
The Options command
You can change the appearance of the screen by means of the Options command.
Choose Options from the Tools menu
The options that you can change are grouped together in different categories. The dialog-box should be seen as representing a box of index cards with labelled tabs. Each card contains a category of options. If you click on a tab, that category is brought to the front.
The illustration shows the View category. To choose a different category click on one of the other tabs. Look through some of the different categories of options to see how you can change the look and behaviour of the program.
Click on the View tab
Make sure the options are shown as they appear in the illustration. To select or de-select an option, point at it with the mouse and click
If you have made changes, click OK, otherwise click Cancel
If Excel does not behave in the way described in these notes, you might find that the explanation is that some of the options are not set in the way that the sheets assume. In this case, look through the options to see if there is anything that might explain the behaviour of the program. For instance if Excel displays a blank cell where you have entered a zero, the reason might be that the Zero values option is turned OFF.
Section 2 Entering data
In this section you will learn how to enter data into Excel, how to move around your worksheet and how to save your data.
Workbooks and worksheets
You will have noticed that your Excel document is referred to as a book or workbook. A workbook can contain a number of sheets – if you look along the bottom of the screen you will see a series of tabs which enable you to jump easily from one sheet in your book to another. If your task is large and complex you might divide your data up between a number of worksheets, but we shall not be doing this in the first part of the workshop.
Data entry
We shall start with a completely new worksheet so if you have already opened a worksheet and entered text, quit from Excel first of all and start it up again. A blank sheet in a new book should be displayed.
To enter data in a cell, you must first make that cell active. When you open a completely new sheet, the cell that is active is always A1.
You can enter different kinds of data into a cell e.g. text or numbers. Data can be entered anywhere on the worksheet. It is not necessary to start with the first cell. You can leave cells blank if you wish.
There is a distinction between just typing data into a cell and entering data into a cell. Until you enter your data you have not committed it to the worksheet. As you will see, there is more than one way of actually entering the data.
As you type data into a cell, what you type also appears on the Formula Bar. At first it will not be obvious why the Formula Bar is needed, but you will soon see how it is used.
Note: It is possible to switch the Formula Bar on and off. If you cannot see it, choose the View tab in the Options dialog-box and make sure the box labelled Formula Bar is ticked.
Typing data into a cell
Click on the cell A1 to make it active
Type the number 1234 into the active cell and look at the Formula Bar to see the number appear there as you type.
Note that when you are typing data into a cell, it doesn’t matter where the mouse cursor is when you do this. Any data that you type always goes straight into the active cell. Once you have started typing data into a cell, right up until you finally enter the data in the cell, anything you type will be treated as data to be put in the active cell.
Now move the mouse-pointer to cell B1 and click the mouse to make B1 active.
In addition to making B1 active, this action also causes the data in A1 to be entered into the worksheet. Making a second cell active, is one way of causing data in the first cell to be entered.
Note how the number entered into cell A1 has now disappeared from the Formula Bar although it still appears in the cell A1, itself. The data that appears in the Formula Bar is always that in the active cell itself and as B1 does not yet contain data, the Formula Bar is empty.
Click on the cell A1 to make it active again and note how the number 1234 reappears in the Formula Bar.
Editing data in a cell
Suppose you now wish to change the number that you’ve entered into cell A1.
Make sure cell A1 is active
Move the pointer up to the Formula Bar. Its shape changes from a cross to an I-beam. Now click at the end of the number 1234 in the Formula Bar. This lets you edit the number. Add the digit 5, making 12345.
Note how the number shown in the cell A1 changes accordingly.
Click on cell B1. This makes B1 active and also enters the edited data in A1 into the worksheet.
Now type 5432 into cell B1
Press Return: this is another way of entering the data
Another way of editing the data: double click on cell A1
Now the mouse-pointer becomes an I-beam while it is over A1, so that you can edit the number without going up to the Formula Bar
Position the I-beam after the 2 in cell A1 and click
Now press backspace to delete the 2 so that the cell contains 1345
Click on B1
Continue going back and forth between A1 and B1 making each active in turn and editing the numbers till you are quite happy with these procedures.
Remember that data which has already been entered into a cell can only be changed by first making that cell active again.
Entering data with the tick icon
You will have noticed that when you are typing in the Formula Bar, buttons with a tick and a cross appear to the left of the data entry area. Clicking on the is another way of entering data. Try the following.
Click on B2 to make it active
Click in the Formula Bar and type 4321
Note how the number appears in cell B2 itself
Now click on the tick to enter the data into B2
This time B2 remains the active cell after you’ve entered its data.
Cancelling a cell entry
Suppose you start to type data into either the active cell or the Formula Bar and then realise you’ve typed the wrong data entirely. You can cancel the data completely before you enter it, by clicking on the icon in the Formula Bar. The cross icon is known as the cancel box for this reason, although we will refer to it as the cross icon in this workshop.
Try the following.
Click on cell B5 to make it active and then type 1200 into the cell or into the Formula Bar
Now click on the cross icon to cancel the entry.
The number disappears from both the cell and the Formula Bar.
Click on one of the cells that contains some data, and then point the I-beam at the end of the data in the formula bar
Use the backspace key to delete the data
Click the cross, and the data will be restored, because you have cancelled the deletion
You can only use the cross icon before you have committed yourself and entered the data. Once data has been entered into a cell, you have to use other methods to remove it.
Undoing cell entries
If you have just entered data and change your mind and want to undo the entry, the simplest thing is to choose Undo Typing… from the Edit menu. Try the following.
Make cell D1 active, type a number and enter the data by clicking the tick icon.
The number should appear in both cell and Formula Bar.
Now choose Undo Typing … in D1 from the Edit menu or click on the icon in the toolbar.
The number should disappear from both cell and Formula Bar.
The Undo… command in the Edit menu is only available immediately after you have made the entry. But the little arrow beside the “undo” icon in the toolbar will give you a list of recent actions and you can go back some way undoing what you have done.
Moving around the worksheet
What you see on the screen in the window is only a small part of the total worksheet. You can see more of it by using the scroll bars at the bottom and right-hand-side of the window in the same way as you would for any other Windows- or Macintosh-based application.
Note:If no scroll bars are shown, choose the Viewtab from the Options dialog-box and click one or both of the Scroll Barboxes. See the first worksheet for details of the Options dialog-box.
Click cell B1 to make it active.
Use both scroll bars to see other areas of your worksheet, but don’t click on the worksheet itself
Come back to the original area of the worksheet and note that cell B1 is still active.
Looking around the worksheet like this doesn’t change which is the active cell. It just lets you look at areas that are hidden from view.
Making cells active
There are several ways in which you can make cells in different parts of the worksheet active:
use the arrow keys which let you move one cell at a time
move the mouse-pointer to another cell and click with the mouse button
press the Tab key to move the active cell one cell to the right
press the Return key to move the active cell one cell down
select Goto... from the Edit menu (or press the function key F5), enter the cell address in the dialog-box, in the text-box labelled Reference and then click OK
click in the box on the left of the formula bar (it contains the address A1 in the picture, because A1 is the currently active cell), type a new address and press Return.
Try these different techniques now.
Make the cell A2 active by using the arrow keys, and type another number, 2145, say into it.
Move to cell A3 by pressing the Return key and type 2214 into the cell.
Now position the pointer over cell B2 and click with the mouse. Type 2211.
Press the Tab key to make cell C2 active and type 2567.
Click the tick icon to enter this data into C2.
Choose Goto... from the Edit menu and type Z38 in the box labelled Reference: and click OK. You should find that Z38 is now active. Type 5534 into it and click the tick icon.
Click on the box on the left of the Formula Bar, type B8 and press Return
Which of these methods of making cells active you choose, will depend very much on the task in hand. If you are entering columns of data, for example, then pressing the Return key is the most obvious choice, while if you were entering data in rows you would probably move from cell to cell by pressing the Tab key.
Zooming
The Zoom... command from the View menu can be used to increase or reduce the scale of the display. When you choose this command, a dialog-box is displayed giving you a choice of either selecting pre-set magnifications or entering your own in the box marked Custom. Try this. To return to the original magnification, choose 100% in the dialog-box.
Can you see another place where you can change the magnification? Look towards the right-hand end of the toolbar.