Excel Chapter Two
I. Enter Data.
Create a worksheet with the following information.
If you know how to type and wrap text (ALT + ENTER), you can highlight the
following data and paste it to your worksheet.
|
Silver Dollars Stock Club |
|
|
|
|
|
|
|
|
|
Portfolio Summary |
|
|
|
|
|
|
|
|
|
Stock |
Symbol |
Date Acquired |
Shares |
Initial Price Per Share |
Initial Cost |
Current Price Per Share |
Current Value |
Gain/Loss |
Percent Gain/Loss |
|
Apple Computers |
AAPL |
12/1/2004 |
440 |
64.59 |
|
82.99 |
|
|
|
|
AT&T |
T |
12/16/2003 |
870 |
28.71 |
|
27.99 |
|
|
|
|
Citigroup |
C |
1/17/2003 |
960 |
49.46 |
|
44.675 |
|
|
|
|
Comcast |
CMCSA |
12/11/2002 |
380 |
33.62 |
|
41.39 |
|
|
|
|
Google |
GOOG |
2/20/2004 |
920 |
390.32 |
|
492.55 |
|
|
|
|
Home Depot |
HD |
11/14/2005 |
770 |
34.54 |
|
31.72 |
|
|
|
|
IBM |
IBM |
9/14/2004 |
990 |
74.08 |
|
81.47 |
|
|
|
|
Merck |
MRK |
1/14/2005 |
950 |
42.125 |
|
38.34 |
|
|
|
|
Sprint Nextel |
S |
9/10/2003 |
560 |
17.79 |
|
21.18 |
|
|
|
|
Totals |
|
|
|
|
|
|
|
|
|
|
Average |
|
|
|
|
|
|
|
|
|
|
Highest |
|
|
|
|
|
|
|
|
|
|
Lowest |
|
|
|
|
|
|
|
|
|
II. Do Calculation.
- Calculate the
Initial Cost, Current Value,
Gain/Loss, and
Percent Gain/ Loss by giving formula to
cells F4, H4, I4, and J4.
After that use "fill handle" (a
special corner) to fill columns F, H, I and J.
The math of the calculations are followed:
Initial Cost
=
Shares *
Initial Price Per Share
Current Value =
Shares *
Current Price Per Share
Gain/Loss =
Current Value -
Initial Cost
Percent Gain/Loss =
Gain/Loss /
Initial Cost
- Use AutoSum button
to calculate the
totals for cells F13, H13 and I13.
Use Percent Gain/Loss
=
Gain/Loss /
Initial Cost for cell J13.
- Find the average, highest and lowest for columns D
to J. However, you have to delete cell J14 since it is invalid.
The ways to do are followed:
a. Type =average(
or =max(
or =min( then
highlight the area which you want them to be calculated then hit Enter.
b. Use fx
function key, and choose an appropriate function to do it. A new window
may need to be moved to a side
and identify the cells which function is applied to.
- Save the file as "Silver Dollars
Stock Club Portfolio Summary" in your USB flash drive.
- Verifying a formula using Range Finder:
Double-click cell J4 to activate Range Finder / Press the ESC
key to quit Range Finder.
III. Format the Worksheet.
- Click Page Layout / Themes / Concourse.
- Make the title and subtitle in the center of the row. (Hint:
highlight the area and click merge and center button)
- Select A1 and A2 / click Cell Styles button /
Title.
- Decrease font size
for A2.
- Fill in color (column 5, row 3) for A1 and A2 / Click Thick Box
Border
- Highlight A3 to J3 / click Cell Styles / Heading 3.
- Highlight A13 to J13 / click Cell Styles / Total Cell
Style.
- Boldface: A14 to A16.
- Center: B4 to B12.
- Highlight C4 to C12 / Right-click on it / click Format
Cells
- Hold Ctrl key to highlight two
separated areas: highlight E4 to I4 and F13 to I13 / click
$ (Accounting Number Format).
- Highlight E5 to I12 / click , (Comma Style)
- Increase decimal place for E4 and G4.
Also, do it for E5 to E12 and G5 to G12.
- Highlight E14 to I16 /
right-click on it / Format Cell / Currency / third style
/ ok.
- Highlight J4 to J16 /
click % (percent style button) / increase to 2 decimal places.
- Highlight J4 to J12 /
click Conditional Formatting / New Rule
Click Format / Fill light red color (column 6, row 2) / OK / OK.
- Change the width of columns. The result will be shown like the
following one.
IV. Copy an entire worksheet to paste to the next worksheet.
- Click copy.
- Click sheet 2 / paste
- View the Formulas: Hold Ctrl + ~.
It is a toggle switch. That is, use Ctrl + ~ to go back value version.
- Rename sheet1 as Portfolio Summary
and sheet 2 as Formula.
- Click
to save the file.
V. Set Up Print Format
Find a way to
print a page in landscape style and all materials are fitted in one page.