1 / 86

Excel Project 3

Excel Project 3. What-If Analysis, Charting, and Working with Large Worksheets. 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. Objectives.

vladimir
Download Presentation

Excel Project 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 C3:G3 • 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 Cell’s 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 Cell’s 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 A4:A18 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 A9:A13 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 A9:A13 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 CTRL+HOME 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 CTRL+HOME 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(b4>=$b$24, $b$19,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 B5:B16 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 C5:G16

  35. Determining Row Totals in Nonadjacent Cells • Select the range H5:H16. Hold down the CTRL key and select the range H9:H14 and cell H16 • Click the AutoSum button on the Standard toolbar

  36. Unfreezing the Worksheet Titles and Saving the Workbook • Press CTRL+HOME 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 B4:H4 • While holding down the CTRL key, select the nonadjacent ranges B6:H6, B9:H9, B14:H14, and B16:H16 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 B5:H5 • While holding down the CTRL key, select the range B10:H13, 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 CTRL+HOME 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 A1:H2 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 A1:H2 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 A1:H2

  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 B5:H5 and B13:H13 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 A16:H16

  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 A18:B25, 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

More Related