Docstoc

ICL Excel Introduction

Document Sample
ICL Excel Introduction Powered By Docstoc
					                                Excel Introduction Outline
 •   What is Excel?
 •   Spreadsheet Terminology
 •   Opening a new Workbook
 •   Excel 2007’s Interface
 •   Downloading & Saving Templates
 •   Selecting Cells
 •   Entering and Editing Data
 •   Saving Workbook Changes & Closing a Workbook
 •   Moving around a Worksheet
 •   Selecting Ranges
 •   Selecting, Moving, and Copying Data
 •   Fill Handle and Auto Fill
 •   Resizing Columns and Rows
 •   Word Wrapping
 •   Inserting Columns and Rows
 •   Deleting Columns and Rows
 •   Clearing Cells
 •   Formulas and Functions
 •   Copying Formulas and Functions (Relative versus Absolute Referencing)
 •   Text Formatting
 •   Aligning Cell Contents
 •   Cell Borders and Shading
 •   Number Formatting
 •   Page Formatting /Printing

Windows Applications
                                               Excel Introduction            Page 1
Robert J Hancock
What is Excel and why use it?
Excel is a spreadsheet application program.

Spreadsheet characteristics:
•Organized as a large 2 dimensional table of columns and rows
        • Columns are labeled with alphabetic letter combinations
        • Rows are labeled with numbers
        • The column label followed by the row label identifies the address of each cell
        • Excel 2007 provides approximately 1 Million cells
•Text and numeric data is entered into cells
•Text and numeric data can be sorted, summarized, and processed with mathematical and logical
operations.

Spreadsheets have broad application in both the business and home environments. Typical business
uses include accounting, statistical analysis, and project management. Business forms are easily
created (e.g., invoices, purchase orders, etc.)




Windows Applications
                                          Excel Introduction                              Page 2
