Excel Project 2 - PowerPoint PPT Presentation

About This Presentation

Title:

Excel Project 2

Description:

Excel Project 2 Formulas, Functions, Formatting, and Web Queries Objectives Enter a formula using the keyboard and Point mode Recognize smart tags and option buttons ... – PowerPoint PPT presentation

Number of Views:64

Avg rating:3.0/5.0

Slides: 77

Provided by: Steven673

Learn more at: http://www.sci.brooklyn.cuny.edu

Category:

more less

Transcript and Presenter's Notes

Title: Excel Project 2

1
Excel Project 2

Formulas, Functions, Formatting, and Web Queries

2
Objectives

Enter a formula using the keyboard and Point mode
Recognize smart tags and option buttons
Apply the AVERAGE, MAX, and MIN functions
Verify a formula using Range finder
Format a worksheet using buttons and commands

3
Objectives

Add conditional formatting to a range of cells
Change the width of a column and height of a row
Check the spelling of a worksheet
Preview how a printed copy of the worksheet will
look

4
Objectives

Print a partial or complete worksheet
Display and print the formulas version of a
worksheet
Use a Web query to get real-time data from a Web
site
Rename sheets in a workbook
E-mail the active workbook from within Excel

5
Starting and Customizing Excel

Click the Start button on the Windows taskbar,
point to All Programs on the Start menu, point to
Microsoft Office on the All Programs submenu, and
then click Microsoft Office Excel 2003 on the
Microsoft Office submenu
If the Excel window is not maximized,
double-click its title bar to maximize it
If the Language bar appears, right-click it and
then click Close the Language bar on the shortcut
menu
If the Getting Started task pane appears in the
Excel window, click its Close button in the
upper-right corner
If the Standard and Formatting toolbars are
positioned on the same row, click the Toolbar
Options button and then click Show Button on Two
Rows

6
Entering the Worksheet Title and Subtitle

Select the cell A1. Type Blue Chip Stock Club in
the cell and then press the DOWN ARROW key
Type Investment Analysis in cell A2 and then
press the DOWN ARROW key

7
Entering the Column Titles, Stock Data, and Row
Titles

Enter the values in their respective cells as
shown on the following slide. Use the ALTENTER
key combination to move to the next line within a
cell

8
Entering the Column Titles, Stock Data, and Row
Titles
9
Saving the Workbook

With a USB flash drive connected, click the Save
button on the Standard toolbar
When Excel displays the Save As dialog box, type
Blue Chip Stock Club Investment Analysis in the
File name text box
If necessary, click USB flash drive in the Save
in list. Click the Save button in the Save As
dialog box

10
Entering a Formula Using the Keyboard

With cell F4 selected, type d4e4 in the cell
Press the RIGHT ARROW key twice to select cell H4

11
Entering Formulas Using Point Mode

With cell H4 selected, type (equal sign) to
begin the formula and then click cell D4
Type (asterisk) and then click cell G4
Click the Enter box and then click cell I4. Type
(equal sign) and then click cell H4. Type
(minus sign) and then click cell F4
Click the Enter box. Click cell J4. Type
(equal sign) and then click cell I4. Type /
(division sign) and then click cell F4. Click
the Enter box

12
Entering Formulas Using Point Mode
13
Copying Formulas Using the Fill Handle

Click cell F4 and then point to the fill handle.
Drag the fill handle down through cell F12 and
continue to hold down the mouse button
Release the mouse button.
Select the range H4J4 and then point to the fill
handle
Drag the fill handle down through the range H5J12

14
Copying Formulas Using the Fill Handle
15
Determining Totals Using the AutoSum Button

Select cell F13. Click the AutoSum button on the
Standard toolbar twice.
Select the range H13I13. Click the AutoSum
button

16
Determining the Total Percent Gain/Loss

Select cell J12 and then point to the fill handle
Drag the fill handle down through cell J13

17
Determining the Average of a Range of Numbers
Using the Keyboard and Mouse

