ADD

Document Sample
ADD Powered By Docstoc
					BIM192 Additional Topics -- Linked Index
  From any Sheet press HOME + Enter Keys to return to this Index
This module includes a selection of techniques for managing Excel and extracting information from worksheets.
MENU BAR ITEMS
    FILE        EDIT       VIEW   INSERT        FORMAT                       TOOLS          DATA      WINDOW
Page Setup Find          Comment Comment Cells -- Protection              Protection     Sort       Freeze Pane
Print Area Replace                          Sheet - Hide
                                 Edit Comment                             Goal Seek      Filter
Print      Go To Special         Chart      Conditional F'mat             Scenarios      Validation
           Paste Special         Name                                     Auditing
                                                                          Options - View
                                                                          Solver
                                                                                        Histogram
                                                                          Data Analysis ---
Menu Items -- Alphabetical List
Item                       Menu        Function
Auditing                   Tools       Traces Precedents (data-from cells) and Dependents (data used-by cells), to
                                       assist in understanding and/or debugging a worksheet.
Cells -- Protection         Format     All cells are in "Locked Mode" by default. This command lets you change the
                                       default settings from locked to unlocked. Cell contents may also be hidden.
                                       Protection Status must then be activated from the Tools Menu.
Chart                       Insert     Starts the Chart Wizard, which guides you through the steps for creating a new
                                       chart or modifying an existing chart.
Comment                     Insert     Allows you to insert a comment as a reminder, or as help for other users.
Conditional F'mat           Format     Applies formats to cells that meet criteria based on values or formulas you specify.
Data Analysis               Tools      Opens a menu with 14 Statistical analysis tools, including Histogram.
Comment                     View       Displays all comments on a sheet.
Filter                      Data       Allows you to select rows to display in a list.
Find                        Edit       Searches selected cells or sheets for the characters you specify, and selects the
                                       first cell that contains those characters.
Freeze Pane                 Window     Keeps Column/Row Titles visible while scrolling.
Goal Seek                   Tools      Adjusts the value in a specified cell until a formula that is dependent on that cell
                                       reaches a target value.
Gridlines                   Options    Select "View" tab … then check "Gridlines" to turn Gridlines on or off.
Name                        Insert     Creates a name for a cell, range, or constant or computed value that you can use
                                       to refer to the cell, range, or value.
Paste Special               Edit       Permits selective Pasting of copied cells: e.g. Formulas, Values, Comments.
Page Setup                  File       Opens Dialog box to select Print settings.
Print                       File       Starts Print request
Print Area                  File       Defines the selected range as the print area, which is the only portion of the
                                       worksheet that will be printed.
Protection                  Tools      Prevents changes to a sheet, except to unlocked cells.
Replace                     Edit       Searches for and replaces specified text, formatting, footnotes, endnotes, or
                                       comment marks in the active document.
Scenarios                   Tools      Creates and saves scenarios, which are sets of data you can use to view the
                                       results of what-if analyses.
Sheet - Hide                Format     Hides the active sheet. The sheet remains open and accessible to other sheets,
                                       but it is not visible. You can't hide the only visible sheet in a workbook.
Sort                        Data       Arranges the information in rows or lists alphabetically, numerically, or by date.
Validation                  Data       Defines what data is valid for individual cells or cell ranges; restricts the data entry
                                       to a particular type, such as whole numbers, decimal numbers, or text; and sets
                                       limits on the valid entries.
Zero Values                 Options    Select "View" tab … then check "Zero Values" to turn zero display on or off.
Other Features
Renaming Sheets             Right-click Sheet Tab and select Rename option.
Status Bar Statistics       Select (highlight) data, then Right-click Status Bar statistics box.
Toolbar Quick Sorts         Select (highlight) data to be sorted, then Click AZ or ZA toolbar icon.
INDEXProtection -- A 2-Stage process
  .
INDEX
INDEX
  .                                   Projected GPA Status -- End of THIS Semester              Format CELLS / Prote
  .
INDEX You MUST enter data in                          Student's Name:
INDEX the yellow cells, for each
  .                                       This semester Expected GPA:
                                                                                                This sheet is presently i
       course you are taking.                                                                   protection status. This
  .                                               Status this semester:                         Cells are in LOCKED m
  .               TABLE-1: Courses Registered THIS Semester                                     in HIDDEN mode.
  .   Course                       Term      Expected      Grade Point    Course    Quality
  .    Code    Course Name         Mark     Letter Grade   Equivalent     Credits   Points      Exercise:
  .                                                               0.00                   0.00
  .