Robert J Hancock
Spreadsheet terminology
A spreadsheet is a matrix of columns and rows. The columns are labeled with sequential alphabetic letters (A, B, … Z,
AA, AB, …AZ, BA, BB…BZ, etc.) and the rows are labeled with sequential numbers (1, 2, …). At the intersection of
each column and row is a cell, into which you enter data. Each cell has a unique address or cell reference, which
consists of its column letter and row number. For example, the top left cell is A1.
A cell can contain any of the following:
   • A number (with or without associated decimal points, commas, currency symbols, percentage symbol, etc.)
   • Text (including any combination of letters, numbers, and symbols that aren't number-related)
   • A formula
         o a mathematical equation
         o always begins with an equal sign
         o can include math operators, cell references, numbers, parenthesis
                 + Addition
                 - Subtraction
                 * Multiplication
                 / Division
                 ^ Exponential
                 example: =D4/3+D5+(D6+D7)*2
   • A function
          o A formula that includes a predefined shortcut Name that performs a more complex mathematical or
              logical calculation
          o Example: =AVERAGE(B2:B100) returns the average value of the numbers in cells B2 through B100 by
              summing the values in the range of cells from B1 through B100 and dividing by the number of entries in
              that range of cells
          o Excel provides a library of functions to perform mathematical, statistical, financial, logical and other
              calculations
   Windows Applications
                                                   Excel Introduction                                        Page 3
   Robert J Hancock
  Practice: Open a new Excel Workbook
  Select Start > All Programs > Microsoft Office > Microsoft Office Excel 2007

  This opens a new Excel workbook as illustrated below. A workbook consists of one or more tabbed
  sheets called worksheets (Microsoft's term for spreadsheet). Observe the three tabbed sheets at the
  bottom labeled Sheet1, Sheet2, and Sheet3.




                       Sheet Tabs




Windows Applications
                                               Excel Introduction                                       Page 4
Robert J Hancock
   Microsoft                             Excel 2007 Window
   Office Button      Quick Access Toolbar
                                          Tabs                                   Groups




 Ribbon




                                                                                              Column
                                 Function Bar                                                 Headers


                   Insert Function Button
                                                                   Cells
Row                                                Cells are outlined by light gray lines
Headers                                              that divide rows and columns.




                    Sheet Tabs       Insert New Worksheet Tab                               Scroll Bars

                                                 Status Bar                                          Zoom Control




    Windows Applications
                                                        Excel Introduction                                          Page 5
    Robert J Hancock
Microsoft Office Button: Opens the only drop down menu in Excel 2007 – contains commands for opening and
saving workbooks, printing worksheets, and other controls.

Quick Access Toolbar: This is a user customized toolbar to place shortcuts to your favorite commands. By default, it
contains Save, Undo, and Redo buttons.

Ribbon: This is the multi-tabbed toolbar system that contains buttons and other controls for issuing commands.

Tabs/Groups/Commands: Commands with similar attributes are grouped under distinctive Groups that are
organized under Tabs . Click on a Tab to display the associated Groups of Commands.

Insert Function button: You click this button to get help creating functions

Formula bar: Displays contents of the selected cell. You can type and edit the cell’s contents here.

Column headers: Labeled with consecutive alphabetic letters. Click a column letter to select the entire column.

Row headers: Labeled with consecutive numbers. Click a row number to select the entire row.

Scroll bars: Scroll within the active worksheet.

Zoom controls: Change the magnification at which you view the active worksheet.

Worksheet tabs: You can click one of these tabs to switch between worksheets

Insert New Worksheet Tab: Click to insert a new worksheet.
.
Windows Applications
                                                   Excel Introduction                                     Page 6
Robert J Hancock
Microsoft Office Button: clicking displays Excel 2007’s only drop down menu. The
following are the primary buttons you’ll use:
   •   New: click to open a new workbook
   •   Open: click to open an existing (already saved) workbook
   •   Save: click to save updates to an existing (previously saved) workbook
   •   Save As: click to save an existing workbook but with a different name, or save an existing
       workbook to a different location (Storage Device or folder), or save as a different file format
       other than the default .xlsx extension, etc.
   •   Print: click to print a worksheet




                                            Note:
                                            • Clicking directly on any Command button invokes Excel’s
                                              default operation
                                            • Command buttons with arrows to the right provide
                                              additional options other than the default operation:
                                                  • flyover to view additional options in the right column
                                                  • click on any option in the right column

Windows Applications
                                            Excel Introduction                                      Page 7
Robert J Hancock
Quick Access Toolbar: This is a user customized toolbar to place shortcuts to your
favorite commands. The following are default buttons:
   • Save
   • Undo
   • Redo




                               My optional buttons: Print Preview, Print, Open, New, Spell Check




Windows Applications
                                     Excel Introduction                                Page 8
Robert J Hancock
Ribbon: Each Ribbon Tab has named sections called groups. In this example, the Home tab
is selected, which contains the Clipboard, Font, Alignment, Number, Styles, Cells, and
Editing groups.




Some of the groups have a Dialog box launcher icon in their lower-right corner, which
opens a dialog box containing more options for the control settings in that group.

                                                          Note:
                                                          Group details automatically expand
                                                          or collapse based on the width of
                                                          the Excel window.




Windows Applications
                                     Excel Introduction                                 Page 9
Robert J Hancock
Example: Clicking on the Dialog box launcher in the corner of the Alignment Group
under the Home tab, opens the Alignment Tab in the Format Cells dialog box for more
options.




Windows Applications
                                   Excel Introduction                         Page 10
Robert J Hancock
Download a Template entitled Expense report
   1. Click on Microsoft Office Button
         2. select New > The New Workbook dialog box opens
                                      2. Select Expense reports under the Microsoft Office Online
                                         window pane




                                                                    3. Select Expense report
                                                                       in middle window pane



                                                                    4. Click Download




Windows Applications
                                       Excel Introduction                              Page 11
Robert J Hancock
The template Expense report1 workbook opens on your desktop




  Windows Applications
                                        Excel Introduction    Page 12
  Robert J Hancock
Save the Workbook as Expense report1.xlsx
Attach your Flash Drive and save the Workbook as Expense report1.xlsx on your Flash Drive
  1.   Click the Microsoft Office Button and select Save As
  2.   The Save As dialog box pops-up
  3.   Select My Computer > all storage devices attached to your computer will display in the right window pane
                       4. Double click your Flash Drive (note: your Flash Drive Label and Drive Letter will be different)




                                                   6. Navigate to any desired
                                                      folder location on your
                                                      Flash Drive and then
                                                      click Save
                    5. Verify the File name is
                       correct. Otherwise,
                       retype the name as
                       shown here.




  Windows Applications
                                                  Excel Introduction                                        Page 13
  Robert J Hancock
                       All Roads Lead To Rome
Pick one or two Roads you like the most and stick with it!

With almost every topic presented in this lesson, there are multiple methods
available to accomplish each specific Excel task.

In most cases, I have presented all the available methods.

As I present this material, I will focus on one or two methods and only mention
the other methods in passing (in most instances).

Pick one or two Methods you like the most and stick with it!




Windows Applications
                                  Excel Introduction                      Page 14
Robert J Hancock
Selecting a cell
Before entering or editing data in a cell, you first select it. Selection Options:
   • Click the cell (a thick outline highlights the selected cell)
   • Press the arrow keys on the keyboard to move the cell selector
   • Press the Tab key on your keyboard to move the cell selector to the right
   • Press Shift+Tab to move the cell selector to the left
   • Press the Home key to move to the leftmost cell in the same row
   • Press the Ctrl+Home key to move to cell A1
   • Press the Ctrl+End key to move to the highest cell address used
   • Press Ctrl+Backspace to bring the active cell into view if you have scrolled away from the worksheet area
      displaying the active cell

Entering Data
After selecting a cell, data can also be edited directly in the cell or in the Function Bar.

Aborting Data Entry
As you are typing, if you change your mind about entering or editing data in the cell, press Esc before pressing Enter or
moving to a different cell. Once moving away from a cell, press Ctrl+z or click the Undo Button on the Quick Access
Toolbar to undo the last action.

Editing Data
You can change the contents of a cell directly in the cell by double clicking in the cell or by selecting the cell and clicking
in the data displayed in the Function Bar.




   Windows Applications
                                                       Excel Introduction                                          Page 15
   Robert J Hancock
 Practice Entering and Editing Data
 • In cell C4, type Attend Tea Party Conference
 • In cell C7, type your name
 • In cells B11 through K12, type the entries as shown in this example (do not enter the $ signs)
 • Select cell L29 and change the text to Balance Due




Click on any of the
following cells E26:L26,
cells M11:M25, cell M27,
and cell M29 and observe
that the formula of the
selected cell displays in the
Function Bar. Also observe
how Excel automatically
recalculates the entire
worksheet and returns
updated values after each
expense item is entered.




    Windows Applications
                                                    Excel Introduction                              Page 16
    Robert J Hancock
Save Your Workbook Changes
To save changes to a previously saved file, choose from the following:
    • Click the Microsoft Office Button, and then click Save
    • Click the Save button on the Quick Access Toolbar
    • Press Ctrl+s


Close Workbook Expense report1.xlsx
To close an open workbook, do any of the following:
    • Click the Microsoft Office Button, and then click Close
    • Click the Close X icon in the upper right hand corner of the Excel Window
    • Right click on the Title Bar and click Close
    • Press Alt+F4
    • If changes have been made since the last time you saved the workbook, Excel will prompt you to
      save the changes as follows:




   • Note that only the active workbook is closed. If other Excel workbooks are open, Excel does not
     close but now displays another open workbook. In this example, a new workbook with the default
     name Book1 is still open.

  Windows Applications
                                             Excel Introduction                             Page 17
  Robert J Hancock
Moving around a worksheet

Each blank spreadsheet is much larger than can be displayed on the screen at once (Excel 2007
provides approximately 1 Million blank cells). With very large tables of data, you may not be able to
display the entire used worksheet area at the same time.

The simplest method of viewing the non-visible worksheet area is using the horizontal and vertical
scroll bars.
                            To use a scroll bar:

                              To scroll a little bit at a time, click a scroll arrow at one end or the other
                              of a scroll bar.

                              To scroll one screen at a time, click above or below the vertical scroll
      Vertical Scroll Bar
                              box or to the right or left of the horizontal scroll bar.

            Scroll Boxes      To scroll quickly, drag the scroll box.

                              Alternative method using keyboard:
Horizontal Scroll Bar
                                    Page Down: Down one screen
                                    Page Up: Up one screen
                                    Alt+Page Down: Right one screen
                                    Alt+Page Up: Left one screen
   Windows Applications
                                             Excel Introduction                                     Page 18
   Robert J Hancock
Selecting ranges

A range is a group of cells. By selecting a range, you can then perform an action on the entire group of
cells with a single operation, such as applying formatting or clearing the contents. A range normally
extends from two cells, one or more rows of cells, one or more columns of cells, or large blocks of cells.

A range is referenced by the upper left and lower right cells separated with a colon. For example, the
range of cells A1 through G10 would be referred to as A1:G10.

To select a range:
   • With the mouse: Drag across the desired cells. Be careful to position the mouse pointer over the
      center of the cell, and not over an edge or corner.
   • With the keyboard: Select the first cell, and then hold down the Shift key while you press the
      arrow keys to expand the selection area.
   • To select a nonrectangular or noncontiguous range, select the first portion of the range (that is,
      the first rectangular piece), and then hold down the Ctrl key while you select additional
      cells/ranges with the mouse.
   • To select an entire column, click the column header . To select an entire row, click the row header.
      You can click one row or column and then drag to select additional columns, or hold down Ctrl as
      you click on the headers for noncontiguous rows and/or columns.



  Windows Applications
                                              Excel Introduction                                 Page 19
  Robert J Hancock
Practice selecting ranges

In Book1, Sheet1:
   1. Select column B by clicking on the column B header.
   2. Select contiguous columns B through G by pressing and holding the Shift key and then click on
       column G. Release the Shift key.
   3. Select column A by clicking on the column A header, which also deselects any prior selection
   4. Select non-contiguous columns A, E, G and K by pressing and holding the Ctrl key and then click
       on columns E, G, and K. Release the Ctrl key.
   5. Select row 4 by clicking on row 4’s header. The columns are deselected, and row 4 is now
       selected.
   6. Select cell C2 which also deselects the prior selection.
   7. Select the range C2:F10 by pressing and holding Shift key by dragging the mouse across cells C2
       to F10. Release the Shift key.
   8. In addition to selecting range C2:F10, also select the non-contiguous range H2:K10 by pressing
       and holding the Ctrl key and dragging the mouse across cells H2 to K10. Release the Ctrl key.
   9. Select the entire spreadsheet by pressing Ctrl+a. (Remember that Ctrl+a is a universal shortcut
       for selecting all.)
   10. Undo the selection by clicking in any cell or by pressing the Esc key
   11. An alternate method for selecting the entire spreadsheet is to press the square containing a gray
       triangle at the intersection of the column and the row headers in the upper left corner.


  Windows Applications
                                             Excel Introduction                                Page 20
  Robert J Hancock
Moving Data

You will find the need to move data from one cell to another or to move a range of cell contents from one range into
another.

You can easily move cell contents between cells either with Drag-and-Drop or Cut-and-Paste methods.
   • Drag-and-Drop:
         • Select the cell or range, position the mouse over any edge (the mouse cursor changes to a four headed
             arrow) and then drag the selection by its border to the new location.
   • Cut-and-Paste using the keyboard:
         • Select the cell or range
         • Press Ctrl+x (cuts selection to Clipboard)
         • Select the upper left cell in the new range to move to
         • Press Ctrl+v (pastes from Clipboard)
   • Cut-and-Paste using Right Click:
         • Select the cell or range
         • Right click and select Cut (cuts selection to Clipboard)
         • Select the upper left cell in the new range to move to
         • Right click and select Paste (pastes from Clipboard)
   • Cut-and-Paste using the Clipboard group under the Home tab
         • Select the cell or range
         • Click the scissors cut icon in the Clipboard group under the Home tab (cuts selection to Clipboard)
         • Select the upper left cell in the new range to move to
         • Click the Paste icon in the Clipboard group in the Home tab (pastes from Clipboard)


   Windows Applications
                                                   Excel Introduction                                      Page 21
   Robert J Hancock
Copying Data

You will find the need to copy data from one cell to another or to copy a range of cell contents from one range into
another.

You can easily copy cell contents between cells either with Drag-and-Drop or Copy-and-Paste methods.
   • Drag-and-Drop:
         • Select the cell or range, position the mouse over any edge (the mouse cursor changes to a four headed
             arrow), press and hold the Ctrl key and then drag the selection by its border to the new location.
   • Copy-and-Paste using the keyboard:
         • Select the cell or range
         • Press Ctrl+c (copies selection to Clipboard)
         • Select the upper left cell in the new range to copy to
         • Press Ctrl+v (pastes from Clipboard)
   • Copy-and-Paste using Right Click:
         • Select the cell or range
         • Right click and select Copy (copies selection to Clipboard)
         • Select the upper left cell in the new range to copy to
         • Right click and select Paste (pastes from Clipboard)
   • Copy-and-Paste using the Clipboard group under the Home tab
         • Select the cell or range
         • Click the scissors copy icon in the Clipboard group under the Home tab (copies selection to Clipboard)
         • Select the upper left cell in the new range to copy to
         • Click the Paste icon in the Clipboard group in the Home tab (pastes from Clipboard)


   Windows Applications
                                                   Excel Introduction                                        Page 22
   Robert J Hancock
Fill Handle and Auto Fill for copying data

The black square in the lower-right corner of a selected cell or range is called the Fill Handle


                                             Copy a cell’s content or the contents of a range of cells by
                                             positioning the mouse over the Fill Handle and then dragging
                                             across the cell(s) you want to copy to.

                                             In one operation, you can either copy across columns or
                                             across rows, but not both.

                                             Auto Fill:
                                             Auto Fill is a special case of using the Fill Handle. When using
                                             the Fill Handle, Excel automatically recognizes common
                                             incremental steps in the data and assumes you want to
                                             continue the same sequence. Examples:
                                               • Date data: dates, names of days, names of months, etc.
                            Fill Handle        • two or more cells with consistent increments in numbers
                                                 (e.g., 1,2; 25,50; 100, 200; etc.)




  Windows Applications
                                              Excel Introduction                                   Page 23
  Robert J Hancock
Practice Moving data

In a Book1, Sheet1, type data in cells A1 through B2 as follows:
    • A1: your first name
    • B1: your last name
    • A2: 100
    • B2: 125




Move the range A1:B2 with the drag-and-drop method:
  1. Select the range A1:B2.
  2. Position the mouse pointer over any border of the range
     except in the lower-right corner (The mouse cursor changes to
     a four headed arrow.)
  3. Move the selection by dragging the selection to D4:E5




   Windows Applications
                                                    Excel Introduction   Page 24
   Robert J Hancock
Practice Moving data (continued)

Undo the last operation by clicking the Undo button on the Quick Access Toolbar

Move the range A1:B2 with the keyboard cut-and-paste method:
  1. Select the range A1:B2.
  2. Cut the selection to the clipboard by pressing Ctrl+x
  3. Select cell D4 (the upper most left cell in range D4:E5)
  4. Copy the selection on the clipboard to D4:E5 by pressing Ctrl+v

Undo the last operation by pressing the universal undo shortcut Ctrl+z key

Move the range A1:B2 with the right click cut-and-paste method:
  1. Select the range A1:B2.
  2. Cut the selection to the clipboard by right clicking and selecting Cut
  3. Select cell D4 (the upper most left cell in range D4:E5)
  4. Copy the selection on the clipboard to D4:E5 by right clicking and selecting Paste

At home, you may also want to try moving the range with the Clipboard group under the Home Tab




  Windows Applications
                                             Excel Introduction                           Page 25
  Robert J Hancock
Practice Copying Data
Copy the range D4:E5 with the drag-and-drop method:
   1. Select the range D4:E5
   2. Position the mouse over any edge (the mouse cursor changes to a four headed arrow), press and
      hold the Ctrl key and then drag the copy of the selection by its border to cell range A1:B2.
   3. Undo the last operation (use your preferred method)

Copy the range D4:E5 with the keyboard copy-and-paste method:
  1. Select the range D4:E5
  2. Copy the selection to the clipboard by pressing Ctrl+c
  3. Select cell A1 (the upper most left cell in range A1:B2)
  4. Paste the selection on the clipboard to A1:B2 by pressing Ctrl+v
  5. Undo the last operation (use your preferred method)

Copy the range D4:E5 with the right click cut-and-paste method:
  1. Select the range D4:E5.
  2. Copy the selection to the clipboard by right clicking and selecting Copy
  3. Select cell A1 (the upper most left cell in range A1:B2)
  4. Paste the selection on the clipboard to A1:B2 by and selecting Paste




  Windows Applications
                                             Excel Introduction                            Page 26
  Robert J Hancock
Practice Copying using the Fill Handle and Auto Fill
Copy cell B1 into cells B1 through M1 using the Fill Handle:
   1. Select cell B1
   2. Position the mouse over the Fill Handle (the mouse cursor changes to a cross)
   3. Drag the mouse to cell M1 (your last name is copied to each cell in the range B1:M1)




Copy cell A2 into cells A3 through A10 using the Fill Handle:
  1. Select cell A2
  2. Position the mouse over the Fill Handle (the mouse cursor changes to a cross)
  3. Drag to cell A10 (the number 100 is copied to each cell in the range A3:A10)




   Windows Applications
                                                  Excel Introduction                         Page 27
   Robert J Hancock
Practice Copying using Auto Fill

Use Auto Fill to continue the sequence 100, 125 into cells C2 through M2:
   1. Select cell range A2:B2
   2. Position the mouse over the Fill Handle (the mouse cursor changes to a cross)
   3. Drag to cell M2 (the sequence of incrementing the number by 25 is continued to cell M2)




Select Sheet 2 of Practice.xlsx and use Auto Fill to create the sequence January through December in cells B1
throughM1:
   1. Select cell B1 and type January, press Enter
   2. Select cell B1 and position the mouse over the Fill Handle (the mouse cursor changes to a cross)
   3. Drag to cell M1 (the sequence of incrementing the names of the months continues to column M)




   Windows Applications
                                                   Excel Introduction                                      Page 28
   Robert J Hancock
Practice Copying using Auto Fill (continued)

Use Auto Fill to create the sequence Monday through Sunday in cells A2 through A8:
   1. Select cell A2 and type Monday, then press Enter
   2. Select cell A2 and position the mouse over the Fill Handle (the mouse cursor changes to a cross)
   3. Drag to cell A8 (the sequence of incrementing the days of the week continues to row 8)




   Windows Applications
                                                  Excel Introduction                                     Page 29
   Robert J Hancock
Resizing Columns
When text exceeds the width of a cell, the text overflows the cell. If the adjacent column’s cell is empty, the text
continues into the adjacent cell. If data is present or is later entered in the adjacent cell, then the display of overflow
portion of the contents of the first column truncates (the truncated portion is retained but does not display).




• Observe that cells A4 and M1 overflow but continue to display in the adjacent cells since the adjacent cells are
  empty.
• Observe that cells J1 and L1 overflow but do not continue to display in the adjacent cells since the adjacent cells
  contain data.
• You can resize any columns to increase their width to prevent overflowing into the adjacent cells.
• Note: when a number (including dates and time) is entered in a cell and the column width is too narrow to display
  the entire content, the entire cell will display hash marks (####). You must resize the column to display the
  number.
• Save your workbook as Practice.xlxs




   Windows Applications
                                                      Excel Introduction                                          Page 30
   Robert J Hancock
Resizing columns (continued)

You resize a single column width using one of four methods:
  1. On the column header, position the cursor on the right border of the column to resize (the cursor
      will change to a cross with right and left arrows on the cross’s horizontal line) and drag the border
      left to make the column width smaller and drag the border right to make the column width larger.
  2. On the column header, position the cursor on the right border of the column to resize (the cursor
      will change to a cross with right and left arrows on the cross’s horizontal line) and double click.
      The column width will adjust so that the widest cell entry in the column exactly fits.
  3. Select the column to resize, then on the Home tab, in the Cells group, select Format > AutoFit the
      Column Width . The column width will adjust so that its widest entry exactly fits.
  4. You can also specify an exact width as a number. The default column width 8.43. To specify exact
      width:
         • Right-click a selected column, and then select Column Width... Enter the desired column
             width in the Dialog Box and click OK.
         • Alternately, on the Home tab, in the Cells group, select Format > Column Width. Enter the
             desired column width in the Dialog Box and click OK.
  5. You can also resize one or more ranges of columns by selecting the desired range(s) and using
        then using the same options as when resizing a single column.




  Windows Applications
                                              Excel Introduction                                 Page 31
  Robert J Hancock
Word Wrap

An alternative to resizing columns to display text is to set a cell or range of cells to Wrap text. When
set, if the cell text overflows the width of the cell, the row height adjusts to provide for two or more
rows of text to exactly fit the entire text content.

Wrap text is set with one of two methods:
  1. Select the cell or range of cells
        • Right click and select Format Cells… The Format Cells Dialog Box pops-up
        • On the Alignment tab, check the Wrap text checkbox, and then click OK.
  2. Select the cell or range of cells
        • On the Home tab, in the Cells group, select Format > Format Cells... On the Alignment tab,
           check the Wrap text checkbox, and then click OK.

Note: Wrapping text only works with text and not numbers.




  Windows Applications
                                              Excel Introduction                                  Page 32
  Robert J Hancock
Resizing rows

You resize row heights with similar methods as resizing columns, although this isn't quite as important
because row height adjusts automatically when entering data to accommodate the largest font used in
that row.

You resize a row height using one of four methods:
  1. On the row header, position the cursor on the bottom border of the row to resize (the cursor will
      change to a cross with up and down arrows on the cross’s vertical line) and drag the border up to
      make the row width smaller and drag the border down to make the row width larger.
  2. On the row header, position the cursor on the bottom border of the row to resize (the cursor will
      change to a cross with up and down arrows on the cross’s vertical line) and double click. The row
      width will adjust so that the tallest cell entry in the row exactly fits.
  3. Select the row, then on the Home tab, in the Cells group, select Format > AutoFit Row Height .
  4. You can also specify an exact height as a number. The default row height is 15. To specify exact
      height:
         • Right-click a selected row, and then select Row Height... Enter the desired row height in the
            Dialog Box and click OK.
         • Alternately, on the Home tab, in the Cells group, select Format > Row Height... Enter the
            desired row height in the Dialog Box and click OK.
  5. You can also resize one or more ranges of rows by selecting the desired range(s) and using then
       using the same options as when resizing a single row.

  Windows Applications
                                             Excel Introduction                                Page 33
  Robert J Hancock
Inserting Columns or Rows

You will often need to insert one or more columns or rows.

Insert one or more columns or rows with one of several methods:
   1. Right click method
         • Select one or more columns or rows where you want the new
           column(s) or row(s) to be inserted.
         • Right click and select Insert.
   2. Ribbon method
         • Select one or more columns or rows where you want the new
           column(s) or row(s) to be inserted.
         • On the Home tab, in the Cells group, select Insert.
   3. Alternatively, if you want to insert only one column or row, you don’t
       have to select the entire column or row but you can click in any cell in
       the column or row.
         • Using the Right click method, the Insert Dialog Box pops-up
               • Select Entire column or Entire row and click OK
         • Using the Ribbon method, a drop down menu appears
               • Select Insert Sheet Columns or Insert Sheet Rows



  Windows Applications
                                              Excel Introduction                  Page 34
  Robert J Hancock
Deleting Columns or Rows

Delete one or more columns or rows with one of several methods:
     1. Right click method
           • Select the column(s) or row(s) to delete.
           • Right click and select Delete.
           • The selected column(s) or row(s) will be removed.
     2. Ribbon method
           • Select the column(s) or row(s) to delete.
           • On the Home tab, in the Cells group, click Delete.
           • The selected column(s) or row(s) will be removed.
     3. Alternatively, if you want to delete only one column or row, you don’t
         have to select the entire column or row but you can click in any cell in
         the column or row’s deletion point.
              • Using the Right click method, the Delete Dialog Box pops-up
                    • Select Entire column or Entire row and click OK
              • Using the Ribbon method, a drop down menu appears
                    • Select Delete Sheet Columns or Delete Sheet Rows




  Windows Applications
                                             Excel Introduction                     Page 35
  Robert J Hancock
Clearing Cells
Clearing a cell or range of cells is a different operation than deleting a cell or range of cells:
   • Deleting a cell removes the cell or range of cells from the spreadsheet .
   • Clearing a cell or range of cells’ removes the contents of the cell(s) but retains the respective
      cell(s) in the spreadsheet.

You can clear a cell or range of cells in several ways:
     1. Select the cell(s) and press Delete on the keyboard.
     2. Select the cell(s), Right click and then select Clear Contents.
     3. Select the cell(s), on the Home tab, in the Editing group, select the Clear icon, then select Clear
        Contents.

Clearing a cell's content doesn't clear its formatting. To clear formatting:
      1. Select cell(s)
      2. On the Home tab, in the Editing group, select the Clear icon, then select Clear Formats.

To simultaneously clear both a cell(s) contents and formatting:
      1. Select cell(s)
      2. On the Home tab, in the Editing group, select the Clear icon, then select Clear All.




  Windows Applications
                                              Excel Introduction                                  Page 36
  Robert J Hancock
 Formulas
 Excel's intrinsic value is the capability to "crunch numbers" using formulas and functions.

 A Formula is a mathematical equation that returns a result.
    • always begins with an equal sign
    • can include math operators, cell references, numbers, parenthesis
          + Addition
          - Subtraction
          * Multiplication
          / Division
          ^ Exponential
Practice:
   1. In a new worksheet, type 2 in cell A1 and 3 in cell A2.
   2. In cell A3, type =A1+A2 and press Enter.
   3. In cell A4, type =A1*A2 and press Enter
   4. Select cell A3 and observe the cell A3’s formula displays in the
       Function Bar.
   5. Select cell A4 and observe the cell A3’s formula displays in the
       Function Bar.
   6. Observe that cell A3 returns 5 (2+3) and cell A3 returns 6 (2*3)

   7. Select cell A1 and type 5 and press Enter
   8. Observe that cell A3 now returns 8 (5+3) and cell A4 now returns
      15 (5*3)


  Windows Applications
                                                   Excel Introduction                          Page 37
  Robert J Hancock
Order of Mathematical Operations and Grouping

When multiple math operators are used in the same formula, each operation is processed in a specific order as
follows:
    1. Any operations that are in parentheses, from left to right
    2. Exponentiation (^)
    3. Multiplication (*) and division (/)
                                                                    Note:
    4. Addition (+) and subtraction (-)
                                                                    I recommend always using parentheses
                                                                    to avoid unintended consequences.
Each of following examples returns a different result:
    =4+4/4^2               (returns 4.25)
    =(4+4/4)^2             (returns 25)
    =(4+4)/4^2             (returns .5)
    =((4+4)/4)^2           (returns 4)


 Practice:
    1. In a new worksheet
    2. Enter 1 in cell A1
    3. Enter 2 in cell A2
    4. Select A1:A2 and Auto Fill down through cell A6 (drag
        the Fill Handle to cell A6)
    5. Select cell A7 and enter a formula to summarize cells
        A1 through A6 (=A1+A2+A3+A4+A5+A6)
    6. Cell A7 returns 21


   Windows Applications
                                                  Excel Introduction                                     Page 38
   Robert J Hancock
Functions
A Function is a shortcut syntax of a formula to perform a more complex mathematical or logical calculation.

Example: If you wanted to sum the contents of cells A1 through A1000, it would be totally impractical to type the
formula = A1+A2+A3+A4+…………………………………………………………………+A1000. To simplify this operation, Excel
provides a function call SUM. In this example, one would simply type =SUM(A1:A1000) to accomplish the same
results.

A Function has these characteristics:
   • Begins with a predefined name
   • Always includes right and left parenthesis ()
   • Most require one or more arguments which are included inside the parenthesis and separated by commas

One or more Functions may be imbedded within a cell’s formula. Example:
  = A1+ROUNDUP(SUM(B1:B100),0)

Excel provides an extensive library of functions, but here’ a very small sample of commonly used functions:
   =SUM returns the sum of the numbers in a range
   =AVERAGE returns the average of the numbers in a range
   =COUNT returns the count of the number of cells containing numbers in a range
   =MIN returns the lowest number in a range
   =MAX returns the highest number in the range
   =TODAY returns today's date
   =NOW returns the current date and time
   =IF tests a specified condition and returns one result if the condition is True and another result if the condition
       is False
   Windows Applications
                                                    Excel Introduction                                         Page 39
   Robert J Hancock
 Practice using Functions
 Select Sheet 1, Practice.xlsx




In cell N2, type the function =sum(a2:m2) and press Enter > cell N2 should now return 3250

 Note:
 When typing alphabetic letters in formulas or functions, you can enter lower case letters . Excel will automatically
 change to capitalized letters




  Windows Applications
                                                   Excel Introduction                                       Page 40
  Robert J Hancock
  Using the insert function feature
  Since it’s impractical to memorize the syntax and arguments for the hundreds of Excel functions,
  Excel provides an Insert Function feature to assist you in finding the function’s syntax and arguments.

Practice Using Insert Function
                                                  Insert Function
In cell A11, use Excel’s Insert Function to
locate and insert the Sum function.
  • Select cell A11 and click on the
     Insert Function button in the
     Function Bar or on the Formulas
     tab, under the Function Library
     group, click on Insert Function.
  • Type Sum in Search for a function:
     box and click Go




   Windows Applications
                                              Excel Introduction                                Page 41
   Robert J Hancock
Practice Using Insert Function (continued)
The Insert Function dialog box now displays all
functions staring with Sum
  • Select Sum and click OK
  • The Function Arguments dialog box pops-up
  • In the Number 1 box, type the range b5:d5 and
     click OK




 Windows Applications
                                              Excel Introduction   Page 42
 Robert J Hancock
 Finding and inserting functions using the assist drop down menu:
 By either knowing or assuming you know the desired function’s syntax, start by typing = followed by the
 functions presumed syntax. Excel will display all functions that match your input. Select the function you
 intended and read the associated message to verify its purpose. Once you’ve determined that you’ve
 selected the intended function, double click the desired function in the list. Then either type the required
 arguments or drag the mouse over the required arguments and press Enter.




                                                                         1.   In this example, select cell F5 and type
                                                                              =sum
                                                                         2.   Double click the SUM function
                                                                         3.   Drag the mouse cursor over cells D5 and
                                                                              E5 (this enters D5:E5 to the SUM
                                                                              function
                                                                         4.   Press Enter




Windows Applications
                                                Excel Introduction                                        Page 43
Robert J Hancock
Copying Formulas and Functions Using Relative References
When copying a formula or function to another row or column, Excel’s default mode is to copy the formula
or function by adjusting the formulas’ argument references to reflect the row or column being copied to.
This is referred to relative referencing.

Practice:
• In cell B3, type 101
• In cell B4 type 102
• Select cells B3:B4 and Auto Fill down through cell
   B10




 Windows Applications
                                                Excel Introduction                                     Page 44
 Robert J Hancock
Copying Formulas and Functions Using Relative References (continued)

• Select cells A3:B10 and Auto Fill across column M
      • Observe that row 3 increments by 1, row 4 increments by 2, row 5 increments by 5, etc…….
• In cell A11, enter function to sum cell A2 through A10 (returns 900)
• Use the Fill Handle to copy cell A11 across the columns to cell M1
• Use the Fill Handle to copy cell N2 down through the rows to cell N11




Windows Applications
                                              Excel Introduction                                   Page 45
Robert J Hancock
Copying Formulas and Functions Using Relative References (continued)

•   Press Ctrl+`(accent) to display all formulas and functions in their respective cells
•   Observe that each column on row 11 sums the respective columns cells using Relative Referencing
•   Observe that each row in column N sums the respective rows cells using Relative Referencing
•   Press Ctrl+`(accent) to display values




Windows Applications
                                               Excel Introduction                                     Page 46
Robert J Hancock
Copying Formulas and Functions Using Absolute References
Using Absolute Reference, the cell reference(s) don't change when you move or copy a formula or function.

A dollar sign $ is place in front of a column and/or row reference to lock the respective reference when moving or
copying the cell.

Example:
   • A1 is a relative reference to cell A1
   • $A$1 is an absolute reference to A1, which locks both the column (A) and the row (1) when copied or moved
   • A$1 is an absolute reference to A1 which does not lock the column but locks the row when copied or moved
   • $A1 is an absolute reference to A1 which lock s the column but does not lock the row when copied or moved

Note:
When creating an Absolute Reference, after entering the reference (assume it is A1 for this example) you can toggle
the F4 key to toggle between A1 > $A$1 > A$1 > $A1 > A1…….




 Windows Applications
                                                 Excel Introduction                                       Page 47
 Robert J Hancock
Copying Formulas and Functions Using Absolute References (continued)

Practice copying using Absolute References using Sheet 1, Practice.xlsx:
• In cell P1, enter .0625 (an assumed tax rate)
• In cell A12, enter the formula =A11*$P1 (lock s the column when copying)
• Use the Fill Handle and copy cell A12 through cell N12
• In cell O2, enter the formula =N2*P$1 (locks the row when copying)
• Use the Fill Handle and copy cell O2 through cell O11




 Windows Applications
                                              Excel Introduction             Page 48
 Robert J Hancock
Copying Formulas and Functions Using Absolute References (continued)

•     Press Ctrl+`(accent) to display all formulas and functions in their respective cells
•     Observe that each column on row 12 locks column P using Absolute Referencing
•     Observe that each row in column O locks row 1 using Absolute Referencing
•     Note: In this example, I would normally use $P$1 when copying formulas across the columns and rows.
      However, there are many cases when you will only want to lock only the column and allow the row to change
      and visa-versa.

• Press Ctrl+1(accent) to display values




    Windows Applications
                                                  Excel Introduction                                    Page 49
    Robert J Hancock
Text Formatting

Excel 2007 provides the same basic text formatting controls as Microsoft Word 2007 and
Microsoft PowerPoint 2007.

The Font group under the Home tab provides the necessary controls to select the font, size, color,
shading, and borders for your text and cells as follows:
                           Font Size        Increase Font Size

                                                          Decrease Font Size
     Font

     Bold
                                                                Font Color

              Italic
                                                        Format Cells dialog box launcher (see Note)
                   Underline
                            Borders        Fill Color
 Note: You can also launch the Format Cells dialog box by right clicking on a cell or range of cells
Windows Applications
                                           Excel Introduction                                  Page 50
Robert J Hancock
Aligning Cell Contents
Excel 2007 provides the capability to align the contents of cells in the horizontal and vertical
dimensions.
  • Horizontally: cell contents can be aligned to the left, the middle, or right of the cell(s)
  • Vertically: cell contents can be aligned to the top, middle, or the bottom of the cell(s)

Default cell alignment:
  • Text: horizontally aligned left; vertically aligned bottom
  • Numbers: horizontally aligned right; vertically aligned bottom

You can selectively change the alignment for any cell or range of cells.

Vertical alignment becomes an issue only if the row height is larger than it needs to be, or when a
cell contains fewer lines than other cells in that same row.




Windows Applications
                                             Excel Introduction                                    Page 51
Robert J Hancock
Aligning Cell Contents (continued)

You can change cell alignment with the Alignment group under the Home tab as follows:


              Vertical alignment       Orientation menu
                                                                Wrap Text




                                                                 Merge & Center menu




Horizontal alignment             Increase indentation

                        Decrease indentation


 Windows Applications
                                          Excel Introduction                            Page 52
 Robert J Hancock
Aligning Cell Contents (continued)

You can also change cell alignment using the Alignment tab on the Format Cells dialog box.

You can launch the Format Cells dialog box either by right clicking on a cell or range of cells and
selecting Format Cells…. , or by clicking on the Format Cells dialog box launcher in the Font
group under the Home tab and selecting.




Windows Applications
                                            Excel Introduction                                  Page 53
Robert J Hancock
Cell Borders

The light gray lines that divide rows and columns on the video display are called gridlines. By
default, they do not print, The gridlines can be turned on/off for both onscreen viewing and
printing. To control these, use the check boxes in the Sheet Options group under the Page Layout
tab.

Using border formatting , you can create borders that that print in any thickness, style or color.

You can apply border formatting using the Font group under the Home tab or launching the
Format Cells dialog box and selecting the Border tab.

                           Format Cells dialog box
                           You have more flexibility in
Font Group                 creating compound borders
                           using this method than by
                           using the Font group method.




       Cell Borders


Windows Applications
                                            Excel Introduction                                  Page 54
Robert J Hancock
Cell Shading

By default, cells have no shading. Excel 2007 provides the capability to add any background color
to cells and to select from a variety of patterned backgrounds.

To apply quick and simple shading, use the Fill Color button on Font group under the Home tab .
Click the arrow to open a palette of colors to choose from. Select from a palette of standard
colors or select More Colors to open the Colors dialog box. Select either the Standard tab or the
Custom tab for more options.
                                 Standard tab                         Custom tab


Font Group        Fill Color




Windows Applications
                                           Excel Introduction                                Page 55
Robert J Hancock
  Cell Shading (continued)

  Alternatively, you can apply cell shading by launching the Format Cells dialog box and selecting
  the Fill tab. Select from a palette of standard colors or select More Colors to open the Colors
  dialog box. Select either the Standard tab or the Custom tab for more options.



Format Cells dialog box
                                                                            Click Pattern Style for
                                                                            additional fill pattern
                                                                            options



 Click Fill Effects for
 additional gradient
 shading effects




   Windows Applications
                                             Excel Introduction                                       Page 56
   Robert J Hancock
Number Formatting

Excel provides the capability to specify how numbers are displayed:
   • Specify the number of decimal places (e.g., 295.037, 295.04, 295.0, 295, etc.)
   • Negative numbers can be expressed in a variety of ways (e.g., -1234.01, (1234.01), etc.)
   • Include international currency symbols (e.g., $)
   • Include commas (e.g., 1,234,567)
   • Display percentages using a percentage sign (e.g., %2.75)
   • Using scientific format (e.g., 1.234567E+06)
   • Dates are numbers that can be displayed in a variety of formats (e.g., 3/14, 3/14/2001,
     3/14/01, 14-Mar-01, etc.)
   • Time is a number that can be displayed in a variety of formats (e.g., 1:30 PM, 13:30, 3/14/01
     1:30 PM, 3/14/01 13:30, etc.)

To apply the most popular number formatting options, use the Number group under the Home
tab.                                                  Number Format
                  Currency Style
                                                                Increase decimal points


                                                            Decrease decimal points
                   Percent Style   Comma Style
                                                            Format Cells dialog box launcher
Windows Applications
                                           Excel Introduction                                  Page 57
Robert J Hancock
Number Formatting (continued)

Alternatively, to apply number formatting, launch the Format Cells dialog box and select the
Number tab. To launch the Format Cells dialog box, either click the Format Cells dialog box
launcher in Number group under the Home tab or right click on a cell or group of cells and select
Format Cells... , then click on the Number tab. Select the formatting options as desired.




Windows Applications
                                           Excel Introduction                                Page 58
Robert J Hancock
Page Setup and Printing Options

An extensive array of settings and options are provided to prepare a worksheet for printing.

Depending upon a number of factors, a worksheet may or may not fit on a single sheet of printed
paper. Excel provides the flexibility to select the sheet’s size (i.e., letter, legal, A4, etc.), select the
sheet’s orientation (portrait or landscape), adjust the sheet’s margins, adjust the sheet’s effective
font size, select the print area (all or only a portion of a worksheet), set the location and number
of break points when spanning multiple sheets , create headers and footers, include row and
column headings on each sheet, and much more.

The number of settings and options is too extensive to cover in a single class session. To master
the available settings and options, you'll need to experiment on your own.




 Windows Applications
                                               Excel Introduction                                      Page 59
 Robert J Hancock
Page Setup and Printing Options (continued)

The Page Setup group under the Page Layout tab provides the menus for setting the various print
options.

                                                                            Page Setup dialog box
                                                                            launcher

Margins: Select Normal, Wide, or Narrow or select Custom Margins to enter your own values.
Orientation: Select Portrait or Landscape.
Size: Select a paper size; this setting determines where Excel shows page breaks in Print Preview.
Print Area: Select a range of cells, and then select Print Area. Set Print Area to print only a certain part
    of the worksheet. Use Print Area, Clear Print Area to reset the worksheet’s print area.
Breaks: Set or remove hard page breaks here.
Background: Specify a graphic to use as a background behind the cells.
Print Titles: Click this button to open the Sheet tab of the Page Setup dialog box, in which you can select
    certain rows and/or columns to repeat on every page of a printout.

Windows Applications
                                             Excel Introduction                                  Page 60
Robert J Hancock
Scale to Fit

In the Scale to Fit group under the Page Layout tab, you can set Excel to automatically shrink a
printout's font to print a worksheet or Print Area on a specified number of pages, or to print at a
certain percentage of the original font size.




                                                                      Page Setup dialog box
                                                                      launcher




 Windows Applications
                                            Excel Introduction                                 Page 61
 Robert J Hancock
Print Preview
All Page Setup options are also available using Print Preview
Open Practice.xlsx, Sheet 1
Click the Microsoft Office Button, flyover Print, and select Print Preview




Windows Applications
                                           Excel Introduction                Page 62
Robert J Hancock
Print Preview (continues)
Before changing any Page Setup options, this is how the worksheet would print.

1.   In this example, the default Portrait Orientation and font size don’t provide sufficient paper width to print the
     entire worksheet on a single page.

2.   Observe that the Next Page icon is not grayed out (click to see next page).

3.   Click Page Setup to
     change the default
     settings




 Windows Applications
                                                  Excel Introduction                                         Page 63
 Robert J Hancock
Print Preview (continues)
The Page Setup dialog box pops-up.

By trial-and-error, try different settings until you achieve the desired results.
   • Try changing the paper orientation by clicking on the Landscape radio button, then click OK




Windows Applications
                                          Excel Introduction                                Page 64
Robert J Hancock
Print Preview (continues)
• In this example, this worked just fine. The entire worksheet fits on a single printed page.
• In other cases, you can return to Page Set and try other options:
     • Scale the font size
     • Change the print margins
     • Etc.




Windows Applications
                                           Excel Introduction                                   Page 65
Robert J Hancock
Print Preview (continues)
To print Gridlines, click Page Setup and select the Sheet tab




Click the Gridlines check box

Click OK




Windows Applications
                                            Excel Introduction   Page 66
Robert J Hancock
Print Preview (continues)
This is what the worksheet looks like with gridlines printed.




Windows Applications
                                            Excel Introduction   Page 67
Robert J Hancock
Print Preview (continues)
To add a Header, click Page Setup and select the Header/Footer tab

In this example, select the file
name Practice.xlsx as the Header

Click the header down arrow and
select the file name.


Click OK




Windows Applications
                                         Excel Introduction          Page 68
Robert J Hancock
Print Preview (continues)
Worksheet with file name as Header




Windows Applications
                                     Excel Introduction   Page 69
Robert J Hancock
Print Preview (continues)
1.   To add a custom Footer, click Page Setup and select the Header/Footer tab

2.   Click the Custom Footer button




3.   The Footer dialog box opens.

4.   Click in the Left section window
     pane and type text and/or click
     on the desired icons above to
     insert the respective
     information.
                                                                                 Flyover each icon
5.   Repeat for the Center section                                               to see their
     and Right section as desired.                                               respective inserts

6.   Click OK, then click OK on the
     Page Setup dialog box
 Windows Applications
                                                Excel Introduction                       Page 70
 Robert J Hancock
Print Preview (continues)
In this example, I’ve created a custom Footer with the Date in the Left section, the sheet name in the Center
section and the Page# of #Pages in the Right section.




                                                                                                          typed of




   With the cursor in the Left section,   With the cursor in the Center       With the cursor in the Right section, I first
   I clicked on the Insert Date icon      section, I clicked on the Insert    clicked on the Insert Page Number icon,
                                          Sheet Name icon                     pressed the spacebar and typed of, pressed
                                                                              the spacebar and clicked on the Insert
                                                                              Number of Pages icon.




 Windows Applications
                                                         Excel Introduction                                                   Page 71
 Robert J Hancock
                       Final Page Setup




Windows Applications
                           Excel Introduction   Page 72
Robert J Hancock
                           Excel 2007 Introduction
Resources: There are many Excel tutorials available online. Google to find them.

Here are links to a few that I recommend:

http://office.microsoft.com/en-us/training/HA102189871033.aspx
   • www.microsoft.com > Training & Events > Office Online Training > Excel 2007

http:// www.functionx.com/excel/index.htm

http:// www.baycongroup.com/el0.htm


To receive a copy of the Excel Introduction PowerPoint presentation, email me at:
rjhancock@gmail.com

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:17
posted:6/25/2011
language:English
pages:73