Docstoc

Activity Tracking Spreadsheet - Download as DOC

Document Sample
Activity Tracking Spreadsheet - Download as DOC Powered By Docstoc
					SeniorNet
Introduction to
Spreadsheets
Workshop
A tutorial to demonstrate
the basic features and uses of
the Works Spreadsheet
                                                            Spreadsheet Workshop




SeniorNet
Introduction to
Spreadsheet Workshop



In this Workshop you will work with the Works spreadsheet. You will learn how
to open a spreadsheet, select information, edit and format values and add formulas.
You also will learn how to create a chart.

Objectives
   Become familiar with some of the basic features of a spreadsheet
   Use the mouse to select cells and ranges in a spreadsheet
   Create formulas to perform calculations
   Use the toolbar to format numbers for currency
   Change the data within a cell
   Change the appearance of a spreadsheet
   Create and modify a chart
   Save a spreadsheet that has been edited
   Print Preview a spreadsheet




SeniorNet                               1                                   01/23/07
Spreadsheet Workshop
                                                                                                             Spreadsheet Workshop



Contents                                                                                                                                   Page
Objectives ........................................................................................................................................ 1
Introduction to spreadsheets ............................................................................................................ 3
Start Windows ................................................................................................................................. 3
Start the Works Spreadsheet ............................................................................................................ 3
Close Help ....................................................................................................................................... 3
The spreadsheet window ................................................................................................................. 4
Open an existing spreadsheet .......................................................................................................... 4
Parts of a spreadsheet ...................................................................................................................... 6
Select cells ....................................................................................................................................... 6
Edit a spreadsheet ............................................................................................................................ 7
Create a formula .............................................................................................................................. 8
View another formula .................................................................................................................... 11
Insert a row and enter data............................................................................................................. 12
Enter data in a column and see changing values ........................................................................... 12
Open another file ........................................................................................................................... 13
Format a cell .................................................................................................................................. 14
Save the Stocks spreadsheet .......................................................................................................... 14
Print preview a spreadsheet ........................................................................................................... 15
Create a chart ................................................................................................................................. 16
Label the chart ............................................................................................................................... 18
Print preview a chart...................................................................................................................... 20
Close the chart windows................................................................................................................ 20
Close the Spreadsheet program ..................................................................................................... 20
Activity—Work with another spreadsheet .................................................................................... 20
Review—Select cells and ranges................................................................................................... 21
Insert a row .................................................................................................................................... 22
Insert and format a column ............................................................................................................ 24
Copy formulas ............................................................................................................................... 25
Save your work with a new name.................................................................................................. 26
Print Preview ................................................................................................................................. 26
Calculate Year-to-Date expenses .................................................................................................. 26
Expand a column ........................................................................................................................... 26
Prepare your data to create a chart ................................................................................................ 27
Use Paste Special........................................................................................................................... 27
Save the changes ........................................................................................................................... 28
Create a new chart ......................................................................................................................... 28
Label the new chart ....................................................................................................................... 29
Close the new chart window.......................................................................................................... 29
Save ............................................................................................................................................... 30
Close the Works Spreadsheet ........................................................................................................ 30
Now you can… .............................................................................................................................. 30




SeniorNet                                                                2                                                                01/23/07
Spreadsheet Workshop
                                                                   Spreadsheet Workshop



Introduction to spreadsheets
Spreadsheet programs allow you to enter number values and to create formulas
that automatically adjust the calculation when you change the numbers. The
spreadsheet window has gridlines like an accountant’s ledger and it acts like a
calculator. Spreadsheets can be used for budgets, insurance records, taxes, tracking
investments and other paperwork that requires numbers and calculations.

Start Windows
Turn on your computer and log on, if necessary.

Start the Works Spreadsheet
1      Click Start and place the pointer on All Programs to open the All
       Programs menu.

2      Move the pointer into the All Programs menu and place the pointer on the
       folder Microsoft Works. The Microsoft Works folder shows a folder icon
       and an arrow on the right; Works Task Launcher shows the Works program
       icon and has no arrow.
       The menu for the Microsoft Works folder opens.

3      Click Microsoft Works Spreadsheet (see Figure 5.1).

       Note: If there are a lot of programs listed in the Programs menu, the sub-
       menu may open to the left, as shown in Figure 5.1, rather than to the right.




                Figure 5.1 Microsoft Works folder with Spreadsheet selected

