Excel Project 3

1
Excel Project 3

• What-If Analysis, Charting, and Working with
  Large Worksheets

2
Objectives

• Rotate text in a cell
• Create a series of month names
• Use the Format Painter button to format cells
• Copy, paste, insert, and delete cells
• Format numbers using format symbols

3
Objectives

• Freeze and unfreeze titles
• Show and format the system date
• Use absolute cell references in a formula
• Use the IF function to perform a logical test
• Show and dock toolbars

4
Objectives

• Create a 3-D Pie chart on a separate chart sheet
• Color and rearrange worksheet tabs
• Change the worksheet view
• Goal seek to answer what-if questions

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
Bolding the Font of the Entire Worksheet

• Click the Select All button immediately above row
  heading 1 and to the left of column heading A
• Click the Bold button on the Formatting toolbar

7
Entering the Worksheet Titles and Saving the
Workbook

• Select cell A1 and then enter Aquatics Wear as
  the worksheet title
• Select cell A2 and then enter Six-Month Financial
  Projections as the worksheet subtitle
• With a floppy disk in drive A, click the Save
  button on the Standard toolbar
• When Excel displays the Save As dialog box, type
  Aquatics Wear Six-Month Financial Projection in
  the File name text box
• If necessary, click 3½ Floppy (A) in the Save in
  list. Click the Save button in the Save As
  dialog box

8
Rotating Text and Using the Fill Handle to Create
a Series of Month Names

• Select cell B3
• Type July as the cell entry and then click the
  Enter box
• Click the Font Size box arrow on the Formatting
  toolbar and then click 11 in the Font Size list
• Click the Borders button arrow on the Formatting
  toolbar and then click the Bottom Border button
  (column 2, row 1) on the Borders palette
• Right-click cell B3

9
Rotating Text and Using the Fill Handle to Create
a Series of Month Names

• Click Format Cells on the shortcut menu
• When the Format Cells dialog box is displayed,
  click the Alignment tab
• Click the 45 point in the Orientation area
• Click the OK button
• Point to the fill handle on the lower-right
  corner of cell B3

10
Rotating Text and Using the Fill Handle to Create
a Series of Month Names

• Drag the fill handle to the right to select the
  range C3G3
• Release the mouse button
• Click the Auto Fill Options button below the
  lower-right corner of the fill area
• Click the Auto Fill Options button to hide the
  Auto Fill Options menu

11
Rotating Text and Using the Fill Handle to Create
a Series of Month Names
12
Copying a Cells Format Using the Format Painter
Button

• Click cell H3
• Type Total and then press the LEFT ARROW key
• With cell G3 selected, click the Format Painter
  button on the Standard toolbar
• Point to cell H3
• Click cell H3 to assign the format of cell G3 to
  cell H3. Click cell A4

13
Copying a Cells Format Using the Format Painter
Button
14
Increasing Column Widths and Entering Row Titles

• Move the mouse pointer to the boundary between
  column heading A and column heading B so that the
  mouse pointer changes to a split double arrow
• Drag the mouse pointer to the right until the
  ScreenTip displays, Width 35.00 (250 pixels)
• Release the mouse button
• Click column heading B and drag through column
  heading G to select columns B through G
• Move the mouse pointer to the boundary between
  column headings B and C and then drag the mouse
  to the right until the ScreenTip displays, Width
  14.00 (103 pixels)

15
Increasing Column Widths and Entering Row Titles

• Release the mouse button
• Use the technique described in Step 1 to increase
  the width of column H to 15.00
• Enter the row titles in the range A4A18 as shown
  on the next slide, but without the indents
• Click cell A5 and then click the Increase Indent
  button on the Formatting toolbar
• Select the range A9A13 and then click the
  Increase Indent button on the Formatting toolbar

16
Increasing Column Widths and Entering Row Titles

• Click cell A19

17
Copying a Range of Cells to a Nonadjacent
Destination Area

• Select the range A9A13 and then click the Copy
  button on the Standard toolbar
• Click cell A19, the top cell in the destination
  area
• Click the Paste button on the Standard toolbar
• Scroll down so row 5 appears at the top of the
  window
