Your guide to Excel 2000 by dfsiopmhy6

VIEWS: 3 PAGES: 28

									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:
“display, download and print parts of the Licensed Work for the purpose of
promotion of the Licensed Work, testing the Licensed Work, or for training
Authorised users”.
Any other uses of these screenshots may be copyright infringement.
            Your guide to Excel




                   About this workbook

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
enhance your work.

             Getting Started – how to log on
You can access Excel from any of the computers in the libraries. You will
need your username and password to log on to the network. To find your
username your student ID card should be placed on the card reader at the
Information Desk. Your password will be based on your date of birth and six
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).
     4. Type in your password.
     5. Press return           ..



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



                              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:
                                                      Menu Bar




                                                                                       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
           the online help facility.



                 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.
      5. Using the steps above make all your headings bold.
      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.



   14. Click the                           radio button.

   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.




Click here




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




                                                                             Click here




              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.



                                                       Click here




                                                       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




                                                                  then click here




 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