Close Help
When you first open Works Spreadsheet, the Help window may be open to the
right of the spreadsheet window.

SeniorNet                                   3                                    01/23/07
Spreadsheet Workshop
                                                                Spreadsheet Workshop




1      Click the X on the top right of the Help window to close it. Do not click the
       X on the title bar of the spreadsheet window.

The spreadsheet window
The Microsoft Works Spreadsheet program opens to a blank spreadsheet entitled
“Unsaved Spreadsheet” (see Figure 5.2).




                     Figure 5.2 Microsoft Works Spreadsheet window

You see the title bar, menu bar, toolbar and a bar that is new to you, the formula
bar. The formula bar shows the contents of a cell, the rectangular box into which
you enter data when the cell is selected.

Open an existing spreadsheet
1      Click the Open button on the toolbar.

SeniorNet                                 4                                   01/23/07
Spreadsheet Workshop
                                                              Spreadsheet Workshop



      The Open dialog box opens.

2     Click the arrow on the Look in list box and move the pointer to click the
      location where the Spreadsheet Workshop folder is stored.

      Note: You may have noticed that when a program is first started the Open
      dialog box Look in list box usually shows the My Documents folder.

3     Double-click the Spreadsheet Workshop folder to open it.
      The spreadsheet files contained in the folder appear in a list.

4     Double-click the icon next to the spreadsheet file Stocks.
      A message box may appear indicating that the file is a Read-only
      document. In order to save any changes you make you will have to rename
      and save a new version of the file (see Figure 5.3). You will save the file
      later.




                           Figure 5.3 Read-only message box

5     Click OK if the message box appears.
      The student practice file Stocks opens (see Figure 5.4).
      This spreadsheet shows how you can track the changing quantity and price
      per share of shares of stock you own.




SeniorNet                                5                                  01/23/07
Spreadsheet Workshop
                                                              Spreadsheet Workshop




                              Figure 5.4 Stocks spreadsheet

       Note: The number in B7 (the last number in column B) might show as
       $8,199 or $8,200, depending upon how the spreadsheet rounds off the
       number.

Parts of a spreadsheet
A spreadsheet is a grid that is composed of rows (labeled with numbers) and
columns (labeled with letters). The place where a column and row intersect is
called a cell. Cells are named for the column and row number, for example cell D3
is the rectangular box located in column D and row 3. Cells can hold numbers, text
or formulas.

Select cells
You select a cell or cells in order to make additions or changes to a spreadsheet.

1      Move the mouse pointer into the spreadsheet. The pointer appears as a large
       outlined plus sign.

2      Click in cell D3 to select it (see Figure 5.4).
SeniorNet                                  6                                  01/23/07
Spreadsheet Workshop
                                                                 Spreadsheet Workshop



       A dark rectangular border appears around the cell and the contents of the
       cell, 60.00, appears in the formula bar below the toolbar.

3      Move the pointer to cell C3, press and hold down the mouse button and
       drag the pointer down to cell C7. Release the mouse button.
       The range of cells C3 through C7 (written as C3:C7) is highlighted (see
       Figure 5.5).




                          Figure 5.5 Cell range C3:C7 selected

4      Click on the letter A at the top of column A to select the whole column.

5      Click on the number 3 on the left of row 3 to select the whole row.

Edit a spreadsheet
You will correct a mistake that was typed in one of the cells. You can either make
the change right in the cell or you can edit the contents of the cell in the formula
bar. You will select the cell and then make the change to it.

1      Click in cell C4.
       A border around the cell highlights it. The number 40 in the cell appears on

SeniorNet                                 7                                    01/23/07
Spreadsheet Workshop
                                                                   Spreadsheet Workshop



       the formula bar.

2      Type the number 60 in the highlighted cell.
       The number is replaced in the cell and also in the formula bar.

3      Click in cell D7.
       A border highlights the cell. The number in the cell, 27.33, appears in the
       formula bar.

4      Move the pointer into the formula bar and click to the right of 33 to place
       the insertion point there. Press the Backspace key twice to delete the 33
       and type 66 in its place.

5      Press the Enter key on the keyboard or click the Enter button, the green
       checkmark on the formula bar (see Figure 5.6), to accept the change and
       enter the numbers that are in the formula bar into cell D7.




                           Figure 5.6 Edit using the formula bar