• Press the ESC key

18
Copying a Range of Cells to a Nonadjacent
Destination Area
19
Inserting a Row

• Right-click row heading 21, the row below where
  you want to insert a row
• Click Insert on the shortcut menu
• Click cell A21 in the new row and then enter
  Margin as the row title
• Right-click row heading 24 and then click Insert
  on the shortcut menu
• Click cell A24 in the new row and then enter
  Revenue for Bonus as the row title

20
Inserting a Row
21
Entering a Number with Format Symbols

• Enter 250,000.00 in cell B19, 5.00 in cell B20,
  62.00 in cell B21, 14.00 in cell B22, 6.75 in
  cell B23, 15,000,000.00 in cell B24, and 30.00
  in cell B25

22
Entering a Number with a Format Symbol
23
Freezing Column and Row Titles

• Press CTRLHOME to select cell A1 and ensure that
  Excel displays row 1 and column 1 on the screen
• Select cell B4
• Click Window on the menu bar
• Click Freeze Panes on the Window menu

24
Freezing Column and Row Titles
25
Entering the Projected Monthly Total Net Revenue

• Enter 23538000 in cell B4, 10781000 in cell C4,
  18875345 in cell D4, 11451990 in cell E4,
  15109656 in cell F4, and 25235860 in cell G4
• Click cell H4 and then click the AutoSum button
  on the Standard toolbar twice

26
Entering the Projected Monthly Total Net Revenue
27
Entering and Formatting the System Date

• Click cell H2 and then click the Insert Function
  box on the formula bar
• When Excel displays the Insert Function dialog
  box, click the Or select a category box arrow,
  and select Date Time in the list
• Scroll down in the Select a function list and
  then click NOW
• Click the OK button
• When Excel displays the Function Arguments dialog
  box, click the OK button

28
Entering and Formatting the System Date

• Right-click cell H2
• Click Format Cells on the shortcut menu
• When Excel displays the Format Cells dialog box,
  if necessary, click the Number tab
• Click Date in the Category list. Scroll down in
  the Type list and then click 3/14/2001
• Click the OK button

29
Entering and Formatting the System Date
30
Entering a Formula Containing Absolute Cell
References

• Press CTRLHOME and then click cell B5
• Type (equal sign), click cell B4, type (1-b21,
  and then press F4 to change b21 from a relative
  cell reference to an absolute cell reference
• Type ) to complete the formula
• Click the Enter box in the formula bar
• Click cell B6, type (equal sign), click cell
  B4, type -, and then click cell B5

31
Entering a Formula Containing Absolute Cell
References

• Click the Enter box in the formula bar

32
Entering an IF Function