INDEX                                                             0.00                   0.00   1. Unlock
  .
INDEX                                                             0.00                   0.00
                                                                                                that data may be entere
  .
INDEX                                                             0.00                   0.00
                                                                                                menu
  .
INDEX                                                             0.00                   0.00
  .
INDEX                                                             0.00                   0.00
INDEX
  .                                                               0.00                   0.00   2. Hide
INDEX
  .                                                               0.00                   0.00   Formulas / Functions wi
INDEX
  .                                                               0.00                   0.00   shown. FORMAT
  .
INDEX                                                             0.00                   0.00
  .
INDEX                                                             0.00                   0.00   3. Protect
  .
INDEX                                                             0.00                   0.00   menu
  .
INDEX
  .
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX   GPE & LETTER GRADE TABLE
                         Letter   Grade Point
           Term Mark
                         Grade    Equivalent
INDEX
INDEX          0           0         0.0
INDEX         49          F          0.0                    THIS IS A LOOKUP TABLE ONLY
INDEX         50          D-         0.7                 NO DATA IS ENTERED INTO THIS TABLE
INDEX         55          D          1.0                           BY THE STUDENT
INDEX         60          D+         1.3
INDEX         65          C-         1.7                This Table shows the Letter Grade that is awarded
INDEX         70          C          2.0                   based on the Term Mark the student earns.
INDEX         75          C+         2.3
INDEX         80          B-         2.7                It also shows the Grade Point Equivalent (GPE) of
INDEX         84          B          3.0                                 the Letter Grade.
INDEX         88          B+         3.3
INDEX         92          A-         3.7
INDEX         96          A          4.0
INDEX
INDEX
INDEX                  This is the end of this Sheet.
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
Format CELLS / Protect SHEET
This sheet is presently in DEFAULT
protection status. This means, all
Cells are in LOCKED mode but not
in HIDDEN mode.

Exercise:

   Unlock the YELLOW cells, so
that data may be entered. FORMAT


   Hide the BLUE cells, so that the
Formulas / Functions will not be
shown. FORMAT menu

   Protect the Sheet. TOOLS
P TABLE ONLY
D INTO THIS TABLE
TUDENT

etter Grade that is awarded
Mark the student earns.

 Point Equivalent (GPE) of
INDEXData Validation at INPUT time
  .
INDEX
INDEX
  .                                      Projected GPA Status -- End of THIS Semester
  .
INDEX You MUST enter data in                           Student's Name:
INDEX the yellow cells, for each
  .                                        This semester Expected GPA:                         Data Validation
       course you are taking.
  .                                                Status this semester:                       To help users avoid Da
  .
                                                                                               Exercise:
  .                      Courses Registered THIS Semester
  .   Course                        Term      Expected      Grade Point    Course    Quality
                                                                                               1. Apply Input
  .    Code    Course Name          Mark     Letter Grade   Equivalent     Credits   Points
INDEX
  .
                                                                                               that data entered to Co
