Introduction to Excel v.4
Microsoft's Excel Support Page
Search FAQs about: Excel 4
Contents
IntroductionSpreadsheets are used for a wide variety of tasks from the simple presentation of tables of numbers to complex simulations of scientific processes and computer based training materials. Excel has the greatest range of mathematical and scientific functions of any spreadsheet on the market, and it is widely used for teaching.
The scope of this document is to introduce you to the Excel user interface, to enable you to enter data, format data, set up simple calculations and produce simple graphs. There are other documents available to enable you to perform more complex formatting operations, use formulae more powerfully and refine your graphs.
The lessons have been designed in such a way that you are advised to complete a lesson and its exercise before proceeding to the next one.
If, after these exercises, you still feel you need some practice in the elementary aspects of Excel before progressing to more advanced topics you are strongly recommended to use the tutorial program that comes with Excel.
Formatting Data in Microsoft Excel
Writing Formulae in Microsoft Excel
Charting in Microsoft Excel
Task 1 - Running Excel
Task 2 - The Excel ScreenInstructions You will learn the parts of the Excel screen by observation and try out some common Excel tasks.
Activity 2.1 Familiarise yourself with the Excel worksheet window. If the worksheet is displayed on part of the screen, you can enlarge the worksheet window by clicking on the Maximise box to the right of the Title Bar.
Activity 2.2 The columns are labelled by letters and the rows
by numbers. At the intersection of a row with a column is a box called a
cell. Cells are referenced by both their column label and their
row number.
Click on the cell D7 using the mouse. A black box appears around the cell.
This is called the cell highlight and it's presence denotes a
selected range. Move the mouse slowly over the black border.
Notice that the cursor changes from a thick white crosshair
when
over the main part of the cell to a white arrow
when it is over the black border, and a black crosshair
when it is over the little black box in the bottom right hand corner of the
cell highlight.
Activity 2.3 Now look at the formula bar. This has three areas. The lefthand area will have the text D7 in it. Excel can only edit the contents of one cell at a time, even when a range is selected. The editable cell is called the active cell. It is always highlighted and it's reference will be displayed in the left-hand area of the formula bar.
The right-hand area of the formula bar will display the contents of the active cell, and in here the contents can be changed. The middle area of the formula bar is used when the cell is being edited to confirm or cancel the changes.
Task 3 - Entering DataInstructions You will start entering data into the spreadsheet using the keyboard.
Comments You are Sales Manager of VINO plc. You have the sales figures for 3 of your 4 sales reps for three quarters, and you wish to record this data.
Activity 3.1 First we must enter the two titles. Click on cell A1. Notice that the lefthand section of the formula bar displays the cell reference A1. Now type:
VINO plc.
Notice that the text appears in the cell and also in the right hand section of the formula bar. Also the middle section of the formula bar changes to display two buttons. These are:
<Cancel> button. Clicking on this cancels the edits in the cell.
<Confirm> button. Clicking on this returns you to the sheet with the
cell edited.
Click on the <Confirm> button to complete the entry. Now click on cell A3 and type:
(Figures in 000's)
When you are entering a title (ie a label) that is wider than the cell, you will not see the whole label in the cell until you confirm the entry. The right-hand area of the formula bar however will show you the entire entry. Click on the <Confirm> button.
Activity 3.2 Now enter the column labels for the table - Sally, Ann and Ray in cells A7, A8 and A9 respectively. When you have typed the text you can confirm the entry by pressing <Enter> instead of clicking the <Confirm> button.
Activity 3.3 Now enter the row labels for the table - Q1, Q2 and Q3 in cells B6, C6 and D6 respectively. When you have typed the text you can confirm the entry by pressing the right cursor <> key. This confirms the entry and moves you on to the next cell in one operation.
Activity 3.4 Now enter the values 40, 57, 52, etc. so that your sheet looks like the one shown below.
Task 4 - Editing DataInstructions You will edit the data in the spreadsheet using the keyboard and mouse.
Comments There has been an error with Ray's sales figures and you wish to put the correct data in the worksheet.
Activity 4.1 For the first quarter, Ray sold [[sterling]]58,000 of goods, not [[sterling]]55,000. Click on the cell B9 and type:
58
The contents of cell B9 are deleted and completely replaced by the new typing. If you had done this by accident then at this point you could click the <Cancel> box to undo the edits. We want to confirm the entry, so click the <Confirm> button or press <Enter>.
Activity 4.2 For the third quarter, Ray sold [[sterling]]71,000 of goods, not [[sterling]]73,000. Click on the cell D9, but this time press the function key <F2>. Look at the formula bar and notice that at the end of the figure there is a flashing I-beam cursor, indicating that the text in the cell is ready for editing.
Press the <Backspace> key once and type 1, then confirm the cell edits.
Activity 4.3 The title is incorrect. It needs to be changed to SALES FOR
VINO plc (1991/2). To do this, click on A1 and then move the cursor to the
Formula Bar. As the cursor goes over the editing section of the formula bar
it's shape changes to an I-beam,
.
Position this just to the left of the word VINO and click the mouse button
once. The flashing cursor appears to the left of the word VINO. Type:
Sales For
The text is inserted in front of the word VINO. Now use the mouse to click at the end of the cell entry and type:
(1993/4)
Confirm the edits in the cell.
Task 5 - File OperationsInstructions You will use the Window menu and various commands from the File menu.
Comments Most worksheets need to be worked on several times. To do this you must be able to save a worksheet, open an existing worksheet and create new worksheets.
Type in a filename . Remember that filenames cannot be longer than 8 alphanumeric characters and you cannot use the following characters * . # / \. It is a good idea if filenames are meaningful and helpful e.g. "ACCTS90", "REPORT1" rather than "FRED". When finished click <OK>. The title appears in the title bar.
Activity 5.2 Now run the File New command. In the dialogue box select Worksheet and click <OK>. A new worksheet appears.
Activity 5.3 You are looking ahead to the next financial year and you wish to create a worksheet in readiness. Enter the title: Sales For Vino plc (1994/5) into cell A1 and confirm.
Activity 5.4 Select the Window menu. The currently open worksheets will be numbered 1, 2, 3 etc at the bottom of the menu. Choose the appropriate numbered option to switch to the worksheet you have just saved.
If you wish to see all your open worksheets on the screen at the same time (for example, for copying data from one to another), then use the Window Arrange All command. Only one worksheet can be active at any one time - the current worksheet has the title bar highlighted. To work on another worksheet viewed on your screen, click on it, or use the Window menu.
Task 6 - Editing a WorksheetInstructions You will add extra data to the worksheet, use the Edit Insert command and move a range of cells.
Comments The sales reps have produced their figures for the last quarter plus the missing figures for the fourth rep. You wish to enter them into the spreadsheet, along with the Year Target figures for each rep.
Q1 44, Q2 59, Q3 38
Activity 6.2 Now enter the figures for the last quarter as follows:
Sally 65, Ann 90, Ray 82, John 70
Activity 6.3 For each of the reps, you would like to enter his/her Year Target figures. These can be entered in column B, with a heading TARGET.
Move the cursor over the label at the top of the B column and click the mouse button once. The entire column should be selected black on white. Note that the cell at the top of the column, B1, is white, indicating that it is the active cell in the highlighted range.
Now go to the Edit menu and select the command Insert. A blank column is inserted and the contents of all the other columns are shifted one column to the right.
Label the column with the title Target in cell B6, and enter the target figures as given below:
Sally 250, Ann 220, Ray 250, John 200
Activity 6.4 Management have come back to you asking for the target figures to be after the Q4 figures. Move the cursor over cell B6, click and hold down the mouse button. With the button still held, move the cursor down until it is over cell B10. The black border highlight extends across the cells as the cursor moves across them. Now release the mouse button. The process of moving the mouse whilst holding the button down is called dragging the mouse.
The cells are black on white with the black border around them. A rectangular group of cells, like this, is called a range, so you have now selected a range of cells.
Move the mouse over the black border until it changes to the white arrow
.
Click and hold the mouse button. Now drag the cursor over to column G. You
will notice that a 'ghost' highlight border follows the cursor. Position this
ghost box over the range G6 to G10, and release the mouse button. The data in
the selected range is moved to the new location.
Activity 6.5 Select column B, and choose Edit Delete to remove the column again.
Task 7 - Formatting DataInstructions You will learn the parts of the Excel screen by observation and try out some common Excel tasks.
Makes the text bold.
Makes the text larger by about 10%.
Now to select the range A1:F1. Hold down the <Shift> key, and click on
the cell F1. The in between A1 and F1 are all selected. The columns A to F
contain the table we are working with, and we want to centre the title across
the entire table. Click on the Center tool
in the toolbar, and the title will centre itself across the columns.
Activity 7.2 Now we need to align the labels Q1, Q2, etc so that they align
with the data below them. The data is aligned right, so select the range B6:F6
and click on the right align button
on the toolbar.
Your spreadsheet will look something like below:
Save your worksheet by clicking on the File Save button
on the toolbar.
Activity 7.3 Select the data range A6 to F10 by either clicking in cell A6
and dragging the mouse down to F10, or else using the <Shift-Click>
procedure. Click on the Autoformat tool
.
This puts a pre-existing set of formats on the table. If you like this, save
the file. If not, go to the Edit menu and select Undo. This command undoes
the last action you performed, allowing you to recover from mistakes and
experiment with options. Note that it will only recover from the last action,
so if you want to reverse an action, do so immediately.
Task 8 - Using FormulaeInstructions You will widen a column using the mouse, and enter formulae from the keyboard.
Comments As Sales Manager, you need to know how well or badly your sales team has done for the financial year 1991/2. You want to establish:
1) the total sales figure for each rep;
2) the actual total sales and the target total sales .
In F6 enter the label Actual Sales. The label will not fit in the
column width, so you must widen the column. Move the cursor to the column
labels. Now move the cursor across the labels: notice that the cursor changes
from a crosshair to a vertical line with arrows
.
Move the cursor in between the labels for the F and G columns so that the
cursor changes form. Click and drag the mouse to the right. A dotted line
appears under the cursor, indicating the new column width. When this looks big
enough release the mouse button. The column adopts the new width.
Activity 8.2 F7 represents Sally's total sales - click on this cell. The formula for Sally's total sales will be the sum of the four quarters ie B7+C7+D7+E7. Click on F7 and type:
=B7+C7+D7+E7
Confirm the entry in the cell. Excel adds up all the contents of the cells B7 to E7 and displays the result in F7. Note that the formula bar still contains the formula, so if you need to edit it you can.
Activity 8.3 An easier way to add up a collection of cells, especially if
they are all together is to select F7 again and then click on the Autosum
button,
.
This pastes in the SUM function and the active dotted box indicates the range
of cells to be summed. Press <Enter> to accept the suggested range. The
result is displayed in F7, but the formula bar will contain the formula
SUM(B7:E7).
Activity 8.4 We need to copy the formula in F7 to F8, F9, F10 so the totals
for the other sales reps are calculated. Select cell F7 so that the cell is
highlighted. Now move the cursor over the little black box in the bottom
righthand corner so that the cursor changes shape to a black crosshair
.
Now click and drag the mouse button so that a ghost highlight covers the range
F7:F10. Release the mouse button and the formula is copied down the range.
Note that the formula changes to take account of the fact that it is in a
different row. The formula in row 7 is =SUM(B7:E7), but the formula in row 8
is =SUM(B8:E8).
Note that the bottom border will have vanished, so you may want to redo your autoformat command.
Activity 8.5 Using the same technique, sum up the reps' totals from F7 to F10, and put the result in F12. Enter an appropriate label in A12. Repeat for the target total sales.
Task 9 - Creating a ChartInstructions You will learn the parts of the Excel screen by observation and try out some common Excel tasks.
Comments A chart is a graphic representation of worksheet data. There are 14 different types of chart - eight 2-dimensional and six 3-dimensional types - including the Bar, Line, Pie, and Scatter types.
Activity 9.1 One way of achieving this objective is to use the input data that was entered initially, i.e. B7 to E10. The column and row labels will also be required to identify the data. The data range for the chart therefore is A6 to E10.
Select this data range with the mouse and click on the Chart Wizard tool,
.
An active dotted box appears around the selected data and the cursor changes
to a narrow black crosshair. Scroll down the sheet, position the crosshair on
cell A13, click and hold down the mouse button and drag the mouse down to cell
G22. This box will be where Excel displays the embedded chart. You will then
get the first dialogue box of the ChartWizard for the first step.
Using ChartWizard
The ChartWizard breaks down the operation of creating a chart in to 5 steps. If you need more information click on the Help button.
Step 1 ChartWizard is checking that you have the correct data for the chart. Ensure that this displays $A$6:$E$10. Click on the <Next> button.
Step 2 Which chart type do you require? Click on the Column window. This can be easily changed if necessary. Click on the <Next> button.
Step 3 Which chart format? These are variations on the chart type you selected in Step 2. Choose a format. Click on the <Next> button.
Step 4 Now Chartwizard displays your chart. Has it interpreted your requirements correctly? For the exercise, check that the Rows option is selected under Data Series in. Click on the <Next> button.
Step 5 Annotating the chart. Do you want a legend (key), and titles?
For the exercise, ensure that the Legend option is selected Yes.
Click in the Chart Title box and type VINO Sales Figures (1991/2).
Click in the Category (X) option, and type the text Quarterly Sales.
Click on <OK>.
Activity 9.2 The chart is placed in the worksheet. Click anywhere in the worksheet to deselect the chart. Point your mouse over the chart so that the cursor changes to become a white arrow. Drag your mouse - your chart will move with it.
Task 10 - PrintingInstructions You will use the Page Setup, Print Preview and Print commands from the File menu.
Comments Having gone to all this work to create your spreadsheet, you will want to take away a printed copy.
Activity 10.2 Once the right printer has been selected, you will probably want to check how your page is set up. Select the Page Setup... command from the File menu. You should get the dialogue box as shown below:
Click on the Centre Horizontally and Centre Vertically check boxes. Then click <OK>.
Activity 10.3 We will now preview the spreadsheet on screen. Previewing your spreadsheet, before committing it to the printer saves you time, effort and money so it is recommended. As a precaution you are advised to save your work beforehand.
From the File menu choose the Print Preview command. The screen will display a preview of the printout. To examine the worksheet more closely, click on the Zoom button. When you are satisfied with the Preview, click on the <Print> button. The spreadsheet window will reappear with a dialogue box checking that you are sending the print to the right place, how much of the sheet you want printed and how many copies. Click <OK> and a small dialog box appears giving the progress of the printing. Wait until this disappears before continuing with your work.
Task 11 - Finishing ExcelInstructions You will use the Exit command from the File menu.
Comments You should always quit any computer program when you have finished your session. Never switch off the computer when Excel is still running unless absolutely necessary as this will corrupt your spreadsheet files. Also never leave a computer whilst you are still logged on to it as others may use your Novell account and could potentially damage your files.
Activity 11.2 Excel will ask you if you want to save your document before it lets you quit, so the following dialogue box will appear:
Click either Yes or No depending on whether you want the changes saved. Once you have done that Excel will quit. If you click on <Cancel> you will be returned to your unsaved spreadsheet to continue working.
About This Document
Author: University Computing Service, University of Leeds
Date: August 1993
Acknowledgments:
This document was prepared from the Introduction to Excel 4.O - A Self
Teaching Guide produced by Carol Everett, Computer Services, Anglia
University.
Thanks are given for the permission to use their material.
