Monday, February 28, 2011

Challenge!

3:48 PM Unknown

  • Open your Monthly Budget file.
  • Add cells C2 through C10 using a handheld calculator, the calculator on your computer, or pencil and paper.
    • If you included additional monthly bills in cells C11 through 14, add cells C2 through C14 together to get your total monthly expenses.



How long did it take you to add all those numbers? Well, in the next three modules you will learn how Excel can do the math for you!

  • Type the total you came up with in cell C15.

  • Type a subtraction formula in C17 that subtracts the amount in C15 from the amount in C16.

  • Save and close the Monthly Budget file.

    • Creating Simple Division Formulas

    3:46 PM Unknown

    Creating division formulas is very similar to the addition, subtraction and multiplication formulas. To divide the contents of cell B2 by cell B3, you would need to insert a division operator / between them, =B2/B3.
    To Create a Simple Formula that Divides One Cell by Another:


    • Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
    • Click the cell where the answer will appear (B4, for example).
    • Type the equal sign (=) to let Excel know a formula is being defined.
    • Click on the first cell to be included in the formula (B2, for example).
    • Type a mathematical operator (Ex: the division symbol /). The operator displays in the cell and Formula bar.
    • Click on the next cell in the formula (B3, for example).
    • If you include multiple cells in the formula, repeat steps 4 and 5 until the entire formula is entered.
    • Very Important: Press Enter or click the Enter button on the Formula bar. This step ends the formula.

    Creating Simple Multiplication Formulas

    3:44 PM Unknown

    Creating multiplication formulas is very similar to addition and subtraction formulas. To multiply two cells the formula, B2 and B3, you would need to insert a multiplication operator * between them, =B2*B3.


    To Create a Simple Formula that Multiplies the Contents of Two Cells:
    • Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
    • Click the cell where the answer will appear (B4, for example).
    • Type =
    • Click on the first cell to be included in the formula (B2, for example).
    • Type a mathematical operator (Ex: the multiplication symbol *). The operator displays in the cell and Formula bar.
    • Click on the next cell in the formula (B3, for example).
    • If you include multiple cells in the formula, repeat steps 4 and 5 until the entire formula is entered.
    • Press Enter or click the Enter button on the Formula bar to complete the formula.

    Creating a Simple Subtraction Formula Using the Point-and-Click Method

    3:43 PM Unknown

    Formulas can be created by using either numbers or cell references in the definition. You can also use the mouse to select the cells to be used in the formula instead of typing the cell number or cell reference. Using this method, we are going to write a simple formula that subtracts one cell from another: =B3-B2.


    To Create a Simple Formula using the Point and Click Method:
    • Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
    • Click the cell where the answer will appear (B4, for example).
    • Type the equal sign (=) to let Excel know a formula is being defined.
    • Click on the first cell to be included in the formula (B3, for example).
    • Type the subtraction sign (-) to let Excel know that a subtraction operation is to be performed.
    • Click on the next cell in the formula (B2, for example).
    • If you include multiple cells in the formula, repeat steps 4 and 5 until the entire formula is entered.
    • Press Enter or click the Enter button on the Formula bar to complete the formula.


    Creating a Simple Addition Formula (continued)

    3:41 PM Unknown

    But what if a column contains many numbers, each of which regularly changes? You don't want to write a new formula each time a number is changed. Luckily, Excel 2003 lets you include cell references in formulas.
    A formula can add the value of two cells-B2 and B3, for example. Type any two values in these two cells and the formula will adjust the answer accordingly.
    Using this method to calculate two numbers-128 and 345, for example-requires that you type 128 in cell B2, for example, and 345 in cell B3. The Excel formula, =B2+B3, would then be defined in cell B4.



















    To Create a Simple Formula that Adds the Contents of Two Cells:
    • Type the numbers you want to calculate in separate cells (for example, type 128 in cell B2 and 345 in cell B3).
    • Click the cell where the answer will appear (B4, for example).
    • Type the equal sign (=) to let Excel know a formula is being defined.
    • Type the cell number that contains the first number to be added (B2, for example).
    • Type the addition sign (+) to let Excel know that an add operation is to be performed.
    • Type the cell number that contains the first number to be added (B3, for example).
    • Press Enter or click the Enter button on the Formula bar to complete the formula.

    Creating a Simple Addition Formula

    3:39 PM Unknown

    A simple formula in Excel contains one mathematical operation only: one number plus a second number equals a third number. Writing a simple formula is really no more difficult than that: 1+1. The only difference in Excel is that all formulas must begin with the equal sign (=). It is not enough to type 1+1 in Excel because what will appear in the cell is "1+1." You must begin the equation with an equal sign, or =1+1. This holds true for any formula, simple or complicated, that adds, subtracts, multiplies or divides.
    Let's add two numbers to create a third, 128+345=473. In Excel, this would be expressed by the formula, =128+345, as shown below.


    To Create a Simple Formula that Adds Two Numbers:
    • Click the cell where the formula will be defined.
    • Type the equal sign (=) to let Excel know a formula is being defined.
    • Type the first number to be added (128, for example)
    • Type the addition sign (+) to let Excel know that an add operation is to be performed.
    • Type the second number to be added (345, for example
    • Press Enter or click the Enter button on the Formula bar to complete the formula.

    About Formulas

    3:38 PM Unknown

    In school, you learned formulas used to calculate math problems. Microsoft Excel uses these same formulas to perform calculations in a spreadsheet.
    A formula can be a combination of values (numbers or cell references) and math operators (+, -, /, *, =) into an algebraic expression. Excel requires every formula to begin with an equal sign (=).
    The following table illustrates the mathematical operators learned in school and those represented in Excel 2003.

    SchoolExcel 2003
    Addition++
    Subtraction--
    MultiplicationX*
    Division//
    Equals==
    The result of a formula-the answer to 2+3, for example-displays in the cell on the Excel worksheet. The formula is visible only in the formula bar. A formula's result will change as different numbers are entered into the cells included in the formula's definition.

    Creating Simple Formulas

    3:36 PM Unknown

    Introduction

    By the end of this lesson, learners should be able to:

    • Understand the parts of an Excel formula
    • Create a simple addition formula
    • Create a simple subtraction formula using the Point-and-Click Method
    • Create simple multiplication formulas
    • Create simple division formulas

    Challenge!

    3:33 PM Unknown

    • Open your Monthly Budget file.
    • Type the following data in the spreadsheet:
        In cell A1, type Monthly Budget.
        In cell A2, type Rent or Mortgage.
        In cell A3, type Car Payment.
        In cell A4, type Cable.
        In cell A5, type Power.
        In cell A6, type Phone.
        In cell A7, type Insurance.
        In cell A8, type Credit Cards.
        In cell A9, type Groceries.
        In cell A10, type Gas.
    • Type your other monthly bills in Column A, cells A11-A14 (if you have any).
    • Type Total Monthly Expenses in cell A15.
    • Type Income in cell A16.
    • Type Savings in cell A17.


  • Save and close the Monthly Budget file.

    • Selecting Multiple Cells

    3:30 PM Unknown

    The currently-selected cell in Excel is called the active cell. You can also select a group of adjacent cells, or a cell range. Many operations can be done against a cell range: move it, copy, it, delete it or format it. A cell range can be defined in different ways: select a specific range of cells, select multiple columns or rows, or select the entire worksheet.
    To Select a Range of Cells:
    • Move to the first cell in the range.
    • The mouse pointer becomes a large cross.
    • Click-and-hold the left mouse button and drag left or right, up or down to the last cell you want to select.
    • Release the mouse button.
    • The cells you selected are shaded.


    To Select All Cells in a Column or Row:
    • Click the gray Column heading to select the entire column. (Click and drag the cursor across other column headings to select those columns).

    Click the gray Row heading to select the entire row. (Click and drag the cursor down through the row headings select those rows).

    To Select the Entire Worksheet:
    • Click the gray rectangle in the upper left corner to select entire worksheet.

     

    If the cells and columns you want to select are not directly next to one another, select one of the ranges you want to select, and hold down the Control key while selecting other ranges.

    Performing Undo and Redo

    3:27 PM Unknown

    Sometimes, you might do something to a spreadsheet that you didn't mean to do, like type the wrong number in a cell. Excel 2003 allows you to undo an operation. Use the Undo button Undo button on the Standard toolbar to recover an error. The last single action is recoverable.
    To Undo Recent Actions (typing, formatting, etc), One at a Time:
    • Click the Undo button Undo button.
    To Undo Several Recent Actions at Once:
    • Click the arrow next to the Undo button.
    • Select the desired Undo operation(s) from the list.


  • Microsoft Excel reverses the selected action and all actions that appear in the list above it.

    • An Undo operation can be cancelled by applying a Redo. This is useful when an Undo operation was mistakenly applied. Remember, a Redo is possible only if you have not changed an Excel spreadsheet since the last Undo operation was completed:
    To Redo an Undo Operation:
    • Press the Redo button Redo button.
    To Redo several recent Undo actions at once:
    • Click the arrow next to Redo button.
    • Select the desired Redo operation from the list.
    • Microsoft Excel reverses the Undo operation

    Deleting Information in a Cell

    3:23 PM Unknown

    To Delete Data that Already Appears in a Cell:
    • Click the cell that contains the information to be deleted.
    • Press the Delete key, or
    • Right-click and choose Clear Contents from the shortcut menu.


    To Delete Data Being Typed But Not Yet Added to the Cell:
    • Cancel an entry by pressing the Escape key.
    

    Editing Information in a Cell

    3:21 PM Unknown

    Information in a spreadsheet is likely to change over time. Information can be changed in either of two ways.
    Quick and Easy Method:
    • Click the cell that contains the information to be changed.
    • Type the new entry. The old entry is replaced by the new entry.
    If the original entry is long and requires only a minor adjustment (in spelling, for example), then you can directly edit the information in the cell.
    To Edit Information in a Cell:
    Method 1: Direct Cell Editing
    • Double-click on the cell that contains the information to be changed.
    • The cell is opened for direct editing.


  • Make the necessary corrections.

  • Press Enter or click the Enter button on the Formula bar Enter button to complete the entry.

    • Method 2: Formula Bar Editing
    • Click the cell that contains the information to be changed.
    • Edit the entry in the formula bar.

    Entering Text in a Cell

    3:19 PM Unknown

    You can enter three types of data in a cell: text, numbers, and formulas. Text is any entry that is not a number or formula. Numbers are values used when making calculations. Formulas are mathematical calculations.
    To Enter Data into a Cell:
    • Click the cell where you want to type information.
    • Type the data. An insertion point appears in the cell as the data is typed.


    The data can be typed in either the cell or the Formula bar.


    Data being typed appears in the both active cell and in the formula bar.


    Notice the Cancel and Enter buttons in the formula bar.

  • Click the Enter button Enter button to end the entry and turn off the formula bar buttons.

    • Excel's AutoComplete feature keeps track of previously-entered text. If the first few characters you type in a cell match an existing entry in that column, Microsoft Excel fills in the remaining characters for you.


     


    Enter, edit and delete data

    3:16 PM Unknown

    Introduction

    By the end of this lesson, learners should be able to:

    • Enter text in a cell
    • Edit information in a cell
    • Delete information in a cell

    Closing a Workbook

    3:14 PM Unknown

    To close an existing Excel 2003 Workbook:
    • Choose File Close from the menu bar. The workbook in the Excel window is closed.

    Excel 2003 will prompt you to save information if any has been typed between the last save and the time you close the file.

    Opening a workbook

    3:12 PM Unknown

    You can open any workbook that has previously been saved and given a name.
    To Open an Existing Excel 2003 Workbook:
    • Choose File Open from the menu bar.


  • The Open dialog box opens.


  • In the Look in list, click the drive, folder, or Internet location that contains the file you want to open.

  • In the folder list, open the folder that contains the file. Once the file is displayed, click on the file you want to open.

  • Click the Open button.

    • Saving a workbook

    3:09 PM Unknown

    Every workbook created in Excel must be saved and assigned a name to distinguish it from other workbooks. The first time you save a workbook, Excel will prompt you to assign a name through the Save As operation. Once assigned a name, any additional changes made to the text, numbers or formulas need to be saved using the Save operation.
    To Save a new Workbook:
    • Choose File Save As from the menu bar.



  • The Save As Dialog Box appears.

  • Click on the Save In: dropdown menu and locate where the file will be saved. Choose 3 1/2 Floppy (A:) to save the file to a floppy disk or Local Disk (C:) to save the file to your computer.

  • Type a name for your file in the File Name: box.

  • Click the Save button.


    • To Save Changes Made to an Existing Workbook:
    • Choose File Save from the menu bar, or

      Click the Save button Save button on the Standard toolbar.
    If you're saving the file for the first time and you do not choose a file name, Microsoft Excel will assign a file name for you.
    It is a good idea to Save frequently when working in a spreadsheet. Losing information is never fun! You can quickly save your spreadsheet by using the quick-key combination Ctrl + S.

    Creating a workbook

    3:06 PM Unknown

    A blank workbook is displayed when Microsoft Excel is first opened. You can type information or design a layout directly in this blank workbook.
    To Create an Excel Workbook:
    • Choose File New from the menu bar.


    The New Workbook task pane opens on the right side of the screen.


  • Choose Blank Workbook under the New category heading.

  • A blank workbook opens in the Excel window. The New Workbook task pane is closed.



    •  


    Understanding File Terms

    2:59 PM Unknown

    The File menu contains all the operations that we will discuss in this lesson: New, Open, Close, Save and Save As.

    New
    Used to create a new Workbook.
    Open
    Used to open an existing file from a floppy disk or hard drive of your computer.
    Close
    Used to close a spreadsheet.
    Save As
    Used when to save a new file for the first time or save an existing file with a different name.
    Save
    Used to save a file that has had changes made to it. If you close the workbook without saving then any changes made will be lost.

    Create, open and save workbooks

    2:57 PM Unknown

    Introduction

    By the end of this lesson, learners should be able to:

    • State the differences between New, Open, Close Save and Save As
    • Create a workbook
    • Save a workbook
    • Open a workbook
    • Close a workbook

    Challenge!

    2:56 PM Unknown

    • Display the contents of every menu in the menu bar and note the icons associated with specific menu choices.
    • Try and find the matching pictures or shortcuts in the standard toolbar.
    • Click on each of the three worksheet tabs -- Sheet1, Sheet2 and Sheet3 --to practice moving from sheet-to-sheet in the workbook.
    • Practice scrolling in the worksheet by using the Page Up (PgUp) and Page Down (PgDn) keys.
    • Use the horizontal and vertical scrollbars to practice scrolling up, down, left and right in the worksheet.

    Moving around the worksheet

    2:54 PM Unknown

    You can move around the spreadsheet in several different ways.
    To Move the Cell Pointer:
    • To activate any cell, point to a cell with the mouse and click.
    • To move the pointer one cell to the left, right, up, or down, use the keyboard arrow keys.
    To Scroll Through the worksheet:
    The vertical scroll bar located along the right edge of the screen is used to move up or down the spreadsheet. The horizontal scroll bar located at the bottom of the screen is used to move left or right across the spreadsheet.




















    The PageUp and PageDown keys on the keyboard are used to move the cursor up or down one screen at a time. Other keys that move the active cell are Home, which moves to the first column on the current row, and Ctrl+Home, which moves the cursor to the top left corner of the spreadsheet or cell A1.
    To Move between worksheets
    As mentioned, each Workbook defaults to three worksheets. These worksheets are represented by tabs-named Sheet1, Sheet2 and Sheet3-that appear at the bottom of the Excel window.
    To Move from one worksheet to another worksheet:
    • Click on the sheet tab (Sheet1, Sheet2 or Sheet 3) that you want to display

    The Cell

    2:49 PM Unknown

    An Excel worksheet is made up of columns and rows. Where these columns and rows intersect, they form little boxes called cells. The active cell, or the cell that can be acted upon, reveals a dark border. All other cells reveal a light gray border. Each cell has a name. Its name is comprised of two parts: the column letter and the row number.

    In the following picture the cell C3, formed by the intersection of column C and row 3, contains the dark border. It is the active cell.

    Important Terms
    • Each cell has a unique cell address composed of a cell's column and row.
    • The active cell is the cell that receives the data or command you give it.
    • A darkened border, called the cell pointer, identifies it.

    Twitter Delicious Facebook Digg Stumbleupon Favorites More

     
    Powered by Blogger