INDEX
  .                                                                                            Letter Grade (e.g. A
INDEX
  .                                                                                            grades, but A+ and E a
INDEX
  .                                                                                            1.2 Use an
INDEX
  .                                                                                            data entry.
  .
INDEX
  .
INDEX                                                                                          2. Apply Input
  .
INDEX                                                                                          that data entered to Co
  .
INDEX
                                                                                               integer between 1 and
  .
INDEX
INDEX
  .                                                                                            have credits greater tha
  .
INDEX
  .
INDEX
  .
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX                              GPE & LETTER GRADE TABLE
                                    Term                    Grade Point
                                             Letter Grade
                                    Mark                    Equivalent
INDEX
INDEX
INDEX                               49           F
INDEX                               50           D-            0.7
INDEX                               55           D             1.0
INDEX                               60           D+            1.3
INDEX                               65           C-            1.7
INDEX                               70           C             2.0
INDEX                               75           C+            2.3
INDEX                               80           B-            2.7
INDEX                               84           B             3.0
INDEX                               88           B+            3.3
INDEX                               92           A-            3.7
INDEX                               96           A             4.0
INDEX
INDEX
INDEX   This is the end of this Sheet.
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
Data Validation
To help users avoid Data Entry errors.
 Exercise:

 1. Apply Input Validation to ensure
 that data entered to Column F is a valid
 Letter Grade (e.g. A- and F are valid
 grades, but A+ and E are not valid).
 1.2 Use an In-cell Dropdown to assist
 data entry.

 2. Apply Input Validation to ensure
 that data entered to Column H is an
 integer between 1 and 6 (No courses
 have credits greater than 6).
   Conditional Formats -- To highlight Important Information
INDEX
  .
INDEX
INDEX
  .      Projected GPA Status -- End of THIS Semester    Exercise
  .
INDEX                   Student's Name:    Ali Anyoglu   1. Identify all the Conditional Formats used in Cell F6
INDEX
  .         This semester Expected GPA:   3.00           2. In Cell F4 apply the same Conditional Formats so that
 .                Status this semester:    High Honor       the student's name is the same color as the Status.
 . Press [F9] Key to change GPA
 .


INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
 mats used in Cell F6
nditional Formats so that
me color as the Status.
 Tools -- Options Menu
INDEX
INDEX
  Item
INDEXNo QuantityPrice        Q*P
   A123
INDEX         3    1,000,000  3,000,000
   A124
INDEX         6    1,500,000  9,000,000
   A125      34      250,000  8,500,000
   A126       0    4,000,000          0
   A127       0    3,750,000          0
   A128       0    2,250,000          0
   A129       0    1,750,000          0
INDEX
  The
INDEX Options Menu
  - 8
INDEX Tabs
  - c.
INDEX 100 choices
INDEX
  View
INDEX Tab                    Exercises
  - Gridlines
INDEX                        Turn OFF
  - Zero Values
INDEX                        Turn OFF
  - Row
INDEX and Column HeadersTurn OFF
INDEX
  Edit
INDEX Tab
                             If
  - Move Selection after Enter ON then Turn OFF … If OFF leave it OFF
INDEX
INDEX
  Custom
INDEX      Lists            Create a Custom List of Months in Turkish
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEXMix-1 (Protect,               Validate , Conditional Format, Options )
  .
INDEX
INDEX
  .                                   Projected GPA Status -- End of THIS Semester                      Exercises
  .
INDEX You MUST enter data in                          Student's Name:               ceren
INDEX the yellow cells, for each
  .                                       This semester Expected GPA:     3.05
       course you are taking.                                                                           Preparing a Sheet for
  .                                               Status this semester:          Honor
                                                                                                        Others
  .               TABLE-1: Courses Registered THIS Semester
  .   Course                       Term      Expected      Grade Point    Course            Quality     On this sheet combine t
  .    Code    Course Name         Mark     Letter Grade   Equivalent     Credits           Points      required on the four pre
  .                                              A                4.00              3           12.00
  .
INDEX                                            A                4.00              3           12.00
                                                                                                        Validation
  .
INDEX                                            F                0.00              2            0.00
INDEX
  .                                              F                0.00              3            0.00
                                                                                                        Conditional Formats
INDEX
  .                                              A                4.00              3           12.00   only
INDEX
  .                                              A                4.00              4           16.00   Protect
INDEX
  .                                              A                4.00              3           12.00   OptMenu
INDEX
  .                                                               0.00                           0.00   and Row and Column H
  .
INDEX                                                             0.00                           0.00   OFF.
  .
INDEX                                                             0.00                           0.00
  .
INDEX                                                             0.00                           0.00
  .
INDEX                                                             0.00                           0.00
  .
INDEX
  .
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX   GPE & LETTER GRADE TABLE
                         Letter   Grade Point
           Term Mark
                         Grade    Equivalent
INDEX
INDEX          0           0         0.0
INDEX         49          F          0.0                    THIS IS A LOOKUP TABLE ONLY
INDEX         50          D-         0.7                 NO DATA IS ENTERED INTO THIS TABLE
INDEX         55          D          1.0                           BY THE STUDENT
INDEX         60          D+         1.3
INDEX         65          C-         1.7                This Table shows the Letter Grade that is awarded
INDEX         70          C          2.0                   based on the Term Mark the student earns.
INDEX         75          C+         2.3
INDEX         80          B-         2.7                It also shows the Grade Point Equivalent (GPE) of
INDEX         84          B          3.0                                 the Letter Grade.
INDEX         88          B+         3.3
INDEX         92          A-         3.7
INDEX         96          A          4.0
INDEX
INDEX
INDEX                  This is the end of this Sheet.
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
Options )
 Exercises

  Preparing a Sheet for Use by
  Others

  On this sheet combine the features
  required on the four previous sheets:

  Validation … same
  Conditional Formats … Cell H6

  Protect … same
  OptMenu … Gridlines, Zero Values
  and Row and Column Headers all