6      Click in cell C6. Move the pointer into the formula bar and click after 100.
       Press the Backspace key once to delete one of the 0s and type a 1 so that
       the number becomes 101.

7      Click the Cancel button, a red X, on the formula bar (see Figure 5.6) to
       cancel the change to 101.
       The number 100 is restored to the cell and the formula bar.

Create a formula
Spreadsheets are excellent calculators. The instructions you type in the
spreadsheet to define calculations, such as adding or multiplying numbers, are
called formulas. You click in the cell in which you want to store the result of the
calculation and type an equal sign (=) in that cell to indicate that you are starting to
type a formula. After the = sign, type the formula. There are lots of formulas in the
Works Spreadsheet program that you can use to make calculations, such as the
SeniorNet                                   8                                    01/23/07
Spreadsheet Workshop
                                                          Spreadsheet Workshop



sum of a column of numbers, the product of multiplying two numbers or more
complicated formulas.

One of the formulas you can use is for adding a column or row of numbers. You
will now add the numbers in column B.

1     Click in cell B9.
      This is where you will store the formula.

2     Type the equal sign (=) and the word sum and an open parenthesis
                 .
      You have typed the beginning of a formula for addition. What you typed
      appears in both cell B9 and the formula bar.

3     Move the mouse pointer to cell B3 and press and hold down the mouse
      button and drag the pointer down to cell B7. Release the mouse button.
      The range of cells B3 through B7 that you wish to sum is selected and
      appears as B3:B7 in the formula.




SeniorNet                              9                                  01/23/07
Spreadsheet Workshop
                                                                      Spreadsheet Workshop




                       Figure 5.7 Formula for sum of range of cells

4     Click after the formula in cell B9 and type a closed parenthesis           to end the
      formula (see Figure 5.7).

5     Press the Enter key or click the green checkmark (the Enter button) on the
      formula bar.

      Note: Or to automatically calculate a total, you could click in cell B9 and
      click the AutoSum (Σ) button on the toolbar and press Enter. This is a way
      to create a total without having to type the formula.

6     Click in cell B9 to select it again if it is not selected.
      Note that the formula you typed appears in the formula bar and the sum
      appears in cell B9 (see Figure 5.8).

      Note: The total at the bottom of column B is not in the same format as the
      rest of the numbers in the column B. You will fix it later in this lesson.


SeniorNet                                  10                                       01/23/07
Spreadsheet Workshop
                                                                      Spreadsheet Workshop




                           Figure 5.8 Sum formula and result

View another formula
There are formulas in the cells B3 through B7 that tell the spreadsheet to multiply
the number in a cell in column C by the number in the corresponding cell in
column D. The Formula is the Price per share multiplied by the Number of shares
which equals the total Cost of the stock.

1      Click in cell B3.
       The multiplication formula that is in cell B3 appears in the formula bar (see
       Figure 5.9). The contents of cell C3 are multiplied by the contents of cell
       D3. The asterisk (*) stands for multiply.



                       Figure 5.9 Multiplication formula in cell B3

The symbols for the basic mathematical operators used in formulas are:




SeniorNet                                  11                                       01/23/07
Spreadsheet Workshop
                                                             Spreadsheet Workshop



                            Addition    +      Subtraction    -

                       Multiplication   *         Division    /


Insert a row and enter data
You may want to add an additional item in your spreadsheet, such as a new stock
you may have purchased.

1     Click the number 4 at the beginning of row 4 to select the whole row for the
      stock Illusion.

2     Click the Insert menu and click Insert Row.
      A new row is inserted above the selected row.

3     Type data for the new row as shown below.

      The cell in which you are to type is indicated in the column below on the
      left with the column heading in parentheses next to it so you can keep track
      of where you are.

      You will enter a formula into cell B4. The total in the cell won’t appear
      until you enter the information into cells C4 and D4. Press the Tab key to
      advance to the next cell in a row.

             A4   (Stock)                   Nonesuch Tab
             B4   (Cost of Stock)           =C4*D4   Tab
             C4   (Number of shares)        150 Tab
             D4   (Price per share)         50   Tab