• Click cell B9. Type if(b4gtb24, b19,0 in
  the cell
• Click the Enter box in the formula bar

33
Entering the Remaining July Formulas

• Enter the remaining formulas, as instructed on
  page EX 173

34
Copying Formulas with Absolute Cell References
Using the Fill Handle

• Select the range B5B16 and then point to the
  fill handle in the lower-right corner of cell B16
• Drag the fill handle to the right to select the
  destination area C5G16

35
Determining Row Totals in Nonadjacent Cells

• Select the range H5H16. Hold down the CTRL key
  and select the range H9H14 and cell H16
• Click the AutoSum button on the Standard toolbar

36
Unfreezing the Worksheet Titles and Saving the
Workbook

• Press CTRLHOME to select cell B4 and view the
  upper-left corner of the screen
• Click Window on the menu bar and then click
  Unfreeze Panes
• Click the Save button on the Standard toolbar

37
Assigning Formats to Nonadjacent Ranges

• Select the range B4H4
• While holding down the CTRL key, select the
  nonadjacent ranges B6H6, B9H9, B14H14, and
  B16H16 and then release the CTRL key
• Right-click the selected range
• Click Format Cells on the shortcut menu
• When Excel displays the Format Cells dialog box,
  click the Number tab, click Currency in the
  Category list, select 2 in the Decimal places
  box, click in the Symbol list to ensure a
  dollar sign shows, and click (1,234.10) in the
  Negative numbers list

38
Assigning Formats to Nonadjacent Ranges

• Click the OK button
• Select the range B5H5
• While holding down the CTRL key, select the range
  B10H13, and then release the CTRL key
• Right-click the selected range
• Click Format Cells on the shortcut menu

39
Assigning Formats to Nonadjacent Ranges

• When Excel displays the Format Cells dialog box,
  click Currency in the Category list, select 2 in
  the Decimal places box, click None in the Symbol
  list so a dollar sign does not show, click
  (1,234.10) in the Negative numbers list
• Click the OK button
• Press CTRLHOME to select cell A1

40
Assigning Formats to Nonadjacent Ranges
41
Formatting the Worksheet Titles

• Select cell A1 and then click the Font box arrow
  on the Formatting toolbar
• Scroll down and point to Franklin Gothic Medium
  (or a similar font) in the Font list
• Click Franklin Gothic Medium
• Click the Font Size box arrow on the Formatting
  toolbar and then click 36 in the Font Size list
• Click cell A2 and then click the Font box arrow

42
Formatting the Worksheet Titles

• Click Franklin Gothic Medium (or a similar font)
  in the Font list
• Click the Font Size box arrow and then click 16
  in the Font Size list
• Select the range A1H2 and then click the Fill
  Color button arrow on the Formatting toolbar
• Click Green (column 4, row 2) on the Fill Color
  palette and then click the Font Color button
  arrow on the Formatting toolbar
• Click White (column 8, row 5) on the Font Color
  palette

43
Formatting the Worksheet Titles
44
Displaying the Drawing Toolbar

• Click the Drawing button on the Standard toolbar

45
Moving and Docking a Toolbar

• Point to the Drawing toolbar title bar or to a
  blank area in the Drawing toolbar
• Drag the Drawing toolbar over the status bar at
  the bottom of the screen

46
Adding a Drop Shadow

• With the range A1H2 selected, click the Shadow
  Style button on the Drawing toolbar
• Click Shadow Style 14 (column 2m row 4) on the
  Shadow Style palette
• Click cell A4 to deselect the range A1H2

47
Changing Font Size, Adding Underlines, Adding
Background Colors, and Adding Drop Shadows to
Nonadjacent Cells

• With Cell A4 selected, hold down the CTRL key,
  click cells A6, A8, A14, and A16
• Click the Font box arrow on the Formatting
  toolbar, scroll down and click Franklin Gothic
  Medium (or a similar font) in the Font list
• Click the Font Size box arrow on the Formatting
  toolbar and then click 12 in the Font Size list
• Use the CTRL key to select the nonadjacent ranges
  B5H5 and B13H13 and then click the Borders
  button on the Formatting toolbar
• Click cell A4 and then while holding down the
  CTRL key, click cells A6, A8, A14, and select the
  range A16H16

48
Changing Font Size, Adding Underlines, Adding
Background Colors, and Adding Drop Shadows to
Nonadjacent Cells

• Click the Fill Color button arrow on the
  Formatting toolbar and then click Light Yellow
  (column 3, row 5)
• Click the Shadow Style button on the Drawing
  toolbar
• Click Shadow Style 14 (column 2m row 4) on the
  Shadow palette

49
Changing Font Size, Adding Underlines, Adding
Background Colors, and Adding Drop Shadows to
Nonadjacent Cells
50
Formatting the Assumptions Table

• Scroll down to view rows 18 through 25 and then
  click cell A18
• Click the Font Size box arrow on the Formatting
  toolbar and then click 16 in the Font Size list.
  Click the Italic button and then click the
  Underline button on the Formatting toolbar
• Select the range A18B25, click the Fill Color
  button arrow on the Formatting toolbar, and then
  click Green (column 4, row 2) on the Fill Color
  palette
• Click the Font Color button on the Formatting
  toolbar to change the font in the selected range
  to white
• Click the Shadow Style button on the Drawing
  toolbar and then click Shadow Style 14 on the
  Shadow Style palette

51
Formatting the Assumptions Table

• Click cell D25 to deselect the range A18B25

52
Hiding the Drawing Toolbar and Saving the
Workbook

• Click the Drawing button on the Standard toolbar
• Click the Save button on the Standard toolbar

53
Drawing a 3-D Pie Chart on a Separate Chart Sheet

• Select the range B3G3
• While holding down the CTRL key, select the range
  B16G16
• Click the Chart Wizard button on the Standard
  toolbar
• When Excel displays the Chart Wizard Step 1 of
  4 Chart Type dialog box, click Pie in the Chart
  type list and then click the 3-D Pie chart
  (column 2, row 1) in the Chart sub-type box
• Click the Next button

54
Drawing a 3-D Pie Chart on a Separate Chart Sheet

• Click the Next button
• When Excel displays the Chart Wizard Step 3 of
  4 Chart Options dialog box, type Six-Month
  Projected Operating Income in the Chart title
  text box
• Click the Legend tab and then click Show legend
  to remove the check mark
• Click the Data Labels tab
• In the Label Contains area, click Category name
  and click Percentage to select them

55
Drawing a 3-D Pie Chart on a Separate Chart Sheet

• If necessary, click Show leader lines to select
  it
• Click the Next button
• When Excel displays the Chart Wizard Step 4 of
  4 Chart Location dialog box, click As new sheet
• Click the Finish button
• If the Chart toolbar appears, click its Close
  button

56
Drawing a 3-D Pie Chart on a Separate Chart Sheet
57
Formatting the Chart Title and Data Labels

• Click the chart title. On the Formatting
  toolbar, click the Font Size box arrow, click 28
  in the Font Size list, click the Underline
  button, click the Font Color button arrow, and
  then click Red (column 1, row 3) on the Font
  Color palette
• Click one of the five data labels that identify
  the slices. On the Formatting toolbar, click the
  Font Size box arrow, click 12 in the Font Size
  list, click the Bold button, and then click the
  Font Color button to change the font to the color
  red

58
Formatting the Chart Title and Data Labels
59
Changing the Colors of the Pie Slices

• Click the July slice twice (do not double-click).
  Click the Fill Color button arrow on the
  Formatting toolbar
• Click Orange (column 2, row 2). One at a time,
  click the remaining slices and then use the Fill
  Color button arrow on the Formatting toolbar to
  change each slice to the following colors August
  Yellow September Green October Plum
  November Red and December Blue. Click
  outside the Chart Area

60
Changing the Colors of the Pie Slices
61
Exploding a 3-D Pie Chart

• Click the slice labeled December twice (do not
  double-click)
• Drag the slice to the desired position

62
Rotating and Tilting the 3-D Pie Chart

• With the December slice selected, click Chart on
  the menu bar
• Click 3-D View
• When Excel displays the 3-D View dialog box,
  click the up arrow button until 25 shows in the
  Elevation box
• Click the Left Rotation button until the Rotation
  box displays 270
• Click the OK button. Click outside the chart area

63
Rotating and Tilting the 3-D Pie Chart
64
Showing Leader Lines with the Data Labels

• Click the December data label twice (do not
  double-click)
• Point to the upper-left sizing handle on the box
  border and drag the December data label away from
  the December slice
• Select and drag the remaining data labels away
  from their corresponding slices as shown
• Click outside the chart area

65
Renaming and Reordering the Sheets, and Coloring
Their Tabs

• Double-click the tab labeled Chart1 at the bottom
  of the screen
• Type 3-D Pie Chart and then press the ENTER key
• Right-click the tab
• Click Tab Color on the Shortcut menu
• When Excel displays the Format Tab Color dialog
  box, click Red (column 1, row 3) in the Tab Color
  area

66
Renaming and Reordering the Sheets, and Coloring
Their Tabs

• Click the OK button
• Follow the first two steps, naming Sheet1 Six
  Month Financial Projection, and use Light Yellow
  as the Tab Color
• Drag the Six-Month Financial Projection tab to
  the left in front of the 3-D Pie Chart tab and
  then click cell E18

67
Renaming and Reordering the Sheets, and Coloring
Their Tabs
68
Checking Spelling in Multiple Sheets

• With the Six-Month Financial Projection sheet
  active, hold down the CTRL key and then click the
  3-D Pie Chart tab
• Click the Spelling button on the Standard toolbar
• Correct any errors and then click the OK button
  when the spell check is complete
• Click the Save button on the Standard toolbar

69
Previewing and Printing the Workbook

• Ready the printer. If both sheets are not
  selected, hold down the CTRL key and then click
  the tab of the inactive sheet
• Click File on the menu bar and then click Page
  Setup. Click the Page tab and then click
  Landscape. Click Fit to in the Scaling area
• Click the Print Preview button in the Page Setup
  dialog box. When the preview of the first of the
  selected sheets appears, click the Next button at
  the top of the Print Preview window to view the
  next sheet. Click the Previous button to
  redisplay the first sheet

70
Previewing and Printing the Workbook

• Click the Print button at the top of the Print
  Preview window. When Excel displays the Print
  dialog box, click the OK button
• Right-click the Six-Month Financial Projection
  tab. Click Ungroup Sheets on the shortcut menu
  to deselect the 3-D Pie Chart tab
• Click the Save button on the Standard toolbar

71
Previewing and Printing the Workbook
72
Shrinking and Magnifying the View of a Worksheet
or Chart

• If cell A1 is not active, press CTRLHOME
• Click the Zoom box arrow on the Standard toolbar.
• Click 75
• Click the Zoom box arrow on the Standard toolbar
  and then click 100
• Click the 3-D Pie Chart tab at the bottom of the
  screen. Click the Zoom box arrow on the Standard
  toolbar and then click 100

73
Shrinking and Magnifying the View of a Worksheet
or Chart

• Enter 66 in the Zoom box to return the chart to
  its original magnification

74
Splitting a Window into Panes

• Click the Six-Month Financial Projections tab at
  the bottom of the screen
• Click cell C7, the intersection of the four
  proposed panes
• Click Window on the menu bar
• Click Split on the Window menu
• Use the scroll arrows to show the four corners of
  the worksheet at the same time

75
Splitting a Window into Panes
76
Removing the Panes from the Window

• Position the mouse pointer at the intersection of
  the horizontal and vertical split bars
• When the mouse pointer changes to a four-headed
  arrow, double-click

77
Analyzing Data in a Worksheet by Changing Values

• Use the vertical scroll bar to move the window so
  cell A6 is in the upper-left corner of the screen
• Drag the vertical split box from the lower-right
  corner of the screen to the left so that the
  vertical split bar is positioned as shown on the
  next slide
• Use the right scroll arrow to view the totals in
  column H in the right pane
• Click cell B19 in the left pane
• Enter 100000 in cell B19, 10 in cell B22, and 32
  in cell B25

78
Analyzing Data in a Worksheet by Changing Values
79
Goal Seeking

• Close the workbook without saving changes and
  then reopen it
• Drag the vertical split box to the middle of
  column F
• Scroll down so row 6 is at the top of the screen
• Show column H in the right pane
• Click cell H16, the cell that contains the
  six-month total operating income

80
Goal Seeking

• Click Tools on the menu bar
• Click Goal Seek
• When Excel displays the Goal Seek dialog box,
  click the To value text box, type 7,000,000 and
  then click the By changing cell box
• Click cell B25 on the worksheet

81
Goal Seeking

• Click the OK button
• Click the Cancel button in the Goal Seek Status
  dialog box

82
Quitting Excel

• Click the Close button on the title bar
• If the Microsoft Excel dialog box is displayed,
  click the No button

83
Summary

• Rotate text in a cell
• Create a series of month names
• Use the Format Painter button to format cells
• Copy, paste, insert, and delete cells
• Format numbers using format symbols

84
Summary

• Freeze and unfreeze titles
• Show and format the system date
• Use absolute cell references in a formula
• Use the IF function to perform a logical test
• Show and dock toolbars

85
Summary

• Create a 3-D Pie chart on a separate chart sheet
• Color and rearrange worksheet tabs
• Change the worksheet view
• Goal seek to answer what-if questions

86
Excel Project 3 Complete