P TABLE ONLY
D INTO THIS TABLE
TUDENT

etter Grade that is awarded
Mark the student earns.

 Point Equivalent (GPE) of
   Scenarios
INDEX
INDEX
    Creates
INDEX          and saves scenarios, which are sets of data you can use to view the resu
    what-if analyses.
INDEX
    Steps to follow:
INDEX                                                                  Exercises:
   From TOOLS menu -- Scenario                                        1.
   Select Add …                                                       exercises on the
   Then type the Scenario Data                                        enter the courses yo
                                                                      this semester, plus t
                                                                      for each course.
INDEX
INDEX                                                                 2.
INDEX                                                                 (representing your
INDEX                                                                 Probable
INDEX                                                                 scenarios) of how yo
INDEX                                                                 this semester will en
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
can use to view the results of

        Exercises:
        1. After you have completed the
        exercises on the Mix-1 sheet,
        enter the courses you are taking
        this semester, plus the Credits
        for each course.

        2. Create three (3) Scenarios
        (representing your Best,
        Probable and Worst case
        scenarios) of how you believe
        this semester will end for you.
INDEX
  .  Comment
  .
INDEX
  .
INDEX
    Monthly Loan Payment                Comments are used for three main purposes:
  . Present Value (PV)
INDEX                      -50,000      1. Used by the person who creates the sheet as a reminder of
  . Interest Rate (RATE)
INDEX                        14.0%      what a cell is used for or what a formula is meant to do, etc.
  . Term (NPER)                36.0     2. To serve as a guide to a person who may later need to maintain
  . Future Value (FV)          0.00     or modify the sheet.
  . Payment (PMT)            1,709      3. As a gude for a user of the sheet.
  .
  . Exercise:
    a)
INDEX Add a comment to cell E5 explaining that the "annual rate of interest must be entered in this cell."
  .
  . b)
INDEX Edit the Comment in cell E4 by changing the word MINUS to the word NEGATIVE
  . c)
INDEX Delete the Comment in Cell E3
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
  .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
  .
  .
   Goal Seek --
INDEX
INDEX
                         Calculates the change to one cell            to achieve goal
  . Monthly Loan Payment
INDEX
  . Present Value (PV)
INDEX                    -85,000 Cost of Purchase -- Amount of Loan
  . Interest Rate (RATE)
INDEX                      18.0% Annual interest rate.
  . Term (NPER)             36.0 Number of months to repay
  . Future Value (FV)       0.00 FV is zero if loan is fully repaid
  . Payment (PMT)
  .                          3,073
  . Situation:
  . You
INDEX wish to purchase a Mercedes Benz valued at 85,000 Euros
  . The
INDEX bank agrees they will lend you the money at 18% per year ( 1.5% per month)
  . They
INDEX suggest you will repay the loan in full over 36 months (3 years)
  . Your
INDEX payments are 3,073 Euros each month … for the 3 years.
INDEX
INDEX GOAL is to pay only 2,500 Euros per month … this is all you can afford.
  . Your
  .
INDEX
  . Finding Solutions using Goal Seek:
INDEX
  . There
INDEX are 4 possible alternatives in this situation:
INDEXBorrow less money from the Bank … for 36 payments of 2,500 Euros how much can you borrow?
  . a)
INDEXFind a bank with a lower interest rate … How low must the interest be.
  . b)
  . c) Pay 2,500 Euros for more months than 36
INDEX
  . b)
INDEXDo not repay all of the loan in 36 months. Leave a Future Value.
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
  .
INDEX
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
n you borrow?
  Chart
INDEX
INDEX
INDEX
  Year-2000                Sales Volume               Exercise-1:
INDEX Rep. Qtr-1
  Sales                  Qtr-2    Qtr-3      Qtr-4   Create a "Stacked Column with 3D visual effect" chart
  Ali
INDEX        119              99      109        125 (the same as the one on the left). Place it in the green area.
  Beril      132             110      121        139
  Cemal      112              94      103        118
  Deniz      152             127      139        160
  Evren      141             118      129        149

