Excel 2003 Online Training by okj18402

VIEWS: 11 PAGES: 24

More Info
									Microsoft Excel for CPSC 101
    Related course objectives: Develop specific computer skills and competencies, including spreadsh
    Mr. John Lamertina

    Specific Spreadsheet Skills
    Getting Started: Calculations



    Formatting


    Formulas



    Copy: Relative & Absolute References



    Get Stock Quotes with Data Import

    Charting

    Lists, Sorting, and Subtotals
lop specific computer skills and competencies, including spreadsheet skills


          Excel Help Topics
          Enter a formula
          Use cell references
          Enter data in worksheet cells
          Training > Excel 2003 > Create your first workbook
          Display numbers as dates or times
          Format numbers as text
          Training > Excel > Making time count
          About formulas
          Simplify formulas by using functions
          Move or copy a formula
          Training > Excel 2003 > Enter formulas
          About cell and range references
          Switch between relative, absolute, and mixed references
          Move or copy rows and columns
          Training > Excel 2003 > Copy a formula instead of creating a new one
          Import or connect to data
          Training > Excel 2003 > Import external data into Excel
          Create a chart
          Training > Excel 2003 > Charts I: How to create a chart
          Sort a range
          Insert subtotals in a list of data in a worksheet
          Training > Excel 2003 > Lists I: How to use lists in Excel 2003
Format > Cells… Number tab

       The Entry:      9.292006

       The Interpretation:
       Number            9.292006
       Date               1/9/1900
       Accounting        $    9.29
       Text             9.292006


Format > Cells… Alignment tab
       10     19       27       39       56        77       91       108
       17     26       41       55       63        80       92       109
       28     33       45       59       69        87       98       121



What value results from copying the formula in the colored cell to the cell marked with "►" ?
=A1                    10            ►
                                                       ►

=A$1                   10
                                               ►
                                                                 ►

=$A1                   10

                                                        ►

=$A$1                  10                                                  ►
                                                                 ►
cell marked with "►" ?




            ►




                             ►
            ►




                         ►
Sample calculations are illustrated in this Monthly Expense Example

Annual Salary (gross)                            32000
Estimated Total Income Tax Rate                    25%
Net Annual Salary                     $         24,000

Starting Balance:                                    0
Monthly Net Pay Check                 $      2,000.00
New Balance                           $      2,000.00

                                      October
Monthly Expenses
   Rent                                            750
   Car payment                                     400
   Gasoline                                        125
   Entertainment                                    99
   Savings                                         250
   Student Loan                                    350
Total Expenses                                    1974

Ending Balance                        $         26.00
ly Expense Example




        $        26.00      $       86.00
        $     2,000.00      $    2,000.00
        $     2,026.00      $    2,086.00

       November             December

                      750               750
                      400               400
                       40               100
                      150               150
                      250                50
                      350               350
                     1940              1800

        $         86.00     $      286.00
Basic Chart Types

  1)   Pie Chart
       Most effective way to display percentages of a whole (proportional relationships)

  2)   Column (Bar) Chart
       To show actual values (rather than percentages) in comparing categories of data

  3)   Line Chart
       To show changes over time.

  4)   XY (Scatter)
       Shows the relationship between two sets of data as (x,y) coordinate pairs.
       Illustrates how the x parameter affects the y parameter.
       Example 1: Business. x = price per unit; y = total units sold
       Example 2: Scientific. x = pressure; y = temperature

Charting Tips
       Message:      Use charts to deliver your particular message, and state that message in the Chart Title.
       Simple:       Insure that your audience will understand your message. Keep the chart and message simple


Sample Data and Charts

                     East            West          North     South
       Total Sales             890          535         1099       713


                                     Total Sales


                                                                                1200
                                                                                1000

                                                                  East             800
                                                                  West             600
                                                                  North            400
                                                                  South            200
                                                                                    0
                                                                                           East   West




                     1st Qtr         2nd Qtr   3rd Qtr   4th Qtr
       Sales                   890         535      1099       713
                                                                            1200
                                                                            1000
                                                                             800
                                                                             600
                                                                             400
                                                                             200
                                                                               0
0
    1st Qtr   2nd Qtr
e in the Chart Title.
hart and message simple




                Total Sales




                                      Total Sales




         West       North     South




                 Sales




                                              Sales