Enter data in a column and see changing values
You will change some of the information in column C so you can see how to enter
data into a column. As you change the information you also will see how column
B is affected since the values in column B depend on the values in columns C and
D. Each cell in column B is equal to the value in the corresponding cell C times
(*) the value in the corresponding cell D. The numbers in column B change as you
change the numbers in column C.


SeniorNet                               12                                 01/23/07
Spreadsheet Workshop
                                                              Spreadsheet Workshop



1      In column C, type the following values to replace the numbers in the
       selected cells, one at a time. Click in cell C4 first because the number of
       shares in cell C3 is already set at 50. Press the Enter key after each entry to
       move to the next cell in a column.

       The cell in which you are to type is listed below on the left with the name
       of the stock in column A in parentheses next to it to help you keep your
       place.

       As you type each number, see the change in value in the adjacent cell in
       column B.

       Note: If you don’t have time to type all of these numbers, just type one and
       go to the section Open another file.


              C3     (FlybyNite)                  50     Enter
              C4      (Nonesuch)                  60     Enter
              C5     (Illusion)                   70     Enter
              C6     (Boiler Works)               80     Enter
              C7     (FAROut)                     90     Enter
              C8     (OnTheLam)                   100    Enter

       Note: If you did not type all of the new numbers above, you can open a
       student file in which the numbers are typed for you. If you typed the
       numbers yourself, skip to the Format a cell section.

Open another file
If you are going to use the file that was created for you, rather than typing the
information, you will first need to close the file Stocks and open StocksA.

1      Click the File menu and click Close. When you see a message box asking if
       you want to save changes, click No.

2      Follow the steps near the beginning of this lesson to open an existing
       spreadsheet. Open the file called StocksA which shows your spreadsheet
       with the changed numbers in columns B and C.

SeniorNet                                 13                                   01/23/07
Spreadsheet Workshop
                                                              Spreadsheet Workshop




Format a cell
The value in cell B10 and the values in column D are not formatted with dollar
signs. You will format them now.

1     Click in cell B10 and click the Currency button ($) on the right end of the
      toolbar.
      The number in cell B10 now has a dollar sign and a decimal point with
      zeros showing cents.

2     Click the D at the top of column D to select the entire column.

3     Click the Currency button ($).
      All of the numbers in cells D3 through D8 are formatted as currency. The
      heading, Price per share, is unaffected (see Figure 5.10).




                          Figure 5.10 Completed spreadsheet

Save the Stocks spreadsheet
Save your spreadsheet after you make changes.

1     Click the Save button on the toolbar.

2     A message may appear advising that, since it is a Read-only document, you
SeniorNet                                14                                 01/23/07
Spreadsheet Workshop
                                                              Spreadsheet Workshop



      cannot save the file without renaming it using the Save As dialog box (see
      Figure 5.11).




                             Figure 5.11 Save As message

3     Click OK, if the message appears.
      The Save As dialog box opens.

4     Type My stocks to replace the current name or the blank space in the
      File name box.

5     Click the Save button on the dialog box.
      The file, with the changes, is saved with a new name in the same location
      where the file was originally opened.

Print preview a spreadsheet
Preview your spreadsheet to see how it would look in print.

1     Click the File menu and click Print Preview to view the spreadsheet as it
      will print.
      The print preview window has a toolbar on the top that differs somewhat
      from the Print Preview in the Word program (see Figure 5.12).




SeniorNet                                15                                 01/23/07
Spreadsheet Workshop
                                                                 Spreadsheet Workshop




                         Figure 5.12 Spreadsheet Print Preview

2      Click the Magnifying glass button with the plus (+) sign to Zoom In. Click
       the Magnifying glass button with the minus (-) sign to Zoom Out or just
       place the pointer on the page until it takes the shape of a magnifying glass.
       Each click of the mouse will either enlarge or reduce the image size.

3      Click the Close button (labeled Close) on the Print Preview toolbar.
       You return to the spreadsheet document.

Create a chart
You can create a visual representation of parts of your spreadsheet.

1      Select the cells in the range from A3 through B8 (A3:B8): Point to cell A3
       and press and hold down the mouse button and drag over into the B column
       and then down to cell B8. Release the mouse button.
       You now have a block of highlighted cells as shown in Figure 5.13.

2      Click the New Chart button at the far right of the toolbar (see Figure 5.13).