INDEX600         Annual Performance
INDEX
     500
INDEX
INDEX400
                                              Qtr-4
INDEX300
                                              Qtr-3
INDEX200
                                              Qtr-2
INDEX100
                                              Qtr-1
INDEX 0                                               Exercise-2:
INDEX      Ali     Beril Cemal Deniz Evren            Use the same Table Data to create a PowerPoint Chart slide.
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
l effect" chart
  the green area.




rPoint Chart slide.
INDEXName First Name Quiz 1
  Last                        Sorting
  Hanci
INDEX        Aysu       95
  Bayraktar Oguzhan
INDEX                   92
INDEX
  Aksoy      Ali        90    Exercise
  Oluc
INDEX        Deniz      88    This is an exercise to learn Sorting.
  Vural      Yesim      87    1. Select (Highlight) the DATA in Columns BCD from ROW 2
  Ozay       Ilhan Zeki 86       to ROW 55.
  Ketenci    Cicek      85    2. Select DATA--SORT ... Then Sort by each of the Columns
  Isisag     Baran      84    Be sure to Select (Highlight) all of the COLUMNS and all of
  Eryilmaz   Ayjan      81    the DATA ROWS before you sort.
  Gungor
INDEX        Hande      80    If you only Select (Highlight) one Column, for example the
  Atac
INDEX        Nazli      80    Last Name, then only the Last Name will be sorted and
  Erdogan
INDEX        Arzu       79    the First Name and Marks will no longer match the Last Name.
  Pektas
INDEX        Diyar      78
  Eraydin
INDEX        Arzu       77
  Karadagli Selami
INDEX                   76
  Oral
INDEX        Tolga      76
  Mansuroglu Tayfun
INDEX                   75
  Karaoglu
INDEX        Cagri      74
  Canaydin Filiz
INDEX                   71
  Ersoz
INDEX        Gokce      71
  Yaman
INDEX        Kivanc     70
  Yagci
INDEX        Zeynep     70
  Kalemci
INDEX        Berna      69
  Kayahan
INDEX        Serap      69
  Ozen
INDEX        Tuba       69
  Mavus
INDEX        Davut      67
  Cayiroglu Pervin
INDEX                   66
  EkmekciogluAli
INDEX                   63
  Genc
INDEX        Gokhan     63
  Rona
INDEX        Ismail     62
  Ickale
INDEX        Reyhan     62
  Iren
INDEX        Bahar      61
  Kesim
INDEX        Cenk       61
  Isik
INDEX        Bahar      60
  Satana
INDEX        Yasemin    60
  Yayici
INDEX        Latif      58
  Kacar
INDEX        Sedef      58
  Gulbaran Goktug
INDEX                   55
  Emre
INDEX        Pinar      55
  Gungoroglu Hande
INDEX                   53
  Gunes
INDEX        Aykut      52
  Karlikli
INDEX        Caner      52
  Ozdemir
INDEX        Ipek       47
  Ozer
INDEX        Tuba       40
  Gurtanyel Ayse
INDEX                   39
  Yildirim
INDEX        Mehmet     38
  Pekol
INDEX        Dilek      36
  Yildirim
INDEX        Menekse    36
  Oztek
INDEX        Didem      35
  Ozhan
INDEX        Tuna       35
  Oncel
INDEX      Hulya     31
  Polat
INDEX      Ugur      31
  Sonmez
INDEX      Yasemin   19
  Uysal
INDEX      Kaan      10
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
 Paste Special
INDEX
INDEX
  The COPY then PASTE commands will automatically
INDEX
   copy
INDEX both the CONTENTS and FORMATS.
INDEX
 Paste Special enables us to Paste Selected information.
  Formulas Copies only Formula not Format
  Values     Copies the result of a Formula not the Formula
  Formats    Copies only the Cell Formats not the content
  Comments Copies only the Comments not Content nor Format
INDEX
  Validation Copies only the Validation criteria, if any.
INDEX
INDEX
  Skip
INDEX Blanks Will not replace values in your paste area when
INDEX        blank cells occur in the copy area.
INDEX
  Paste
INDEX Link Links the pasted data to the source data. This
INDEX        was how we linked a PowerPoint slide to an XL sheet.
INDEX
  Exercise:
INDEX
  Open
INDEX a blank worksheet, enter some data and practice               Operation:See next sheet.
   each