2nd Qtr   3rd Qtr   4th Qtr
Title   FirstName   LastName
Mrs     George      Washington
Mr      Theodore    Roosevelt
Ms      Jill        Johnston
City            State   DogName
Mt.Vernon       VA      Potomac
New York City   NY      Spot
Loretto         PA      Frank
Instructions
Click on any data cell
Data > Sort … by Department Assigned, Make, Type, etc
Data > Subtotals …

 ID #  YEAR   MAKE        TYPE     ODOMETER       DEPARTMENT ASSIGNED             PRICE
    87  2002 Ford        Sedan          37780    Police & Security            $      20,888
   195  2004 Ford        Truck          19870    Telecomm                     $      18,682
   503  2003 Chevrolet   Sedan           8900    Police & Security            $      20,904
   678  1995 Ford        Pickup         73419    Housing & Residential Life   $      11,866
   696  2000 Dodge       Van            43907    Police & Security            $      15,844
   798  2003 Ford        Van            24575    Housing & Residential Life   $      19,846
   817  2004 Chevrolet   Van            16896    Housing & Residential Life   $      20,824
   818  2000 Chevrolet   Pickup         37786    Housing & Residential Life   $      13,783
   829  1995 Ford        Pickup         32765    Housing & Residential Life   $       5,000
   834  1991 Chevrolet   Van           138456    Property                     $      11,449
   841  1999 Ford        Sedan          59378    Printing Services            $      20,315
   865  2000 GMC         Van            42313    Housing & Residential Life   $      12,500
   866  2001 Ford        Sedan          49973    Police & Security            $      15,090
   874  2004 Ford        Van            43678    Telecomm                     $      21,580
   877  1996 Chevrolet   Pickup         50580    Property                     $      14,130
   888  1999 Ford        Sedan          62743    Police & Security            $      22,413
   897  2005 Ford        Truck          12703    Housing & Residential Life   $      22,724
   904  1996 Chevrolet   Sedan          53383    Police & Security            $      16,539
  1016  1997 Ford        Pickup         52728    Housing & Residential Life   $      14,757
  1055  2004 Ford        Van             3500    Dining Services              $      22,438
  1071  1993 Ford        Van            93484    Printing Services            $      12,149
  1140  2003 Ford        Truck          29781    Property                     $      19,634
  1167  2000 Ford        Truck          39292    Housing & Residential Life   $      15,366
  1172  1995 Ford        Sedan          78740    Police & Security            $      15,871
  1196  1998 Chevrolet   Van            53560    Housing & Residential Life   $      12,493
  1374  1995 Chevrolet   Sedan         137534    Police & Security            $      20,453
  1375  1999 Ford        Pickup         45608    Housing & Residential Life   $      15,866
  1415  1997 GMC         Van            89006    Athletics                    $      16,748
  1445  1996 Dodge       Van            56788    Telecomm                     $      10,105
  1455  1998 Ford        Truck          76681    Housing & Residential Life   $      12,850
  1462  1997 GMC         Van            82091    Athletics                    $      20,882
  1476  2002 Dodge       Pickup         47222    Property                     $      11,232
  1575  2000 Chevrolet   Pickup         43896    Property                     $      12,643
  1585  1999 Ford        Sedan          65404    Police & Security            $      22,674
  1599  1996 Chevrolet   Van            52672    Printing Services            $      15,773
  1602  1999 GMC         Van            35860    Dining Services              $      16,356
  1642  2003 Chevrolet   Sedan          44108    Property                     $      11,136
  1643  1998 Chevrolet   Truck          43912    Housing & Residential Life   $       7,200
  1648  1992 Chevrolet   Van           101712    Housing & Residential Life   $      14,929
  1649  2004 Chevrolet   Pickup          2250    Printing Services            $      20,680
  1675  1999 Chevrolet   Pickup         54741    Property                     $       9,839
  1678  2003 Ford        Van            25126    Housing & Residential Life   $      12,957
  1680  2003 Chevrolet   Pickup         23915    Housing & Residential Life   $      16,763
  1733  2005 Ford        Pickup          5272    Police & Security            $      15,914
  1735  1993 Chevrolet   Sedan         110863    Property                     $      11,041
  1776  1987 Chevrolet   Sedan          81802    Housing & Residential Life   $      14,993