SeniorNet                                 16                                   01/23/07
Spreadsheet Workshop
                                                                Spreadsheet Workshop




            Figure 5.13 Spreadsheet window with New Chart button on toolbar

      A New Chart dialog box appears.

3     In the Chart type section, click the pie chart option and type My stock
      chart in the Chart title box (see Figure 5.14).




SeniorNet                                 17                                  01/23/07
Spreadsheet Workshop
                                                             Spreadsheet Workshop




                          Figure 5.14 New Chart dialog box

4     Click OK.
      My stock chart appears showing the stock names for each of your stocks.

Label the chart
You now want to show both the names and the percentages for the values of each
stock.

1     Click the View menu and click Data Labels.
      The Pie Chart Data Labels dialog box appears.

2     Click the arrow on the 2nd label list box and click Percentages (see Figure
      5.15).

      Note: Don’t worry that the percentages may not show in the preview
      window. They will show after you click OK.




SeniorNet                               18                                 01/23/07
Spreadsheet Workshop
                                                                  Spreadsheet Workshop




                       Figure 5.15 Pie Chart Data Labels dialog box

3     Click the OK button.
      The My Stock pie chart with labels appears (see Figure 5.16).




                            Figure 5.16 Pie Chart with labels


SeniorNet                                  19                                   01/23/07
Spreadsheet Workshop
                                                              Spreadsheet Workshop



Print preview a chart
1      Click the Print Preview button on the toolbar.
       The Print Preview window opens so you can see how the chart will print.

Close the chart windows
1      Click the Close button on the toolbar to close the Print Preview window.

2      Click the File menu on the Chart window and click Close to close the Chart
       window.
       You are returned to the spreadsheet window.

Close the Spreadsheet program
1      Click the File menu and click Exit to close the Spreadsheet program.
       A Save Changes message appears.

2      Click Yes to save any changes you made to the spreadsheet since you last
       saved it.

If you have time, complete the following Activity to practice what you have
learned or do it for lab or home practice. If you don’t have time now, go to the
Close the Works Spreadsheet section at the end of the lesson.

Activity—Work with another spreadsheet
In this activity you will open the household budget student file. You will format
the numbers for US currency, create a Sum formula for the subtotal, change the
alignment of field labels, edit entries, calculate a year-to-date column and create a
pie chart showing how you’ve spent your money. You also will practice saving
and print previewing the worksheet.

If it isn’t already open, start up the Works Spreadsheet program.

1      Click the Start button, point to All Programs, point to the Microsoft
       Works folder and click Microsoft Works Spreadsheet.
       A blank spreadsheet opens.

2      Click the Open button on the toolbar.
       The Open dialog box opens.

SeniorNet                                 20                                   01/23/07
Spreadsheet Workshop
                                                                 Spreadsheet Workshop




3     Click the arrow on the Look in list box and move the pointer to click on
      My Documents, Desktop, 3½ Floppy or the CD/DVD drive depending on
      where the student files folder is and double-click the Spreadsheet
      Workshop folder to open it.

4     Double-click the icon next to the file Budget.
      A message box may appear indicating that the file is Read-only.

5     Click OK if the Read-only message appears.
      The student practice file Budget opens.

Review—Select cells and ranges
1     Move the pointer within the spreadsheet and see that the pointer appears as
      a large outlined plus sign. The spreadsheet is set up as a grid. The columns
      are lettered and the rows are numbered. A cell is the intersection of a
      column and row. Cell D6 is the cell in column D and row 6.

      Examine the components of the spreadsheet window labeled in Figure 5.17.




                        Figure 5.17 Parts of the Budget window



SeniorNet                                21                                    01/23/07
Spreadsheet Workshop
                                                             Spreadsheet Workshop



2     Click to select each of the following cells in turn: A4, E6, D9 and A1.
      The selected cell is outlined with a border. The formula bar, located below
      the toolbar, shows the cell location and displays its contents. Notice what is
      displayed on the formula bar each time you click a cell.

3     Select each of the following ranges: C4:C9, D6:E6 and D4:E9.
      To select a range of cells (a group of contiguous cells that form a
      rectangle), point to the cell at one corner of the range and press and hold
      down the mouse button and drag the pointer to include the other cells in the
      range. Then release the mouse button.
      Notice that to the left of the formula bar the range of cells that are selected
      is showing, and within the formula box the content or formula in the first
      cell selected is showing.

