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 and paste
• Insert and delete cells
• Use smart tags

• Format numbers using format symbols
• Freeze titles
• Display and format the system date
• Use absolute cell references in a formula
• Use the IF function to perform a logical test

3
Objectives

• Copy absolute cell references
• Display and dock toolbars
• Add a drop shadow to a range of cells
• Create a 3-D Pie chart on a separate chart sheet
• Color worksheet tabs
• Rearrange sheets in a workbook

• Preview and print multiple sheets
• Use the Zoom box to change the worksheet view
• View different parts of the worksheet through
  window panes
• Use Excel to answer what-if questions
• Goal seek

4
Starting and Customizing Excel

• Click the Start button on the Windows taskbar,
  point to All Programs on the Start menu, and then
  click Microsoft Excel on the All Programs submenu
• If the Excel window is not maximized,
  double-click its title bar to maximize it
• If the New Workbook task pane displays, click the
  Show at startup check box at the bottom of the
  task pane to remove the check mark and then click
  the Close button in the upper-right corner to
  close the task pane
• If the Language bar displays, click its Minimize
  button
• If the Standard and Formatting toolbars display
  on one row, click the Toolbar Options button on
  the right side of either toolbar and then click
  Show Buttons on Two Rows on the Toolbar Options
  menu

5
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

6
Entering the Worksheet Titles

• Select cell A1 and then enter Awesome Intranets
  as the worksheet title
• Select cell A2 and then enter Six-Month Financial
  Projections as the worksheet subtitle

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

• Select cell B3. Type January 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 and then click the Bottom
  Border button on the Borders palette.
  Right-click cell B3 and then point to Format
  Cells on the shortcut menu
• Click Format Cells. When the Format Cells dialog
  box appears, click the Alignment tab. Click the
  45 point in the Orientation area and then point
  to the OK button

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

• Click the OK button. Point to the fill handle on
  the lower-right corner of cell B3
• 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
  Fill Options list

9
Rotating Text and Using the Fill Handle to Create
a Series of Month Names
10
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