Click cell D14
Type average( in the cell
Click cell D4, the first endpoint of the range to
average and drag through cell D12, the second
endpoint of the range to average
Click the Enter box

18
Determining the Average of a Range of Numbers
Using the Keyboard and Mouse
19
Determining the Highest Number in a Range of
Numbers Using the Insert Function Box

Select cell D15
Click the Insert Function box on the formula bar
When Excel displays the Insert Function dialog
box, click MAX in the Select a function list
Click the OK button
When Excel displays the Function Arguments dialog
box, type d4d12 in the Number1 box
Click the OK button

20
Determining the Highest Number in a Range of
Numbers Using the Insert Function Box
21
Determining the Lowest Number in a Range of
Numbers Using the AutoSum Button Menu

Select cell D16
Click the AutoSum button arrow on the Standard
toolbar
Click Min
Click cell D4 and then drag through cell D12
Click the Enter box

22
Determining the Lowest Number in a Range of
Numbers Using the AutoSum Button Menu
23
Copying a Range of Cells Across Columns to an
Adjacent Range Using the Fill Handle

Select the range D14D16
Drag the fill handle in the lower-right corner of
the selected range through cell J16 and continue
to hold down the mouse button
Release the mouse button
Select cell J14 and press the DELETE key to
delete the average of the percent gain/loss

24
Copying a Range of Cells Across Columns to an
Adjacent Range Using the Fill Handle
25
Saving a Workbook Using the Same File Name

Click the Save button on the Standard toolbar

26
Verifying a Formula Using Range Finder

Double-click cell J4
Press the ESC key to quit Range Finder
Select cell A18

27
Changing the Font and Centering the Worksheet
Title

Click cell A1
Click the Font box arrow on the Formatting
toolbar
Click Arial Black (or Impact if Arial Black is
not available)
Click the Font Size box arrow on the Formatting
toolbar and click 28 in the Font Size list
Click the Bold button on the Formatting toolbar

28
Changing the Font and Centering the Worksheet
Title

Select the range A1J1. Right-click the
selection
Click Format Cells on the shortcut menu
When Excel displays the Format Cells dialog box,
click the Alignment tab
Click the Horizontal box arrow and select Center
in the Horizontal list
Click the Vertical box arrow and select Center in
the Vertical list

29
Changing the Font and Centering the Worksheet
Title

Click the Merge cells check box in the Text
control area
Click the OK button

30
Changing the Font and Centering the Worksheet
Subtitle

Click cell A2. Click the Font box arrow on the
Formatting toolbar
Click Arial Black (or Impact if Arial Black is
not available)
Click the Font Size box arrow on the Formatting
toolbar and then click 18 in the Font Size list
Click the Bold button on the Formatting toolbar
Select the range A2J2. Right-click the
selection. Click Format Cells on the shortcut
menu. When Excel displays the Format Cells
dialog box, click the Alignment tab. Click the
Horizontal box arrow and select Center in the
Horizontal list. Click the Vertical box arrow
and select Center in the Vertical list. Click
Merge cells in the Text control area. Click the
OK button

31
Changing the Font and Centering the Worksheet
Subtitle
32
Changing the Background and Font Colors and
Applying a Box Border to the Worksheet Title and
Subtitle

Select the range A1A2, click the Fill Color
button arrow on the Formatting toolbar
Click the color Blue (column 6, row 2) on the
Fill Color Palette
Click the Font Color button arrow on the
Formatting toolbar
Click the color White (column 8, row 5) on the
Fill Color Palette
Click the Font Color button arrow on the
Formatting toolbar

33
Changing the Background and Font Colors and
Applying a Box Border to the Worksheet Title and
Subtitle

Click the Thick Box Border button (column 4, row
3) on the Borders palette
Click cell B16 to deselect the range A1A2

34
Bolding, Centering, and Applying a Bottom Border
to the Column Titles

Select the range A3J3
Click the Bold button on the Formatting toolbar
Click the Center button on the Formatting toolbar
Click the Borders button arrow on the Formatting
toolbar
Click the Bottom Border button (column 2 row 1)
on the Borders palette

35
Bolding, Centering, and Applying a Bottom Border
to the Column Titles
36
Centering Data in Cells and Formatting Dates

Select the range B4B12
Click the Center button on the Formatting toolbar
Select the range C4C12
Right-click the selected range and then click
Format Cells on the shortcut menu
When Excel displays the Format Cells dialog box,
click the Number tab, click Date in the Category
list, click 03/14/01 in the Type list
Click the OK button
Select cell E4 to deselect the range C4C12

37
Centering Data in Cells and Formatting Dates

When Excel displays the Format Cells dialog box,
click the Number tab, click Date in the Category
list, click 03/14/01 in the Type list
Click the OK button
Select cell E4 to deselect the range C4C12

38
Centering Data in Cells and Formatting Dates
39
Applying a Currency Style Format and Comma Style
Format Using the Formatting Toolbar

Select the range E4I4
While holding down the CTRL key, select the range
F13I13
Click the Currency Style button on the formatting
toolbar
Select the range E5I12
Click the Comma Style button on the Formatting
toolbar

40
Applying a Currency Style Format and Comma Style
Format Using the Formatting Toolbar

Click cell E4. While holding down the CTRL key,
select cell G4
Click the Increase Decimal button on the
Formatting toolbar
Select the range E5E12. While holding down the
CTRL key, select the range G5G12
Click the Increase Decimal button on the
Formatting toolbar
Click cell A12 to deselect the range G5G12

41
Applying a Currency Style Format and Comma Style
Format Using the Formatting Toolbar
42
Applying a Thick Bottom Border to the Row Above
the Total Row and Bolding the Total Row Titles

Select the range A12J12, click the Borders
button arrow on the Formatting toolbar, and then
click the Thick Bottom Border button (column 2,
row 2) on the Borders palette
Select the range A13A16, and then click the Bold
button on the Formatting toolbar. Click cell E14
to deselect the range A13A16

43
Applying a Thick Bottom Border to the Row Above
the Total Row and Bolding the Total Row Titles
44
Applying a Currency Style Format with a Floating
Dollar Sign Using the Format Cells Command

Select the range E14I16. Right-click the
selected range
Click Format Cells on the shortcut menu
Click the Number tab in the Format Cells dialog
box
Click Currency in the Category list and then
click the third style (1,234.10) in the Negative
numbers list
Click the OK button

45
Applying a Currency Style Format with a Floating
Dollar Sign Using the Format Cells Command
46
Applying a Percent Style Format

Select the range J4J16
Click the Percent Style button on the Formatting
toolbar
Click the Increase Decimal button on the
Formatting toolbar twice

47
Applying Conditional Formatting

Select the range J4J12
Click Format on the menu bar
Click Conditional Formatting
When the Conditional Formatting dialog box
appears, if necessary, click the leftmost text
box arrow and then click Cell Value Is
Click the middle text box arrow and then click
less than

48
Applying Conditional Formatting

Type 0 in the rightmost text box
Click the Format button
When Excel displays the Format Cells dialog box,
click the Patterns tab and then click the color
Red (column 1, row 3)
Click the Font tab and then click Bold in the
Font style list
Click the Color box arrow

49
Applying Conditional Formatting

Click the color White (column 8, row 5) and then
click the OK button
Click the OK button
Click cell B16 to deselect the range J4J12

50
Changing the Widths of Columns

Point to the boundary on the right side of the
column A heading above row 1
When the mouse pointer changes to a split double
arrow, drag to the right until the ScreenTip
indicates Width 13.00 (96 pixels).
Release the mouse button
Drag through column headings B through D above
row 1
Point to the boundary on the right side of column
heading D

51
Changing the Widths of Columns

Double-click the right boundary of column heading
D to change the width of columns B, C, and D to
best fit
Click the column E heading above row 1
While holding down the CTRL key, click the column
G heading and then the column J heading above row
1 so that columns E, G, and J are selected
Point to the boundary on the right side of the
column J heading above row 1
Drag until the ScreenTip, Width 10.00 (75
pixels)

52
Changing the Widths of Columns

Release the mouse button
Click the column F heading above row 1 to select
column F
While holding down the CTRL key, click the column
H and I headings above row 1 so that columns F,
H, and I are selected
Point to the boundary on the right side of the
column I heading above row 1
Drag to the right until the ScreenTip indicates
Width 12.00 (89 pixels)

53
Changing the Widths of Columns

Release the mouse button.
Click cell B16 to deselect columns F, H, and I

54
Changing the Height of Rows

Point to the boundary below row heading 3
Drag up until the ScreenTip indicates Height
45.00 (60 pixels)
Release the mouse button
Point to the boundary below row heading 14
Drag down until the ScreenTip indicates Height
24.00 (32 pixels)

55
Changing the Height of Rows

Release the mouse button and then select cell B16

56
Checking Spelling on the Worksheet

Click cell A3 and then type Stcok to misspell the
word Stock
Click cell A1
Click the Spelling button on the Standard toolbar
With the word Stock highlighted in the
Suggestions box, click the Change button
As the spell checker checks the remainder of the
worksheet, click the Ignore All and Change
buttons as needed

57
Checking Spelling on the Worksheet

Click the OK button
Click the Save button on the Standard toolbar to
save the workbook

58
Previewing and Printing a Worksheet

Point to the Print Preview button on the Standard
toolbar
Click the Print Preview button
Click the Setup button
When Excel displays the Page Setup dialog box,
click the Page tab and then click Landscape in
the Orientation area
Click the OK button

59
Previewing and Printing a Worksheet

Click the Print button
Click the OK button
Click the Save button on the Standard toolbar

60
Printing a Section of the Worksheet

Select the range A3F16
Click File on the menu bar and then click Print
Click Selection in the Print what area
Click the OK button
Click cell B16 to deselect the range A3F16

61
Displaying the Formulas in the Worksheet and
Fitting the Printout on One Page

Press CTRLACCENT MARK ()
When Excel displays the formulas version of the
worksheet, click the right horizontal scroll
arrow until column J appears
If the Formula Auditing toolbar appears, click
its Close button
Click File on the menu bar and then click Page
Setup
When Excel displays the Page Setup dialog box,
click the Page tab

62
Displaying the Formulas in the Worksheet and
Fitting the Printout on One Page

If necessary, click Landscape to select it and
then click Fit to in the Scaling area
Click the Print button in the Page Setup dialog
box
When Excel displays the Print dialog box, click
the OK button
After viewing and printing the formulas version,
press CTRLACCENT MARK () to instruct Excel to
display the values version

63
Displaying the Formulas in the Worksheet and
Fitting the Printout on One Page
64
Changing the Print Scaling Option Back to 100

Click File on the menu bar and then click Page
Setup
Click the Page tab in the Page Setup dialog box.
Click Adjust to in the Scaling area
If necessary, type 100 in the Adjust to box
Click the OK button

65
Importing Data from a Web Source Using a Web Query

With the Blue Chip Stock Club Investment Analysis
workbook open, click the Sheet2 tab at the bottom
of the window
With cell A1 active, click Data on the menu bar,
point to Import External Data on the Data menu
Click Import Data on the Import External Data
submenu
Double-click MSN MoneyCentral Investor Stock
Quotes
When Excel displays the Import Data dialog box,
if necessary, click Existing worksheet to select
it

66
Importing Data from a Web Source Using a Web Query

Click the OK button
When Excel displays the Enter Parameter Value
dialog box, type the nine stock symbols mmm cat
ko dd ge gm intc msft wmt in the text box
Click Use this value/reference for future
refreshes to select it
Click the OK button

67
Importing Data from a Web Source Using a Web Query
68
Changing the Worksheet Names

Double-click the sheet tab labeled Sheet2 in the
lower-left corner of the window
Type Real-Time Stock Quotes as the worksheet name
and then press the ENTER key
Double-click the sheet tab labeled Sheet1 in the
lower-left corner of the window
Type Investment Analysis as the worksheet name
and then press the ENTER key

69
Changing the Worksheet Names
70
E-Mailing a Workbook from within Excel

With the Blue Chip Stock Club Investment Analysis
workbook open, click File on the menu bar and
then point to Send To
Click Mail Recipient (as Attachment) on the Send
To submenu
When the e-mail Message window appears, type
wright_alisha_at_hotmail.com in the To text box
Type the message shown on the following slide in
the message area
Click the Send button

71
E-Mailing a Workbook from within Excel
72
Saving the Workbook and Quitting Excel