4     Click cell D12 and look at the formula =SUM(D4:D9). This translates as
      “add all the numbers in the range of cells from D4 to D9 and place the
      result in D12.”

Insert a row
You forgot to include entertainment in your budget, so you will do it now.

1     Click the row number 6 to select the entire row.
      The Insurance row is highlighted.

2     Click the Insert menu and click Insert Row (see Figure 5.18).




SeniorNet                                22                                   01/23/07
Spreadsheet Workshop
                                                             Spreadsheet Workshop




                          Figure 5.18 Insert row in Budget

      Row 6 is now a highlighted empty row, and “Insurance” is now row 7.

3     Click cell A6 and type Entertainment. Press the Tab key.
      Now you have a new category of expenses.

4     Type the following information in the Entertainment row. Press the Tab
      key after each entry:

      B6     600
      C6     =B6/12 (= the result of B6 divided by 12 entered in C6)
      D6     0
      E6     40
      Press Enter

5     Click cell D13 to see the formula in the formula bar. The formula was in
      cell D12, but it moved down to make room for the new row you added for
      the entertainment category.
      The formula has been corrected automatically to accommodate the new line
      item, as have all of the other formulas in the spreadsheet. This automatic
SeniorNet                               23                                 01/23/07
Spreadsheet Workshop
                                                            Spreadsheet Workshop



      adjustment is one of the reasons why spreadsheets are so popular.

Insert and format a column
Time has passed and you want to record how much you spent in March. To do
that, you must add another column to your budget. March will be put in column F.

1     Since all the numeric entries in the new column will be currency, format
      column F with US currency. Click the F at the top of the column to select
      the entire column and click the Currency ($) button on the toolbar.
      All numeric values in the column (numbers or formulas) will be formatted
      with a $ sign.

2     Click cell F2 and type the heading Mar. and press the Enter key. Type
      Actual in cell F3 and press Enter.
      The heading of the new column is now set up like the other columns.

3     Click in cell F4 to begin entering the following numbers in column F. Press
      the Enter key after each entry to move to the next cell.

      Note: Pressing the Tab key moves one cell to the right and pressing the
      Enter key moves one cell down.

      The name of the category is listed in parentheses to help you keep your
      place.

      F4     (Housing)                   560    Enter
      F5     (Food)                      280    Enter
      F6     (Entertainment)             50     Enter
      F7     (Insurance)                 90     Enter
      F8     (Transportation)            100    Enter
      F9     (Medical)                   50     Enter
      F10    (Contributions)             100    Enter

      Note: If you make a mistake click in the cell and retype the entry.

4     To calculate the total for This Month Actual, click cell F13 and click the
      AutoSum (Σ) button (see Figure 5.19) and press Enter. This is a way to

SeniorNet                               24                                  01/23/07
Spreadsheet Workshop
                                                            Spreadsheet Workshop



      create a total without having to type the formula.
      The total of March’s actual expenses appears in the This Month Actual
      Total row.




                            Figure 5.19 Result of AutoSum

Copy formulas
The formulas for cells F14, F15, F17 and F18 must also be extended for the March
entries. They can all be done at once.

1     Select the range E14:E18.
      Notice the small black square called the handle in the lower right corner of
      the range.

2     Move your pointer over the handle until you see + FILL.

3     Press and hold down the mouse button and drag the handle toward the right
      into the F column. Release the mouse button.
      The formulas in column E have been copied into column F with the
      appropriate references. You can check the formulas by clicking in a cell and
      looking at the formula that appears on the formula bar.
SeniorNet                                25                                 01/23/07
Spreadsheet Workshop
                                                           Spreadsheet Workshop




Save your work with a new name
1     Click the File menu and click Save As.
      The Save As dialog box opens.

2     Click in the File name box and type My budget.

3     Click the Save button.
      The changes you made to the document are saved as a document with a
      new name.

Print Preview
1     Click the Print Preview button on the toolbar to preview your spreadsheet.

2     Click the Close button on the toolbar to close the Print Preview window.

Calculate Year-to-Date expenses
1     Click cell G3 and type YTD(year-to-date). Press the Enter key to advance
      to cell G4.

2     In cell G4, type the formula =sum(D4:F4)and press the Enter key.

