Shortcut to Excel

Document Sample
Shortcut to Excel Powered By Docstoc
					                                         Shortcut to Excel
Part 1—Using Excel Program

1.     Click on Start and go to Programs. Select Microsoft Excel.

2.     Sheet 1 of Book 1 will open.

3.       Cells: In Cell A1, type Candy is Dandy. In Cell A2 type Colors. Continuing in Row A
type Blue, Brown, Green, Orange, Red, Yellow, Total. (You can use the enter key to go down.)
Go to Cell B2 and type Bag 1. Use your tab key to go to C2 and type Bag 2, D2 type Bag 3, E2
type Bag 4, and F2 type Average. If you want to adjust (make smaller or larger) the cell width
you can double click on the line between the column letters (such as F and G) at the top. You
can do the same thing for the length of the cells by double clicking between the row numbers on
the left. Try it.

Looks like this:

Candy is Dandy
Colors    Bag 1         Bag 2        Bag 3        Bag 4        Average
Blue
Brown
Green
Orange
Red
Yellow
Total

4.     Fill: Fill in the numbers as shown below in each cell
Candy is Dandy
Colors    Bag 1         Bag 2        Bag 3        Bag 4        Average
Blue               14           25           15            2
Brown              10           11           12           13
Green               6            5            9           24
Orange             12           17            6           21
Red                22           13            3           16
Yellow             16           14            0           15
Total

5.       Change the font. On your spreadsheet document, click on the rectangle in the upper left
hand corner, to the left of A and above 1. This should highlight (make a different color) your
whole document. Go to the top of the screen where you see a box that says Arial. This is the
name of the font. Click on the down arrow to the right of Arial and you will see the fonts you
can choose. I am choosing Times New Roman. Select your font and notice how the font in the
text of your document changes. I would also like to change the size of the font, which is next to
where you changed the name. Change the 10 to 12. Let’s center everything, while you still have
it highlighted. Next to the place you changed from 10 to 12, you see B (for Bold) I ( for Italics)
and U (for Underline), and then some lines. This shows your alignment and you want center.


       2003-2004                                                                           Page 1
  Center is the middle one. Click it and your text should be centered. Your document should look
  like this:

Candy is Dandy
    Colors    Bag 1       Bag 2      Bag 3      Bag 4    Average
     Blue      14          25         15          2
   Brown       10          11         12         13
    Green       6           5          9         24
   Orange      12          17          6         21
     Red       22          13          3         16
   Yellow      16          14          0         15
    Total


  6.      Centering: Let’s center the Candy is Dandy heading. Select cells A1 to F1 by mouse
  clicking first in A1 and holding down the left mouse button as you slide the mouse to the left,
  highlighting these 6 cells. Then go to the top, right next to where you centered your text. To the
  right you see something that looks like an a in a box with arrows. Click that to merge the cells
  you have selected. Your document should look like:

                          Candy is Dandy
   Colors      Bag 1      Bag 2     Bag 3       Bag 4    Average
    Blue        14         25        15           2
   Brown        10         11        12          13
   Green         6          5         9          24
   Orange       12         17         6          21
    Red         22         13         3          16
   Yellow       16         14         0          15
    Total

  7.      Formulas: Next we want to total each row—we want the total for bag 1, bag 2, etc.
  Click on cell B9, which is directly below the number 16. Go above the blue bar that says Book 1
  and you see =. Click on the =. A dialogue box comes up that says Formula result =. Click on
  Cell B3 then type +, then click on cell B4, type +, then click on cell B5 type +, then click on cell
  B6 type +, then click on cell B7 type +, then click on cell B8 (don’t put an extra + at the end).
  Next to Formula Result = 80 click OK and you will see 80 in the cell next to Total. What you
  have done is add 14+10+6+12+22+16. The advantage to this instead of just typing these
  numbers, which you could do, is that if you change any number in column B, the total will
  change. You can try it by changing 14 to 10 and watch what happens to the total of 80. Enter a
  formula in the same way for Bag 2, Bag 3, and Bag 4. A short cut for typing these formulas is to
  enter the first one as you have done above and hit Enter. Then, click on cell B9 again. A handle
  appears in the bottom right corner of the cell. Grab the handle on the bottom right of the cell and
  drag it across or down through all the cells in which you want to place the formula. Try both
  ways. It should look like this:


         2003-2004                                                                             Page 2
                       Candy is Dandy
 Colors      Bag 1     Bag 2     Bag 3       Bag 4    Average
  Blue        14        25        15           2
 Brown        10        11        12          13
 Green         6         5         9          24
 Orange       12        17         6          21
  Red         22        13         3          16
 Yellow       16        14         0          15
  Total       80        85        45          91