11
Copying a Cells Format Using the Format Painter
Button
12
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 25.00 (180 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 13.00 (96 pixels)

13
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 Revenue in cell A4,
  Expenses in cell A6, Administrative in cell A7,
  Bonus in cell A8, Commission in cell A9,
  Equipment in cell A10. Enter Marketing in cell
  A11, Technical Support in cell A12, Total
  Expenses in cell A13, Net Income in cell A14, and
  Assumptions in cell A16. Select the range
  A7A12. Click the Increase Indent button on the
  Formatting toolbar. Click cell A17

14
Increasing Column Widths and Entering Row Titles
15
Copying a Range of Cells to a Nonadjacent
Destination Area

• Select the range A7A12 and then click the Copy
  button on the Standard toolbar. Click cell A17,
  the top cell in the destination area. Point to
  the Paste button
• Click the Paste button on the Standard toolbar.
  Click the Paste Options button to the right of
  the destination area to view the paste options
  available
• After reviewing the Paste Options menu, press the
  ESC key twice

16
Copying a Range of Cells to a Nonadjacent
Destination Area
17
Inserting a Row

• Right-click row heading 22 and then point to
  Insert on the shortcut menu
• Click Insert

18
Entering a Number with a Format Symbol

• Click cell A22 and enter the row title Revenue
  for Bonus in the cell
• Enter 11.75 in cell B17, 60,000.00 in cell B18,
  2.25 in cell B19, 27.75 in cell B20, 15.35 in
  cell B21, 6,500,000.00 in cell B22, and 31.5 in
  cell B23

19
Entering a Number with a Format Symbol
20
Saving the Workbook

• Click the Save button on the Standard toolbar
• When the Save As dialog box appears, type Awesome
  Intranets 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

21
Freezing Column and Row Titles

• Press CTRLHOME to select cell A1 and ensure that
  row 1 and column 1 display on the screen. Click
  cell B4, the cell below the column headings you
  want to freeze and to the right of the row titles
  you want to freeze. Click Window on the menu bar
  and then point to Freeze Panes
• Click Freeze Panes on the Window menu

22
Freezing Column and Row Titles
23
Entering the Projected Revenue
24
Entering and Formatting the System Date

• Click cell H2 and then click the Insert Function
  box on the formula bar. When the Insert Function
  dialog box appears, 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. Point to the OK button
• Click the OK button. When the Function Arguments
  dialog box appears, click the OK button.
  Right-click cell H2 and point to Format Cells
• Click Format Cells on the shortcut menu. If
  necessary, click the Number tab in the Format
  Cells dialog box. Click Date in the Category
  list. Scroll down in the Type list and then
  click 3/14/2001. Point to the OK button
• Click the OK button

25
Entering and Formatting the System Date
26
Entering a Formula Containing Absolute Cell
References

• Press CTRLHOME and then click cell B7. Type
  (equal sign) and then click cell B17. Press F4
  to change B17 to an absolute reference in the
  formula. Type (asterisk) and then click cell
  B4
• Click the Enter box in the formula bar

27
Entering a Formula Containing Absolute Cell
References
28
Entering an IF Function

• Click cell B8. Type if(b4gtb22, b18,0 in
  the cell. Click the Insert Function box in the
  formula bar to display the Function Arguments
  dialog box to view the function arguments. Point
  to the OK button
• Click the OK button

29
Entering an IF Function
30
Entering the Remaining Projected January Expense
and Net Income Formulas

• Enter the remaining formulas, as instructed on
  page E 3.29

31
Copying the Projected January Expenses and Net
Income Using the Fill Handle

• Select the range B7B14. Point to the fill
  handle in the lower-right corner of cell B14
• Drag the fill handle to select the destination
  area C7G14. Release the mouse button

32
Determining the Projected Total Expenses by
Category and Total Net Income

• Select the range H7H14
• Click the AutoSum button on the Standard toolbar

33
Unfreezing the Worksheet Titles and Saving the
Workbook

• Click cell B4 to clear the range selection from
  the previous steps
• Click Window on the menu bar and then point to
  Unfreeze Panes
• Click Unfreeze Panes
• Click the Save button on the Standard toolbar

34
Assigning Formats to Nonadjacent Ranges

• Select the range B4H4. While holding down the
  CTRL key, select the nonadjacent ranges B7H7 and
  B13H14. Use the horizontal scroll button to
  display cells to select, if necessary. Release
  the CTRL key. Right-click the selected range and
  then point to Format Cells on the shortcut menu
• Click Format Cells. When the Format Cells dialog
  box appears, 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 displays, and click (1,234.10) in
  the Negative numbers list. Point to the OK button

35
Assigning Formats to Nonadjacent Ranges

• Click the OK button. Select the range B8H12.
  Right-click the selected range. Click Format
  Cells on the shortcut menu. 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 display, click (1,234.10) in the
  Negative numbers list. Point to the OK button
• Click the OK button. Select cell A1 to deselect
  the range B8H12

36
Assigning Formats to Nonadjacent Ranges
37
Formatting the Worksheet Titles

• With cell A1 selected, click the Font box arrow
  on the Formatting toolbar. Scroll down and point
  to Impact (or a similar font) in the Font list
• Click Impact. Click the Font Size box arrow on
  the Formatting toolbar and then click 36
• Click cell A2. Click the Font box arrow. Click
  Bookman Old Style in the Font list. Click the
  Font Size box arrow and then click 16 in the Font
  Size list
• Select the range A1H2. Click the Fill Color
  button arrow on the Formatting toolbar. Click
  Red (column 1, row 3) on the Fill Color palette.
  Click the Font Color button arrow on the
  Formatting toolbar. Point to White (column 8,
  row 5) on the Font Color palette
• Click White

38
Formatting the Worksheet Titles
39
Displaying the Drawing Toolbar

• Click the Drawing button on the Standard toolbar

40
Docking a Toolbar at the Bottom of the Screen

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

41
Adding a Drop Shadow

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

42
Changing Font Size, Adding Background Colors, and
Adding Drop Shadows to Nonadjacent Selections

• With Cell A4 selected, hold down the CTRL key,
  click cells A6, A13, and A14. Click the Font
  Size box arrow on the Formatting toolbar and then
  click 12 in the Font Size list
• Click cell A4. While holding down the CTRL key,
  click cell A6 and then select the range A14H14.
  Click the Fill Color button arrow on the
  Formatting toolbar. Click Light Yellow (column
  3, row 5) on the Fill Color palette. Click the
  Shadow Style button on the Drawing toolbar and
  point to Shadow Style 14 (column 2, row 4) on the
  Shadow palette
• Click Shadow Style 14

43
Changing Font Size, Adding Background Colors, and
Adding Drop Shadows to Nonadjacent Selections
44
Formatting the Assumptions Table

• Scroll down so rows 16 through 23 appear. Click
  cell A16. 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 the
  Underline button on the Formatting toolbar.
  Select the range A16B23. Click the Fill Color
  button arrow on the Formatting toolbar. Point to
  Red (column 1, row 3) on the Fill Color palette
• Click Red 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. Click Shadow Style 14 on the Shadow
  Style palette. Select cell D23 to deselect the
  range A16B23

45
Formatting the Assumptions Table
46
Hiding the Drawing Toolbar and Saving the
Workbook

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

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

• Select the range B3G3. While holding down the
  CTRL key, select the range B14G14. Point to the
  Chart Wizard button on the Standard toolbar
• Click the Chart Wizard button on the Standard
  toolbar. When the Chart Wizard Step 1 of 4
  Chart Type dialog box appears, 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.
  Point to the Next button
• Click the Next button
• Click the Next button. When the Chart Wizard
  Step 3 of 4 Chart Options dialog box appears,
  type Six-Month Projected Net Income in the Chart
  title text box. Point to the Legend tab

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

• Click the Legend tab and then click Show legend
  to remove the check mark from its check box.
  Point to the Data Labels tab
• Click the Data Labels tab. In the Label Contains
  area, click Category name and click Percentage to
  add check marks to their respective check boxes.
  Click Show leader lines to add a check mark to
  its check box. Point to the Next button
• Click the Next button. When the Chart Wizard
  Step 4 of 4 Chart Location dialog box appears,
  click As new sheet. Point to the Finish button
• Click the Finish button. If the Chart toolbar
  appears, click its Close button

49
Drawing a 3-D Pie Chart on a Separate Chart Sheet
50
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 point to Red (column 1, row 3) on the Font
  Color palette
• Click Red. 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

51
Formatting the Chart Title and Data Labels
52
Changing the Colors of the Pie Slices

• Click the January slice twice. Click the Fill
  Color button arrow on the Formatting toolbar and
  then point to Green (column 4, row 2) on the Fill
  Color palette
• Click Green. 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 June Red May Orange
  April Yellow March Plum and February
  Blue. Click outside the Chart Area

53
Changing the Colors of the Pie Slices
54
Exploding a 3-D Pie Chart

• Click the slice labeled January twice
• Drag the slice to the desired position and then
  release the mouse button

55
Rotating and Tilting the 3-D Pie Chart

• With the January slice selected, click Chart on
  the menu bar and then point to 3-D View
• Click 3-D View. Click the up arrow button in the
  3-D View dialog box until 25 displays in the
  Elevation box
• Rotate the Pie chart by clicking the Left
  Rotation button until the Rotation box displays
  80. Point to the OK button
• Click the OK button. Click outside the chart area

56
Rotating and Tilting the 3-D Pie Chart
57
Adding Leader Lines to the Data Labels

• Click the January data label twice
• Point to the upper-left sizing handles on the box
  border and drag the January data label away from
  the January slice. Select and drag the remaining
  data labels away from their corresponding slices
  as shown on the following slide. Click outside
  the chart area

58
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 as the new tab
  label. Press the ENTER key. Right-click the tab
  and point to Tab Color on the shortcut menu
• Click Tab Color. When the Format Tab Color
  dialog box appears, click Red (column 1, row 3)
  in the Tab Color area. Point to the OK button
• Click the OK button
• Follow the first two steps, naming this sheet Six
  Month Plan, and use Yellow as the Tab Color
• Drag the Six-Month Plan tab to the left in front
  of the 3-D Pie Chart tab. Click the HOME key and
  then click cell D16

59
Renaming and Reordering the Sheets, and Coloring
Their Tabs
60
Checking Spelling in Multiple Sheets

• With the Six-Month Plan 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
• Click the Save button on the Standard toolbar

61
Previewing and Printing the Workbook in Landscape
Orientation

• 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 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 to
  view the next sheet. Click the Previous button
  to redisplay the first sheet

62
Previewing and Printing the Workbook in Landscape
Orientation

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

63
Previewing and Printing the Workbook in Landscape
Orientation
64
Shrinking and Magnifying the View of a Worksheet
or Chart

• Click the Zoom box arrow on the Standard toolbar.
  Point to 75 in the Zoom list
• 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
• Enter 68 in the Zoom box to return the chart to
  its original magnification

65
Shrinking and Magnifying the View of a Worksheet
or Chart
66
Splitting a Window into Four Panes

• Click the Six-Month Plans tab. Click cell D5,
  the intersection of the four proposed panes.
  Click Window on the menu bar and then point to
  Split
• Click Split. Use the scroll arrows to display
  the four corners of the worksheet

67
Splitting a Window into Four Panes
68
Removing the Four Panes from the Window

• Position the mouse pointer at the intersection of
  the horizontal and vertical split bars
• Double-click the split four-headed arrow

69
Analyzing Data in a Worksheet by Changing Values

• Use the vertical scroll bar to move the window so
  cell A4 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 in the middle of
  column F. Use the right scroll arrow to display
  the totals in column H in the right pane. Click
  cell B18 in the left pane
• Enter 40000 in cell B18, 15.5 in cell B20, and 10
  in cell B21

70
Analyzing Data in a Worksheet by Changing Values
71
Goal Seeking

• Close the Awesome Intranets workbook without
  saving changes. Click the Open button on the
  Standard toolbar and then reopen Awesome
  Intranets
• Drag the vertical split box to the middle of
  column F. Scroll down so row 4 is at the top of
  the screen. Display column H in the right pane.
  Click cell H14, the cell that contains the
  projected six-month total net income. Click
  Tools on the menu bar and then point to Goal Seek
• Click Goal Seek
• Click the To value text box. Type 6,000,000 and
  then click the By changing cell box. Click cell
  B23 on the worksheet and then point to the OK
  button

72
Goal Seeking

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

73
Quitting Excel

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

74
Summary

• Rotate text in a cell
• Create a series of month names
• Use the Format Painter button to format cells
• Copy and paste
• Insert and delete cells
• Use smart tags

• Format numbers using format symbols
• Freeze titles
• Display and format the system date
• Use absolute cell references in a formula
• Use the IF function to perform a logical test

75
Summary

• Copy absolute cell references
• Display and dock toolbars
• Add a drop shadow to a range of cells
• Create a 3-D Pie chart on a separate chart sheet
• Color worksheet tabs
• Rearrange sheets in a workbook

• Preview and print multiple sheets
• Use the Zoom box to change the worksheet view
• View different parts of the worksheet through
  window panes
• Use Excel to answer what-if questions
• Goal seek

76
Excel Project 3 Complete