INDEX of the Paste Special options, view the results.               Transpose:See next sheet.
INDEX                                                                             Continue on next sheet …
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
tinue on next sheet …
 Hide -- Unhide
INDEX
INDEX
  You
INDEX   may HIDE and UNHIDE Sheets quickly and easily.
INDEX HIDE:
   To      From Format menu select SHEET … Then HIDE … the current sheet is Hidden
   To
INDEX UNHIDE: Format -- Sheet -- Unhide and then select the Sheet on the LIST -- Click OK




INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
nt sheet is Hidden
he LIST -- Click OK
    Find -- Replace plus
INDEX
INDEX
                                                      Go To see next Sheet
   The
INDEX Find Dialog Box is the top half of the Find-Replace Dialog Box.
   FIND
INDEX will find letters or numbers in any part of a sheet: Text, Formulas, Functions, Comments etc.
   REPLACE should be used with thought and great care:
INDEX
   a) If you select only one (1) cell and use Replace, it will Replace in every cell on the worksheet
      e.g. The example should read "… is an integer."
      If you select the Cell ( C11 ) and replace A with AN you will also change the Formula in Cell C15
   b) To avoid replacing every cell on the sheet, select at least two (2) cells when using Replace. The
      replacement is then limited to the selected cells.

Example
 1  The sum       is a integer
 2
 3
 4      Sum
  5          15
INDEX
INDEX
INDEX                                                                                      Continue on next sheet …
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
tinue on next sheet …
 Printing with Excel -- A 3-step process
INDEX
INDEX
  Step-1. Set Print Area
INDEX
  An Excel Worksheet has 16 million cells, these could represent thousands of printed pages.
INDEX
  Usually
INDEX we only wısh to print part of the data on a worksheet.
  To do thıs we select (highlight) the cells to be prınted … then select FILE -- Print Area -- Set Print Area

 Step-2. Page Setup
  The Page Setup command
  opens a Dıalog Box with
  four (4)
INDEX Tabs.
INDEX
  Each
INDEX of these Tabs is explained on the following sheets.
INDEX
  Step-3. Print
INDEX
  Choose
INDEX pages to print: All or From_ To_ and number of copies.
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX                                                                                              Continue on next sheet …
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
tinue on next sheet …
 Printing with Excel -- Page Setup Options-1
INDEX
INDEX
  PAGE Tab
INDEX                                                  MARGINS Tab
INDEX
  Use
INDEXthe PAGE Tab to select Orıentation, that is to
 print Vertical (Portrait) or Horizontal (Landscape)

 If selected Print Area is a bit too large for the
 page, then select Fit to 1 page.

INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
Use the MARGINS Tab to
set the Top, Bottom plus
Right and Left margins.

The Default settings are
shown: 1.0 inch & 0.75 inch

Center on Page
Check the Horizontally
option to print the page in
the center of the Left and
Rıght margins

Check the Vertically
option to print the page in
the center of the Top and
Bottom margins



    Continue on next sheet …
 Printing with Excel -- Page Setup Options-2
INDEX
INDEX
  HEADER
INDEX
INDEX          / FOOTER Tab
  To insert
INDEX         a Header or Footer Click the Custom buttons … a dialog box opens

                                                                        Type the Header (Footer
                                                                        or Right Section box.


INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX                                                                  These are options to autom
INDEX                                                                   page numbers, date, time
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
 Type the Header (Footer) in the Left, Center
 or Right Section box.




These are options to automatically insert
 page numbers, date, time or File name




                         Continue on next sheet …
 Printing with Excel -- Page Setup Options-3
INDEX
INDEX
  SHEET
INDEX
INDEX       Tab
  If only
INDEX       one page is to be printed, then this sheet is usually not needed.

                                                               If multi pages are to be printed,
                                                               this sheet may be needed
                                                               such as the print order and
                                                               page number sequence.
INDEX                                                          Print Titles
INDEX                                                          On multi page printouts it is usu
INDEX                                                          print the Left Column(s) and Top
INDEX                                                          sheet, because they contain the
INDEX                                                          needed on every page.
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
pages are to be printed, then options from
et may be needed
he print order and
mber sequence.


i page printouts it is usually necessary to
  Left Column(s) and Top Row(s) on every
ecause they contain the headıngs and titles
 on every page.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:4
posted:10/12/2012
language:English
pages:91