8.      Formulas continued: Next we want to enter a formula to average the number of M&Ms
in each color. To do this, click in the cell below the word Average (Cell F3). Click the = sign
above the blue bar that says Book 1. See the word SUM? Click the arrow to the right of it.
Click on AVERAGE. The program determines that you probably want to average that top row
and so it has picked it for you. You can check by the numbers it has in the {}, which are {14, 25,
15, 2}. Click OK and get the average of 14. If you do not get the range of cells you want,
simply, click on average, then move the gray box that appears. Click your mouse in the box and
hold the mouse down while you move the box. Then, while holding the shift key, click on the
cells you want to average, which in this case are B4, C4, D4, E4. Do this for the other colors (or
use the short cut above in number 7) and your chart should look like this:

                       Candy is Dandy
 Colors      Bag 1     Bag 2     Bag 3       Bag 4    Average
  Blue        14        25        15           2        14
 Brown        10        11        12          13       11.5
 Green         6         5         9          24        11
 Orange       12        17         6          21        14
  Red         22        13         3          16       13.5
 Yellow       16        14         0          15       11.25
  Total       80        85        45          91

9.       Format cells: Some of these averages are actually decimals. We would like to format
the cells to show one decimal place in the average column. Click on the F above the column and
note that it highlights the whole F column. Now right click on that column and go to Format
Cells . . . You have a variety of ways to format cells, but choose Number. Then put 1 for
decimal places and click OK. Note the change below:




       2003-2004                                                                           Page 3
                         Candy is Dandy
 Colors      Bag 1       Bag 2     Bag 3       Bag 4     Average
  Blue        14          25        15           2        14.0
 Brown        10          11        12          13        11.5
 Green         6           5         9          24        11.0
 Orange       12          17         6          21        14.0
  Red         22          13         3          16        13.5
 Yellow       16          14         0          15        11.3
  Total       80          85        45          91

10.     Format cells: Let’s change the background color shading for the Average row.
Highlight cells F3 to F8. Right click in the highlighted cells. Go to Format Cells. Select the tab
Patterns. You can pick a color or go to Pattern and choose a pattern from those. I am choosing a
light gray. When you have finished the Average column, do the same for the Total row. Your
chart should look like this now:

 Colors      Bag 1       Bag 2      Bag 3      Bag 4     Average
  Blue        14          25         15          2        14.0
 Brown        10          11         12         13        11.5
 Green         6           5          9         24        11.0
 Orange       12          17          6         21        14.0
  Red         22          13          3         16        13.5
 Yellow       16          14          0         15        11.3
  Total       80          85         45         91


11. Format cells: Let’s change the color of the words Candy is Dandy. Highlight these by
dragging your mouse across them or by selecting the cell they are in. Do a right click and select
Format Cells. Choose the tab at the top of the dialog box that says Font. Make changes to the
font selected, size, color, etc. When you are finished, click OK.

12.     If you want to print this file with lines, you must highlight the cells you are going to print
and tell it you want them to be outlined with the lines printed. To highlight the cells, start with
cell A1—click in it. Then, while holding down the left mouse key, drag your mouse down and
right, highlighting all the cells in the chart you want to print. Go up to the toolbars at the top and
find the idon that is a square with a horizontal line and a vertical line in it. Click the small
downward pointing triangle to the right of that box and select the option you want. If the lines
are solid in the little picture, they will print. If they are dotted, they will not. Choose the one
with all the lines solid. Then print your chart.

13.     You need to save this file. On the menu bar, go to File, and Save or Save As. When the
Save dialog box comes up, you need to name the file under file name near the bottom of the box.
You also need to look at the top of the box beside Save in: This tells you what place you want to
save this. You might choose the Desktop, My Documents, or some other file. Use the


       2003-2004                                                                               Page 4
dropdown arrow on the right of the box to choose where to save it. Always check to see where
you are saving the document.

14.     Make a chart: Microsoft has a nice chart making function. Highlight cells B3 to B8. Go
to the menu bar and select Insert, Chart. Click on the various chart types and then click on
“Press and hold to view sample”. Pick the first one which is entitled Column. Click Next>.
You can choose rows or columns, but you want columns for this. Click Next>. Then you can
choose a chart title, titles for the x and y axis, etc. Click on the tab for Legend. You may want
to uncheck Show legend. I selected the title for the x axis as Bags and for the y axis as Colors.
I don’t suggest you do anything else with the other tabs at this time. You can go back and
experiment later. Click Next>. On the last dialog box select As object in Sheet 1. Click
Finish. Now you have a chart, which if you click in it, you can move it where you want on the
page!

To print just the graph, click on the graph, to print the chart with the graph, click outside the
graph.

                             Candy is Dandy

            25
            20
   Colors




            15
            10
            5
            0
                 1       2         3          4       5         6
                                       Bags



