IT Services University of St Andrews
Introduction to Microsoft Excel (Office97)
Candlemas 2000
Introduction
Section 1 Starting and finishing Excel
Section 2 Entering data
Section 3 Working with data
Section 4 Laying out your data
Section 5 Simple calculations
Section 6 Charts
Section 7 Printing from Excel
Section 8 Importing and exporting data
Section 9 Simple statistical operations
Section 10 Spreadsheets and Databases
Section 11 Linked worksheets
Section 12 Summarising with a pivot table
Excel Workshop1
Introduction
This workbook is an introduction to Excel version8 (Office97). If you have not used Excel before you should start at the beginning. More experienced users might like to start off with section5. Sections 1 to6 constitute the basic course; later sections deal with some more advanced topics.
If your own copy of Excel is earlier than version 8 you will find that some of the commands are to be found in different menus, but there are no facilities described in the first six sections which are not available in versions 4 or 5. Copies of parallel worksheets for use with earlier versions are available from the IT Services Training Advisor (phone ext 2790).
At various points in the exercises you will be asked to open sample files. The instructor will give you these on a floppy disk.
Macintosh users
These worksheets assume that you have some experience of using a computer and of using Microsoft Windows, the operating system on the PCs. If you are a Macintosh user you may take a little while to adjust to the Microsoft Windows environment, but help is available, so ask if you have difficulties.
The first thing you will notice is that the mouse has two buttons. Unless otherwise stated in the notes, always use the left mouse button.
The disk drives
The PCs in the classrooms have a 3½ inch floppy drive (the A: drive). You also have access to your “home directory” on your Sun account. This is known as the H: drive. In the classrooms you cannot save files on the PC’s hard disk.
After the workshop
You should try to practise the techniques you have learnt as soon after the workshop as you can. Don’t be afraid to experiment. This is often the best way of learning new features. There is an on-line Help system which you can access via the Help menu, or by clicking the ? button in the top right corner of most dialog-boxes. If this doesn’t help you with your queries you should be prepared to consult the Manual. Try out some of the features that haven’t been covered in these worksheets.
What is Excel?
Excel is a spreadsheet with significant charting, statistical and database facilities. In this workshop you will be looking mainly at its spreadsheet capabilities although the later worksheets will cover the more advanced features.A spreadsheet is a powerful application for handling data, mostly numerical data. It provides a method by which data can be analysed and used in calculations.
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
Double-click on the Excel shortcut, or choose Excel from the Start menu
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 page2.
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 page3, 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.