3     Click cell G4 and move the pointer to the lower right corner until the
      handle shows + FILL Press and hold down the mouse button as you drag
      the handle on the lower right corner of cell G4 down through cell G10 and
      release the mouse button to copy the formula down the column.

4     Click the G at the top of the column to select the column and click the
      Currency ($) button on the toolbar.

Expand a column
You can make a column width expand to accommodate the largest number.

1     Double-click the column heading G and the width of the cells in the
      column will expand to accommodate the largest number in the column if
      there are numbers that are too big to be displayed. You can do this in any
      column where this occurs.

SeniorNet                               26                                  01/23/07
Spreadsheet Workshop
                                                              Spreadsheet Workshop




Prepare your data to create a chart
You will create a chart view of your year-to-date budget expenses by category.
You want to be able to associate each YTD value in the chart with the name of the
category so you have to put the category name and the YTD value next to each
other in the chart.

1     Select cells A4 through A10 (A4:A10). These are the category names.


2     Click the Copy          button on the toolbar.
      You are going to copy the contents of the cells to another area in your
      spreadsheet so that you can place the category names next to the YTD
      values for the categories.


3     Click cell A20. Click the Paste      button.
      The category names are pasted in cells A20 through A26.

4     Select cells G4 through G10, (G4:G10). These are the YTD values for the
      categories.

5     Click the Copy button on the toolbar.
      You are going to place the copied contents of the cells next to the category
      names.

Use Paste Special
You can use the Paste Special command to paste values in a cell when you do not
want also to paste the formula.

1     Click cell B20. Click the Edit menu and click Paste Special.
      A Paste Special dialog box appears.
      Since these cells have formulas in them you have to use the Paste Special
      command to paste just the values and not the formulas.

2     Click the radio button next to Insert the value in the cell if it is not already
      selected. Click OK.
      The YTD values for the budget are pasted in cell B20 and below, next to

SeniorNet                                27                                    01/23/07
Spreadsheet Workshop
                                                             Spreadsheet Workshop



       the category names (see Figure 5.20).




                             Figure 5.20 Year-to-date data

Save the changes
1      Click the Save button on the toolbar.
       Notice that, since you already saved the file with the name My Budget, the
       new file is not protected as a Read-only file and you see no warning
       message.

Create a new chart
Sometimes it is easier to understand your overall financial situation by looking at a
chart.

1      Select the range A20:B26.


SeniorNet                                 28                                  01/23/07
Spreadsheet Workshop
                                                                   Spreadsheet Workshop



2     Click the New Chart button on the right of the toolbar.
      The New Chart dialog box appears.

3     Click the Pie chart type and type YTD Budget in the Chart title box.
      The chart you selected appears in the Preview box showing the category
      labels for the selected cells in your database. Click OK.
      The YTD Budget Chart window appears.

Label the new chart
1     Click the View menu and click Data Labels.
      The Pie Chart Data Labels dialog box appears.

2     Click the arrow on the 2nd label list box and click Data values (B20:B26).

3     Click the OK button.
      The YTD Budget pie chart appears with Category labels and YTD
      Amounts (see Figure 5.21).




                       Figure 5.21 YTD Budget chart with amounts

Close the new chart window
1     Click the File menu on the Chart window and click Close to close the Chart
      window.
SeniorNet                                 29                                     01/23/07
Spreadsheet Workshop
                                                           Spreadsheet Workshop




Save
1      Click the Save button on the toolbar to save your work.

Close the Works Spreadsheet
1      Click the File menu and click Exit to close the spreadsheet program.

Now you can…
   Select cell(s) in a spreadsheet
   Insert a row
   Format for currency
   Edit a cell
   Create a subtotal
   Copy (Fill) a formula
   Save a spreadsheet
   Print Preview a spreadsheet
   Create and Print Preview a chart

In this workshop you learned to open and modify a spreadsheet and a chart and
you saw how a spreadsheet could be useful to you in calculating and recalculating
numbers.




SeniorNet                               30                                    01/23/07
Spreadsheet Workshop
                                                                Spreadsheet Workshop



Addendum:


The following files are required for this course:
Budget
Stocks
StocksA


These files should be put in the “Spreadsheet Workshop” Folder and stored in the
computer at the location the students will be using to open and save their files.




SeniorNet                                  31                                       01/23/07
Spreadsheet Workshop

				
DOCUMENT INFO
Description: Activity Tracking Spreadsheet document sample