You are finished with this part!

Next we will insert an Excel worksheet into a Microsoft Word document. It’s easy!




            2003-2004                                                                          Page 5
Part 2—Using Excel in Microsoft Word

1.     Open Microsoft Word on your computer. Type at the top: My Classroom Project
Budget. Be sure it is centered! Enter two times to space down. Be sure you are no longer
centered.

2.      Insert a spreadsheet: Go to the menu bar at the top and click on Insert. On the menu you
will see Object . . . It is at the bottom of the list, so you may have to scroll down. From the
dialog box, select Microsoft Excel Worksheet (be sure it is worksheet and not something else).
Click Ok. After a few seconds an Excel worksheet will appear in your document.

Note: When you click outside the spreadsheet, you go back to being able to type in the word
processed part of your document. When you double click inside the spreadsheet it takes a couple
of seconds to “jump” back to the Excel format.

3.      Description: This example project will be to develop a classroom budget for a garden
project. You will be given $100 to spend on the project for your class. As each student brings in
materials for the garden, you will pay them back from the $100. We will keep track of the date,
the student the description of the item, the cost of the item and a running total of how much you
have left in the $100.

4.     Complete the Excel worksheet: Type the following in the given cells:

Cell Location    Information to be Entered in Cell
A1               Date
B1               Student
C1               Description
D1               Cost
E1               Total
E2               $100.00
A3               January 2, 2003
B3               Mary Sue
C3               Potting Soil
D3               $2.50

You may want to refer to the completed page attached.

5.      Format cells: Format Column E to be currency to two decimal places. To do this, click
on E at the top of the row. The entire row should be highlighted. Do a right click on the mouse
and select Format Cells. Click on the tab Number. On the left side, select Currency. Then
select $ from the list of symbols and make sure it says 2 decimal places. If it does not, select 2.
Click OK.

Format Column D in the same way.




       2003-2004                                                                             Page 6
Look at the way Column A is showing the date. Let’s format that in a different date format.
Click on A at the top of the row. The entire row should be highlighted. Do a right click on the
mouse and select Format Cells. Click on the tab Number. On the left side, select Date this
time. On the right you will see various date formats. Select one of the dates by clicking on it. I
am going to select the format: March 14, 1998.

Let’s change the font and the size of the font for the whole spreadsheet. Click in the square at
the upper left hand corner, where 1 is below it and A is next to it. This should highlight the
entire spreadsheet. Do a right click on the mouse and select Format Cells. Click on the tab
Font. Choose a font from the list on the left and choose a size from the list on the right. Notice
the other choices you could make. I am choosing Times New Roman and size 12. Click OK.

Let’s center everything in row 1 and make the text bold. This time click on the number 1 and the
entire row should be highlighted. This time go to the top of your document and click on the B
(for Bold) and then the center lines to the right of the U.

Notice that some of the columns are not wide enough. You can make them wider in one of two
ways. The first way is that you can put your pointer between the letters at the top (between A
and B, for example) and your pointer turns into a different symbol (a vertical line with an arrow
pointing left and right). When it does that, hold down the left mouse key and drag the line as
narrow or wide as you want. The second way (which I like), is to put your pointer between the
letters at the top and when you pointer turns into the different symbol, double click and left
mouse key. That makes the column the exact width to fit everything. Use one of these methods
for each column.

You can do the same thing to make your rows a different height, although that is not needed on
this document.

6.      Insert a formula: We want the spreadsheet to keep a running total under the $100.00,
column E. To do this, click in cell E3. Go above the ruler at the top left and you see =. Click on
the =. A dialogue box comes up that says Formula result =. You want to take the information in
cell E2 ($100.00) and subtract the information in cell D3 ($2.50). So, after you have the = next
to the = in the gray area (this is called the formula bar), click on cell E2 then the minus sign on
the key pad (-) then on cell D3. Then click Enter. You should have $97.50 in cell E3. If you
click on cell E3, up at the top in the formula bar you should see =E2-D3

7.     Insert a formula continued: Now we want to enter an amount for another person. John
has brought tomato plants on January 5, 2003, at a cost of $5.00. Enter January 5, 2003, John,
tomato plants and $5.00 in the proper places on the spreadsheet. You may have to adjust the size
of your columns again. (See 5 above.)

To enter the formula under $97.50 to subtract the $5.00, you can follow the steps in number 6
above, or you can put your pointer on $97.50 (Cell E3), copy that and paste it in cell E4. You
can also put your pointer on $97.50 (Cell E3), and grab the handle—the small square on the
lower right side of the selected cell, and while holding down the left mouse key, pull the
rectangle down to the next or several cells. What this does is make a reference to what it



       2003-2004                                                                            Page 7
