MS Excel

Document Sample
MS Excel Powered By Docstoc
                                                                                Vol - I

                                 CHAPTER - 17

                           MICROSOFT EXCEL


1.     In olden days, human kind crunched numbers with such Primitive tools as
pencils, paper and hand held calculators. Our ancestors actually did financial
planning by writing the numbers that they punched up on their calculators onto long
screen sheets of paper. These green sheets, because they were much wider than
they were tall, became known as spreadsheets. The spreadsheet use fine grid line to
divide the large sheet into a series of columns and rows. An electronic spreadsheet
program like Excel pays homage to this glorious past by presenting with a facsimile
of the old green sheet on the computer screen in the workbook that appears when
we start the program.

Starting Excel

2.    Several ways to start Microsoft Excel are:-

      (a)    Click on the Start button, Choose Programs then Click on Microsoft

      (b)   From Windows Explorer, double Click on any Excel file. Excel will start
      and the file will be opened.

      (c)    Double Click the Excel short cut icon present on the desktop.

Exiting Excel

3.    There are several ways to exit Excel:-

      (a)    Click on the X button in the upper right corner of the Excel window.

      (b)    Choose File from the top menu bar then click Exit.


Chapter 17                                                                    Vol - I

Work Book & Work Sheet

4.    An Excel document is called a workbook. Workbook is a container for one or
more worksheets. Here are something to keep in mind:-

       (a)   Think of a workbook as a binder.

       (b)   Think of every worksheet as a page in the binder.

Opening Files

       (a)   Click File and then click Open.

       (b)   Click the Open button in the Standard tool bar.

Saving Files

5.     Click File and then click Save. Give some name to the file and then press Ok.

Closing a File or Workbook

6.    Choose File and then click Close from the top menu bar to close the active
workbook. If there are unsaved changes, you will be asked if you want to save them.

Renaming a Worksheet

7.    Work sheet tabs become a very useful interface for navigating a workbook
when sheets have names like Sales Analysis and Forecast instead of sheet 1 and
sheet 2. Do one of the following for renaming:

       (a)   Choose Format click Sheet and then click Rename.

       (b)   Right Click on a sheet tab, then choose Rename from the short cut


Chapter 17                                                                     Vol - I

       (c)   Double click on the sheet tab.

       Note. Each of these procedures will cause the sheet on the tab to be
       highlighted. Simply type the new name, then press Enter.


8.    A worksheet consists of vertical columns and rows. A cell is the area where a
column and a row meet. The cell is addressed by Column-Row method like A1, D8.
A1 means first cell in column A and D8 means cell no 8 in column D.

Types of Cell Data