1861   2003   Chevrolet   Van        8993   Police & Security            $   15,592
1878   1995   Chevrolet   Van       56047   Housing & Residential Life   $   15,930
1949   2004   Chevrolet   Van       34474   Dining Services              $   18,712
2020   2003   Ford        Van       22589   Housing & Residential Life   $   22,045
2021   1999   Dodge       Van       59800   Dining Services              $   14,965
2063   2001   Ford        Sedan     62611   Police & Security            $   18,872
2071   2004   Ford        Van       18755   Property                     $   20,336
2084   1997   GMC         Van       74309   Athletics                    $   14,187
2112   1986   Ford        Sedan    103123   Housing & Residential Life   $   12,007
2124   2002   Ford        Van       36838   Printing Services            $   11,518
2126   2005   Ford        Truck      3670   Housing & Residential Life   $    9,471
2128   1994   Ford        Van       99880   Athletics                    $   15,197
2131   2001   Chevrolet   Pickup    31394   Housing & Residential Life   $   15,613
2132   1998   Chevrolet   Pickup    77104   Housing & Residential Life   $   14,019
2139   2004   Dodge       Van       10650   Property                     $   11,985
2141   2005   Ford        Van        5721   Housing & Residential Life   $   22,546
2142   2005   GMC         Truck      2800   Telecomm                     $   13,410
2153   2001   Chevrolet   Van       32315   Telecomm                     $   14,109
2166   1998   Chevrolet   Van       43038   Housing & Residential Life   $   17,022
2167   2003   Ford        Truck     14678   Dining Services              $   21,743
2353   2001   GMC         Pickup    58570   Dining Services              $   14,127
2364   1990   Chevrolet   Sedan     92419   Athletics                    $   11,630
2388   2001   Dodge       Van       33321   Housing & Residential Life   $   15,485
2412   2000   Ford        Van       35497   Housing & Residential Life   $    9,025
2489   2003   Chevrolet   Van       26493   Property                     $   14,347
MAINTENANCE
$       1,933
$       1,331
$         939
$         507
$         566
$         168
$         705
$       1,594
$       1,370
$       1,235
$         473
$          30
$         567
$          28
$         279
$         112
$       1,591
$       1,641
$       2,000
$         695
$         378
$         575
$       3,491
$         724
$       1,299
$       2,235
$          72
$         755
$         345
$          81
$         980
$       2,000
$         603
$         490
$       2,933
$       1,826
$       2,000
$         499
$         664
$         370
$         160
$         675
$       1,775
$       1,839
$         964
$       1,820
$   1,871
$   1,383
$   1,025
$     379
$   1,948
$     751
$     402
$     559
$   1,350
$   2,000
$     105
$   1,975
$     450
$     594
$   1,245
$   1,118
$     257
$     732
$   1,200
$     202
$   1,255
$   2,257
$     118
$     191
$   1,002
Excel Projects

  1    Use an Excel spreadsheet as the Data Source for a Mail Merge in Word
  2    Explore Excel Help and online Training
  3    Excel Lists: data sort and subtotals
  4    Six-month budget
  5    Nine-week stock tracking
Excel Budget Project
Checking Account (Start)                                           December     January     February
Starting Balance :       Ending Balance of Previous Month
Income :                 Paycheck for Current Month
New Balance :            Starting Balance + Income

Expenses
Rent
Utilities
Car
Clothing
Food
Loans
Savings

Checking Account (End)
Total Expenses               For Current Month
Ending Balance :             New Balance - Total Expenses          $    2,894


Instructions
                         1   Enter formulas for January (in the yellow cells). The starting balance is the previous month's (De
                         2   Copy the January formulas to each other month.
                         3   Create summary formulas for the 6-month totals (in the yellow cells).
                         4   Insert sample Income or Expense values (in the blue cells).
                         5   Create a column chart of all expenses over the six months.
                         6   Create a pie chart of expenses from the 6-month totals.
                         7   Create a line chart of the Checking Account Ending Balance over 6 months.

Below is a completed six-month budget sample.
                 March     April       May        June         6-month totals




balance is the previous month's (December's) Ending Balance.




over 6 months.
Excel Nine-Week Stock Project                                                                       MSN MoneyCentral Inv

       Stock         Initial                    Number of
      Symbols       Amount      Initial Price    Shares
  1                                                               Get stock symbols: http://finance.google.com/finance
  2                                                                                    Blue: enter information
  3                                                                                    Green: copy data
  4                                                                                    Yellow: enter formula
  5
  Total Amount:


                                                                       Price per Share
                      2-Oct         9-Oct        16-Oct       23-Oct       30-Oct      6-Nov             13-Nov
    Stock Symbols
   1
   2
   3
   4
   5


                                                                       Investment Value
                      2-Oct         9-Oct        16-Oct       23-Oct        30-Oct      6-Nov            13-Nov
    Stock Symbols
   1
   2
   3
   4
   5
           Totals



Instructions
   1 Assume that you have an imaginary $5000 that you will invest in five stocks of your choice.
     You may invest equally ($1000 in each stock) or unequally (in $100 increments);
     but you must invest the entire $5000 and you must invest at least $500 in each stock.
   2 Select your five stocks, and use Excel's Investor Stock Quotes query to import data about each stock.
     From the Investor Stock Quotes results, copy values from the column titled "Last" to our column titled "Initial Price".
   3 Enter formulas to calculate "Number of Shares" and "Total Amount"
   4 For each week of the Price per Share table, refresh the Quotes data, and copy the "Last" values to the appropriate colum
   5 For the Investment Value table, insert a formula in the first cell multiplying Number of Shares by the corresponding cell in
     Copy the formulas to all other cells in the table. Calculate Totals.
   6 At the end of nine-weeks, create a line chart that shows the changes in your investment over the nine-week period.

Once per week:
    Remember that you must refresh the Quotes each week, and copy the new "Last" data to the next column in the Price pe

Below is an example of a started project:
MSN MoneyCentral Investor Stock Quotes ►

                                              Stock
                                             Symbols
 google.com/finance                    1
                                       2
                                       3
                                       4
                                       5




                   20-Nov     27-Nov




                   20-Nov     27-Nov




mn titled "Initial Price".

 es to the appropriate column.
 by the corresponding cell in the Price per Share table.

he nine-week period.


 next column in the Price per Share table.

								
To top