Your guide to Excel 2000 by dfsiopmhy6

VIEWS: 3 PAGES: 28

• pg 1
```									Text  University of Bolton.

The screen shots used in this workbook are from copyrighted licensed works
and the copyright for them is most likely owned by the publishers of the
content. It is believed that the use of a limited number of web-resolution
screenshots for training purposes qualifies under educational purposes to:
promotion of the Licensed Work, testing the Licensed Work, or for training
Authorised users”.
Any other uses of these screenshots may be copyright infringement.

This workbook is designed to introduce you to the spreadsheet package
Microsoft Excel. There are several versions of Excel in use today. In this
workbook you will use Excel 2003. From now on we will just refer to it as
Excel.

The workbook will show you how to enter information into a worksheet, use
formulas to perform calculations and create charts.

Each section has exercises which will give you the chance to practise what
you have learned. Work through these pages at your own speed and repeat
the exercises as necessary.
Contents

What is Excel?                                2
Getting Started – how to log on               2
Getting into Excel                            2
The Excel Screen                              3
How do I enter data into the worksheet?       4
How do I save my data?                        6
How do I format cell contents?                7
Cell formatting – Continued                   8
How do I use formulas?                        8
How do I copy formulas?                       11
How do I print my worksheet?                  14
What is a Chart?                              15
The Chart Wizard                              16
How do I format an axis?                      20
How do I change the appearance of my chart?   21
How do I change the chart type?               23
How do I print my chart?                      24
And finally...                                25

 University of Bolton 2008          1
What is Excel?
Excel is a spreadsheet program, allowing you to carry out calculations, make
predictions, and analyse trends. It also allows you to produce visual
representations of this information in the forms of graphs and use graphics to

Getting Started – how to log on
You can access Excel from any of the computers in the libraries. You will
randomly generated digits, but you can change this to something you’ll be
able to remember more easily.

Exercise 1
Log on to the network using the instructions below.

1. Press Control, Alt and Delete simultaneously to log on.
A Windows Dialog Box will appear.
2. Enter your username in the first space (in lower case letters).
3. Press tab         or click in the password box (left hand click).
5. Press return           ..

Top Tip
See the Introduction to IT workbook for information on how to change

Getting into Excel
Exercise 2

1. Click on the Start button in the bottom left hand corner of the
screen.
2. Move the arrow up to All Programs.
3. Then move it across to Microsoft Office.
4. Finally move it across to Microsoft Office Excel 2003 and click.
The sequence to follow is demonstrated on the next page.

 University of Bolton 2008                2
The Excel Screen
When Excel has loaded, the screen display should look similar to the one
below:

Help button
Tool bars

Scroll bars

 University of Bolton 2008          3
Top Tip
To close the Microsoft Office online help, click on         on the Getting
Started bar. If you need help with Excel at any time, click on to restore

How do I enter data into the worksheet?
When you load Excel you will be presented with a blank worksheet. The
worksheet is comprised of many cells, each of which can be activated by
clicking on it. This is called the Active Cell. You can move around within
these using the mouse or arrow keys on the keyboard.

Each cell has an address, comprising a Column Letter and a Row Number
e.g. A1. As you click in the various cells, this address appears on the left of
the Formula Bar.
Formula Bar         Column Letter

Active Cell

Row Number

Sheet Tabs

A spreadsheet can extend into 3 dimensions through the use of multiple
worksheets. These are accessed via the Sheet tabs. The entire spreadsheet
(including all worksheets) is called a “workbook”.

Data is entered into cells as either a label or a value.

Labels are usually textual information i.e. headings of columns, dates. Their
alignment in the cell is left justified.

Values are numerical data, on which you may want to perform mathematical
operations. Their alignment in the cell is right justified.

 University of Bolton 2008                4
Exercise 3

1. Click in cell A1 and enter the title School of Witchcraft and
Wizardry End of Year Results.
2. Before we input the column titles we need to make column A wider.
To do this click in column A, then click Format on the Menu Bar, and
then Column and Width.
The Column Width box will be displayed as below. The number in the
column width box is highlighted, ready for you to overwrite it.

3. Type 17, then click on          .
4. Click in cell A3 and enter the title Students Name.
5. Click in cells B3, C3 and D3 and enter the titles shown below.
6. Now fill in the rest of the information given below.

Your display should now look the same as the one above.

Top Tip
A quicker way to widen the column is to move your mouse over the line
to the right of the column letter. You will see the double arrow. Click
and hold down the left hand mouse button, and move to widen the
column to the width required.

 University of Bolton 2008              5
How do I save my data?
When you have finished entering your data, you should save your work.
From the Menu bar, click on File, then Save As. This will open up the Save
As window as shown below.

Exercise 4

1. Click in the box labelled Save In and select the drive you wish to save
on.
There are various drives available such as A: for floppy disks. It is good
practice to save on both hard storage media, such as floppy disks, CDs and
pendrives, and in My Documents, which is your network drive.
2. Click in the box labelled File name. Delete the default file name that
Excel chooses and replace it with your own file name. We want to
save our data using another name so type in Results.

3. Now click on               .

Top Tip
Once you have saved your document and given it a name, all you need
to do to save fresh changes is to click on on the toolbar. Remember
to save your work at regular intervals.

 University of Bolton 2008              6
How do I format cell contents?
Cell contents in Excel can be formatted in a similar way to using a word
processor. The important difference is that the formatting applies to a whole
cell, not an individual character.

There are many types of formatting that can be applied to the cell. In the
exercise below we will look at the appearance of the cell. Examples of this
are bold, underline, font and colour.

Exercise 5
Using the data file Results we entered earlier, we will do some simple bold
and underline formatting.

1. Enter two headings we will use later – Student average in cell E3 and
Module average in cell A12.
2. Click in cell A1, then click Format on the Tool bar and then Cells.
3. Click on the Font tab if you are not already on it.
4. Click on bold in the Font style box, then click        .
Your title should now be emboldened.
6. To underline your title click in cell A1, then click Format on the Tool
bar, and then Cells.
7. Click in the Underline box and choose Single, then click              .
Your display should look like the one below.
8. Click        to save your work.

 University of Bolton 2008                7
Cell formatting – Continued
An alternative way to change the formatting of cells is to use the Tool bar.

Click on      to embolden.

Click on      to underline.

This is usually the easiest way to alter the appearance of your worksheet, but
it relies on the toolbar being available.

Top Tip
To make the formatting Tool bar visible if it is not displayed, click on
View on the Menu bar, then click Toolbars and Formatting.

Cell formatting is also used for many other purposes. e.g. to tell Excel what
type of data is stored in the cell. i.e. numeric, text, date, currency.

How do I use formulas?
A formula is used when the value of a cell needs to be calculated. For
example, formulae may be used to calculate totals or averages, produce
percentages, or find the minimum and maximum values in ranges.

Parts of a formula

=sign indicates the beginning of           the average function   cell references to tell Excel
a formula                                                         which cells to average

 University of Bolton 2008            8
There are 2 items that occur in formulae and need explaining:

1. A range

A range is a contiguous block of cells and is denoted in Excel by the cell
reference of one corner of the block, a colon and then the cell reference of the
diagonally opposite corner of the block.

e.g.     B5:D5                means the cells      B5,C5,D5.
A1:C2                means the cells      A1,A2,B1,B2,C1 and C2.

2. A function

A function is a predefined calculation that is given a name. There are many
inbuilt functions in Excel. e.g. average, sum, count.

In the example below, the function being used is Average.

AVERAGE(B5:D5) performs the calculation           (B5+C5+D5)/3.

Exercise 6
Using the data we have just typed in we will calculate the total (or sum) of
Harry Potter’s marks.

This will add up B5, C5 and D5.

1. Click in cell F5, to calculate the sum. i.e. add up the marks for Harry
Potter.
2. Click on the formula bar and type =sum(b5:d5) then press return on
the keyboard.
Your display should now look like the one below:
3. Click      to save your work.

 University of Bolton 2008               9
Top Tip
In this exercise we typed the range B5:D5 in on the keyboard. An easier
way to do this is to select the range using the mouse.

Exercise 7
Delete the contents of a cell.

1. Click in cell F5 and press Delete on the keyboard.
The number in F5 will disappear.

Exercise 8

1. Click in the cell E5, to calculate the average mark for Harry Potter.
2. Click on the formula bar and type =average(b5:d5) then press return
on the keyboard.
Your display should now look like the one below:
3. Click         to save your work.

 University of Bolton 2008                10
Exercise 9

1. Click in cell B12, to calculate the Module average for Herbology.
2. Click on the formula bar and type =average(B5:B10) then press return
on the keyboard.
Your display should now look like the one below:
3. Click           to save your work.

How do I copy formulas?
Now we would like to calculate an average mark for the rest of the students.
These don’t have to be done individually. We can do it in one step by copying
the formula.

Exercise 10

1. Click in cell E5, which is the formula we want to copy.
2. Press Ctrl C on the keyboard. The cell E5 will be highlighted with a
dotted line.
3. Click in cell E6, then, using your mouse, select the range E6 to E10.
4. Now press Ctrl V on the keyboard.
The results will appear in the cells.
Your display should now look like the one on the next page.
5. Click          to save your work.

 University of Bolton 2008                 11
When you copy a formula to another cell, any cell references are
automatically updated so that they refer to the cell in the same relative
position. These are called relative cell references. So in the example we
have just done (Exercise 10), if you click in cell E5 the formula is
=average(B5:D5), whereas if you click in cell E6 the formula is
=average(B6:D6).
In some circumstances, a cell reference is written so that the value of the cell
when it is copied does not change e.g. \$B\$5. This is called an absolute cell
reference.

Top Tip
If you would like more information on absolute cell references there are
some good books in the library. Just search for Microsoft Excel in the
catalogue.

Exercise 11

1. Follow steps 1 to 5 in Exercise 10 to find the Potions and Charms
module average.
Your display should now look like the one below:
2. Click        to save your work.

 University of Bolton 2008               12
Exercise 12
Have a look at the average results we have just calculated. You can see
there are rather a lot of decimal places.

To display these as whole numbers we can use cell formatting again.

1. Click in cell E5, then using your mouse, select the Student average
range E5 to E10.
2. Click on Format on the Menu bar, then Cells.
3. Then click Number in the category box.
4. Change the number of Decimal places to 0, and then click        .
Your display should now look like the one below:

Exercise 13

1. Using the same steps as Exercise 12, adjust the Module average
results to display whole numbers.
Your display should now look like the one below:
2. Click        to save your work.

 University of Bolton 2008             13
How do I print my worksheet?
Before printing, you should check how your worksheet looks. Print Preview
shows you exactly how your document will look when it’s printed.

Exercise 14
1. Click on File, then Print Preview.
Your display should look like the one below:

2. Click on              to make your text larger on screen.

3. Click on              .
The Print dialog box will appear as shown below:
4. Click            to print your worksheet.

Make sure this
is the printer
for the room
you are in

then click
here

 University of Bolton 2008                 14
What is a Chart?

A Chart is a graphic representation of worksheet data. Types of Chart that
can be produced in Excel include Column, Line and Pie. Charts can also be
produced with a 3-D visual effect.

120
100
80
60
40
20
0
Harry Potter
Potter

Malfoy
Weasley

Weasley

Weasley
Hermione

Draco
Harry

RESULTS
Granger

Ginny

Fred
Ron

Hermione
Granger
Ron Weasley

Draco Malfoy

Ginny Weasley

RESULTS                     Harry PotterWeasley
Fred

Hermione
Granger
Ron Weasley

Draco Malfoy

Ginny Weasley

Fred Weasley
120
100
80
60
40
20
Malfoy
Potter

Hermione
Harry

Weasley

Weasley

Weasley
Draco
Granger

Ginny

Fred
Ron

 University of Bolton 2008                           15
The Chart Wizard
In Excel we use the Chart Wizard to produce Charts.

The Chart Wizard is a series of dialog boxes that simplifies creating a Chart.
It guides you through the process step by step. You select your data, a chart
type, and decide whether to add items such as titles and a legend. A sample
of the chart you are creating is displayed so that you can make changes
before you finish working in the Chart Wizard.

Chart Title

Y-axis Title                                               End of Year Results                                      Legend

120

100

80

Herbology
Marks

60                                                                                                      Potions
Charms
40

20

0
Harry Potter    Hermione Granger   Ron Weasley   Draco Malfoy     Ginny Weasley   Fred Weasley

Students

Data series                                           X-axis Title

The Legend describes the Data series. In the above example there are
three data series Herbology, Potions and Charms.

Exercise 15
Use the Chart Wizard to create a Column Chart of the End of Year Results.

1. Click in cell A5 and using your mouse block the range A5 to D10, as
shown on the next page.

2. Click               on the Toolbar, to activate the Chart Wizard.
Your display should now look like the one on the next page.

 University of Bolton 2008                                   16
3. Click Column in the Chart type box and click           to continue.
Your display should now look like the one below:

4. Make sure the              radio button is selected, as our data is in
columns.
5. Click the Series tab.
Your display should look like the one on the next page.

 University of Bolton 2008           17
6. Click Series1 and then click in the Name box and type Herbology.
7. Click Series 2 and then click in the Name box and type Potions.
8. Click Series 3 and then click in the Name box and type Charms.
9. Click           .
Your display should look like the one below.

10. Click in the Chart title box and type End of Year Results.
11. Click in the Category (X) axis box and type Student.
12. Click in the Value (Y) axis box and type Marks.
13. Click                .
Your display should look like the one on the next page.

 University of Bolton 2008          18
In this dialog box, you determine where the chart will appear. Your choices
are on a new sheet in this workbook, or as an object on one of the existing
sheets.

15. Click                          to complete the Chart Wizard.

The new sheet will be displayed with the finished chart on it. Notice that the
new sheet created during this process is named Chart1.

End of Year Results

120

100

80

Herbology
Marks

60                                                                                           Potions
Charms

40

20

0
Harry Potter   Hermione   Ron Weasley    Draco Malfoy   Ginny Weasley   Fred Weasley
Granger
Student

 University of Bolton 2008                         19
How do I format an axis?
Exercise 16

1. Place your cursor on the x-axis, as shown below and right click the
mouse.

Right click
here

2.                       will appear. Select Format Axis….
3. Select Font and change the Font style to bold, then click               .

4. Use the same procedure to change the font style to bold on the y-
axis.

The labels on both the x and y axes will now be in bold font.

Top Tip
You can format any area of the chart by right clicking your mouse
button in that area.

 University of Bolton 2008              20
How do I change the appearance of my
chart?
Applying a pattern to a data series can make it appear more distinct. This can
be useful if you want to print the chart in black and white.

Exercise 17

1. Place your cursor over the data series as shown below, and right click
the mouse.

2. Select Format Data Series from the list.
The dialog box shown below will appear.

 University of Bolton 2008          21
3. Click on Fill Effects, and then on Pattern.
4. Click on the pattern you would like, and then on            and
again.

then here

Your chart should look similar to the one below.

 University of Bolton 2008            22
How do I change the chart type?
Exercise 18

1. Place your cursor on to the chart area and right click the mouse.
2. Select Chart Type from the list.

then click
Right click                                                                         here
here

The Chart Type dialog box, as shown below, will open.

Select this

then this

then click
here

 University of Bolton 2008             23
4. Select Line, then Chart sub-type, then click          .
Your chart will look similar to the one below.

How do I print my chart?
Exercise 19

1. Click on File then Print.
The Print dialog box will appear as shown below.
2. Make sure that the printer for the room you are working in is selected,
then click            to print your chart.

Make sure this is
the name of the
room you are in

 University of Bolton 2008               24
And finally...
Well done! You have made it to the end of this workbook. You should now
feel more confident using Excel. You will have a better idea of what it is and
what it can be used for. You have created a worksheet and used formulas to
perform calculations. You have also used the information in your worksheet
to create a chart.

This workbook is designed only as an introduction of Excel. Now that you
have got the basics, it is just a matter of practising and developing your skills.

Good luck!

 University of Bolton 2008            25

```
To top