9.   There are four distinct types of data that can reside in a cell. They are text,
numbers, logical, Date/Time and error values.

       (a)   Text. Text in a cell can include any combination of letters, numbers
       and keyboard symbols.

       (b)   Numbers. The most common thing that is done with spreadsheet
       programs is numeric calculations. Here are some important things to
       understand about the way Excel treat numbers:

             (i)    A number may be displayed using commas, scientific notation or
             one of may built in numeric formats.

             (ii)   Dates and times are number but with special formatting.

             (iii)  When an unformatted number does not fit in a cell, it is displayed
             in scientific notation.

             (iv)    When a formatted number does not fit in a cell, number signs (#
             # # # ) are displayed.

       (c)   Logical.     Logical values TRUE and FALSE can enter into the cells.

       (d)   Date/Time. Store the date and time values.

       (e)   Error. Formula errors are displayed as a value.


Chapter 17                                                                          Vol - I

Entering Data into a Cell

10.    To enter data into a cell, simply do the following:

       (a)    Select the cell by clicking it.

       (b)    Type numbers, text or a combination of both.

       (c)    Press Enter

Editing a Cell

11.     There are two places where editing can be done, in the formula bar or in the
cell itself:-

       (a)     Formula Bar.          Select the cell then click in the formula bar. The
       insertion point is placed at the end of the cell contents.

       (b)   In Cell.        Double click on the cell or select the cell and press F2.
       The insertion point is placed at the end of the cell contents

Undoing Cell Entries and Edits

       (a)   If you haven’t yet pressed enter while entering or editing changes can
       be cancelled by pressing Esc or click the cancel button.

       (b)    If you are already pressed enter choose Edit and then press Undo or
       simply click the Undo button.

Clearing a Cell

       (a)  For clearing a cell choose Edit and then press Delete from the top of
       menu bar.

       (b)    Delete key can also use for clearing a cell.


Chapter 17                                                                      Vol - I

Selecting Cells

12.   Selecting a range with the mouse. Here’s how to select a range of cells by

      (a)    Click on a cell at a corner of the range, but don’t release the mouse.

      (b)    Drag the mouse to select the range.

      (c)    Once the desired range is selected, release the mouse button.

13.   Selecting a range with keyboard are:-

             Keystroke                         Selects

      (a)    Ctrl + Spacebar                   To select entire column

      (b)    Shift + Spacebar                  To select entire row

      (c)    Ctrl + Shift + Spacebar           To select entire worksheet

      (d)    End, Shift + Arrow keys           To extends selection to end of data

      (e)    End, Shift + Home                 To extends selection to lower right
                                               corner of worksheet

      (f)    End, Shift + Enter                To Extends selections to last cell in
                                               Current row of data block

14.   Easiest way of moving and copying cells using drag and drop is given:-

      (a)    Select the cells you want to move.

      (b)  Point to an outside border of the selected range using the mouse. The
      mouse pointer will turn into an arrow.

      (c)   Click on the selection, drag the cells to a new location, and then release
      the mouse button.

      (d)    The cells will now be positioned in the new location.


Chapter 17                                                                       Vol - I

Inserting and Deleting Cells

15.   To insert cells, select the cells where you want to perform the insertion and
use the following methods:-

       (a)     Right click on the selection to display the short cut menu, and choose

       (b)   From the insert menu do one of the followings

             (i)    Choose the Rows command to insert entire rows.

             (ii)   Choose the columns command to insert entire columns.

             (iii)  Choose the cells command to insert a range of cells. The insert
             dialog box is displayed, asking how the insertion should occur.

       (c)   For deleting the cells, select the cells you want to delete.

             (i)    Choose Edit > Delete from the top of menu bar.

             Note A. If you select an entire row or column, the row or column is
                  deleted immediately.

                    B. If you select a single cell or group of single cells, then dialog
                    box (Delete dialog box) appeared and going the opportunity what
                    to delete.

Changing Row Height and Column Width

16.    Select any cell(s) in the column, you want to resize, then choose Format
Column from the top menu bar. The submenu appears, you can notice that the row
height works just like column width.

Formulas and Functions

17.     Excel offers its users a wealth of options for charting and calculating the data
in worksheets. In this chapter we will learn to use cell references, external references
and file links.


Chapter 17                                                                          Vol - I

18.    A formula is essentially a sequence of values and operators that begins with
an equal sign (=) and produces a new value. Excel comes with several hundred built
in formulas, called functions, which are designed to perform many different kinds of

19.    Formulas in Excel share some basic properties:-

       (a)    All formulas begin with an equal sign.

       (b)    After a formula is entered, the resulting value is displayed in the cell.

       (c)    When a cell containing a formula is selected the under lying formula is
       displayed in the formula bar.

Entering a Formula Manually

20.    To enter a formula into a cell simply select desire cell and start typing formula.
The first character must be an equal sign. For example:-

       (a)    Select cell A1 on a blank worksheet.

       (b)    Enter = 1+2 then press enter. The resulting value, 3 appears in the cell.

Order of Calculating

21.    It is very common for formulas to include more than one operator. Excel
performs calculation in a specific order. Order of evaluating operations is given in
following table:-

               Table 17.1.    Evaluating Operations
                Order          Operator     Function
                1                  -        Negation
                2                 %         Percent
                3                 ^         Exponentiation
                4              * and /      Multiplication + division
                5              + and -      Addition + Subtraction
                6                  &        Joining text
                7              = < >,       Comparison


Chapter 17                                                                           Vol - I

Joining Text

22.   The ampersand character (&) joins text (a process referred to as
concatenation). For instance, if you put ABC in cell A2 and XYZ in cell A2, then use
ampersand character (&) to join the text like this = A1&A2 in any cell the result will be
ABCXYZ displayed after pressing Enter or leaving that cell.

Freezing Values

23.   There are times when you will want to “freeze” a range of cells by replacing
formulas with values. For instance, you might print a report that is distributed to other
people in your organization and you don’t want to show them the exact formulas.
Follows this procedure to Select the cells you want to freeze.

       (a)    Choose Edit and click Copy

       (b)    Choose Edit and then click Paste Special.

       (c)   Select the values option from the Paste Special dialog box, then click
       OK. All formulas in the range are replaced with constant values.

Cell References

24.    A cell reference is the cell’s address and it can be either the absolute location
of a specific worksheet or simply the location of a cell relative to a specific cell. To get
the most out of functions, you can use cell references, to incorporate variables into
formulas, by using cell references you can vary the output of formulas depending on
the value you place in the referenced cells, as demonstrated in the following table:-

Table 17.2 Examples
 Formul       Explanation
 =A1*2           Multiplies the value in cell A1 by 2.
 =A1*B1          Multiplies the value in cell A1 by the value in cell B1.
 =”ABC” & A1 Concatenate (joins) the characters ABC and the value in the cell A1.
 =A1 &B1         Concatenates the value in cell A1 and the value in cell B1.


Chapter 17                                                                         Vol - I

25.    Try this example on a blank worksheet to see how cell references work.

       (a)    Enter the number 2 in cell A1.

       (b)    Enter the number 4 in cell A2.

       (c)    Enter the formula = A1+A2 in cell A3 ( The returned is 6 ).

       (d)  Change the value in A1 to 8, and watch the value in A3 change
       automatically to 12.

Absolute References

26.   It refers to a specific cell or cells. In this analogy a specific home address,
such as House No 14. A dollar sign in front of the cell coordinates denotes an
absolute reference:-

       (a)    $A$1 is an absolute reference to cell A1.

       (b)    $B$2 is an absolute reference to cell B2.

27.    An absolute reference does not change when copied to another cell. The
following exercise will demonstrate this:

       (a)    Enter =$A$1 into cell B1.

       (b)    Copy and paste cell B1to B2.

       (c)    The formula in cell B2 is unchanged, it still reads =$A$1

Relative References

28.    It refers to cell (s) relative to a given position, such as “ go one street down
and two houses over”. A relative reference is denoted simply by the column letter
followed by the row number refer to cells relative to cell relative to the cell containing
the formula. Try this example:-

       (a)   Enter=A1 into cell B1. This gives B1 the same content as A1, its
       neighbor directly to the left.

       (b)    Copy and paste cell B1 to B2.


Chapter 17                                                                   Vol - I

        (c)   The formula in cell B2 automatically changes; Excel has adjusted it to
        read =A2, giving B2 the same content once again as neighbor directly to the

Mixed Reference

29.     In a mixed reference, one of the coordinates is absolute, but the other is
relative. “ Turn right on St. Tota Laar. F/O Ashfaq lives three houses down.”

Formatting Work Sheet

30.     Formatting of worksheet is of very important and beneficial.

        (a)   You can highlight important information with formatting.

        (b)   You can use simple formatting procedures to greatly enhance overall
        readability of reports.

        (c)  You can use styles to easily create a consistent look and consistency
        enhances professionalism.

        (d)    You can use conditional formatting to high light information when
        conditions or values change.

Working with Fonts

31.   One of the most basic ways to enhance the appearance and usefulness of the
worksheet in Excel the font property encompasses several aspects of the character:

        (a)   Typeface, such Times New Roman, Arial and Courier.

        (b)   Size measured in points.

        (c)   Boldface and for Italic.

        (d)   Color.

        (e)   Underline.

        (f)   Special affection strike through, superscript and subscript.


Chapter 17                                                                    Vol - I

Applying Font Formats

32.    Applying special font formatting is a simple procedure. As with all cell level
formatting commands, the first step is to select the range you wish to format. Choose
Format > cell to display the format cell dialog box, then click on the font tab.

Printing Worksheet

33.    Despite trends towards the paperless office, hard copies of business reports
and printed worksheets will likely be office fixtures for the foreseeable future.

Determining the Look of Printed Pages

34.    The basic layout of the printed pages can be changed on page tab in the Page

Adjusting Margins

35.   Adjusting the margins can be done using following ways:-

      (a)    The margins can set and also the position of headers, footers and print
      areas from the Margins tab in the page Setup dialog box.

      (b)   The preview area on the dialog box provides a visual illustration of how
      the margins are set.

Creating Page Headers and Footers

36.    You enter and format headers and footers on header / footer tab in the page



Shared By:
Description: How to work with MS Excel.