# Excel 2003 Online Training by okj18402

VIEWS: 11 PAGES: 24

• pg 1
```									Microsoft Excel for CPSC 101
Related course objectives: Develop specific computer skills and competencies, including spreadsh
Mr. John Lamertina

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
Simplify formulas by using functions
Move or copy a formula
Training > Excel 2003 > Enter formulas
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
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
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