“thinks” you want, which is to do the running total. If you click in cell E3, you see =E2-D3. If
you click in cell E4, you see =E3-D4

8.       Enter the following information in the chart and make the formulas in column E subtract
it for you:

January 7, 2003        David           Seeds        $0.75
January 10, 2003       Matthew         Watering Can $6.00
January 12, 2003       Alisa           Fertilizer   $4.25

Hopefully, your total on the line for Alisa is the same as mine—$81.50.

9.      Printing: Print your document. You will see that there are no lines in the spreadsheet.
To put lines, click inside the spreadsheet. Then select all of the cells you want to print with lines.
In our example it will be from A1 to E7. You can just put your pointer in A1, hold down the left
mouse button, and drag the pointer to E7. Then, when you let go, all of those cells should be
highlighted.

Go up to the top where you see a box with dotted lines and a solid line on the bottom. It is on the
upper right hand side, but not all the way to the right. Click on the small down triangle to the
right of this box and you will see a lot of different ways the computer may draw lines—top only,
none, outline, etc. You want the one that will draw lines everywhere, which should be on the
bottom row, the second from the left. Click on it. You should see darker lines on your
spreadsheet. Now print and see if you get these lines.

10.     Saving: Go to the menu bar, click on File, Save as . . . A dialog box comes up. In the
Save In: box at the top click the down arrow and select Desktop. At the bottom of the dialog
box, click the name you want for this file, such as Nancy garden file. Then click Save. If it
comes up with a dialog box about the changing the extension, make the name of your file Nancy
garden file.doc.

11.     Graphics: Let’s put a graphic at the bottom of the chart. Click at the bottom of the chart,
without being in Excel format, and then click Enter. You should be below the chart. Click on
Enter two or three times. Go to the top of the computer screen to the menu bar. Click on
Insert, Picture, Clip Art. A box of pictures comes up. At the top where it says Type one or
more words type garden and then Enter. Click on the picture you want. A rectangle with four
symbols comes up. You want the top symbol which looks like a circle with an arrow into a piece
of paper. Click on that. Then close the Clip Art window by clicking on the X at the upper right.
You should see your picture. Click on your picture. Then go to the lower left side where you
see the word Draw. If you don’t see that, go to the top under View, Toolbars, and click on
Drawing. That should put the drawing tool bar on your screen and you can click on Draw. Go
to Text Wrapping and choose any of the top four—square, tight, behind text, in front of text.
Now you can move that picture around on the page—the little squares are now clear in the
center. If you want to resize the picture (make it bigger or smaller), click on the picture, then
click on one of the arrows in the corners of the picture and while holding down your left mouse
key, pull it diagonally to make it smaller or larger.



       2003-2004                                                                               Page 8
12.     Saving Part II: Now let’s save to a disk. Get a floppy disk and insert it in the disk drive.
Go to the menu bar, click on File, Save as . . . A dialog box comes up. In the Save In: box at
the top click the down arrow and select 3 ½ floppy (A:). At the bottom of the dialog box, click
the name you want for this file, such as Nancy garden file. Then click Save. If it comes up
with a dialog box about the changing the extension, make the name of your file Nancy garden
file.doc. You have saved to the floppy.

13.   Print preview: To see how your document will look on the page, go to File, Print
Preview. Try different buttons at the top to see how you can modify the view. Then click the
word Close. Do not click the X. That will close your whole document!

You have finished this project. However, there are a few additional things you need to meet the
proficiencies this year.

                                  My Classroom Project Budget

        Date           Student     Description    Cost     Total
                                                          $100.00
   January 2, 2003   Mary Sue     Potting Soil    $2.50    $97.50
   January 5, 2003   John         Tomato Plants   $5.00    $92.50
   January 7, 2003   David        Seeds           $0.75    $91.75
  January 10, 2003   Matthew      Watering Can    $6.00    $85.75
  January 12, 2003   Alisa        Fetilizer       $4.25    $81.50




       2003-2004                                                                              Page 9
                                  Additional Activities

1.      Open any three documents. Keep all three of them open. Next, look up in the upper right
hand corner. You see a _ , some boxes, and an X. The X closes the document. The other two
allow you to go between more than one open window. Click on the _. Look at the bottom of
your computer desktop and you will see this and the other windows. Go between the windows
by clicking on them. This is helpful if you are working on more than one document, or if you are
cutting and pasting between documents.

2.      Open a Word document (Document 1). Type some text into the document (Document
2). Open a new Word document. Go to Document 1, highlight some text and copy it. You can
do this in several ways. One is to highlight the text by dragging you pointer over it while
holding down the left mouse key. Then go to Edit, Copy. Then go to Document 2 and paste
the text there. You can do this by going to Edit, Paste.




       2003-2004                                                                        Page 10