; A1
Learning Center
Plans & pricing Sign in
Sign Out
Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>



  • pg 1
									Spreadsheet-Based Decision Support Systems

                   Chapter 1: Introduction

Manuel Gonçalves
                        Overview of Excel
   Excel Basic Functionality
     –   Chapter 2: Excel Basics and Formatting
     –   Chapter 3: Referencing and Names
     –   Chapter 4: Functions and Formulas
     –   Chapter 5: Charts
     –   Chapter 6: Pivot tables

   Excel Extended Functionality
     –   Chapter 7: Statistical Analysis
     –   Chapter 8: Solver and mathematical programming
     –   Chapter 9: Simulation
     –   Chapter 10: Working with Large Data

Spreadsheet-Based Decision Support Systems
            Chapter 2: Excel Basics and Formatting

Manuel Gonçalves
   2.1 Introduction
   2.2 Defining the Set of Excel Objects
   2.3 Entering Data into Cells
   2.4 Understanding Excel Menus and Toolbars
   2.5 Formatting
   2.6 Summary

   The Excel object hierarchy

   Basic data entry and data copying and pasting in cells

   Excel menus and toolbars

   Customizing and creating new menus and toolbars

   Formatting cells

   Conditional formatting

         Defining the Set of Excel Objects
   Objects: The elements manipulated in spreadsheet applications and
    VBA programming.

   Object Hierarchy: An ordered listing of all objects.

   Object Model: The entire system of objects.

                       Entering Data into Cells
   Simple values: Numbers and text that are entered into cells.

   Click-and-drag: Used to copy a highlighted cell or range of cells to an
    adjacent cell or range of cells.

         Copying a range of cells.
                                          Excel automatically numbers sequentially when
                                          copying from a pattern it recognizes.

Understanding Excel Menus and Toolbars
   Menus and Shortcut Keys

   Toolbars

   Customizing Toolbars and Menus

               Menus and Shortcut Keys
   The Menu contains the standard Windows options of File, Edit, View,
    Insert, Format, Tools, Window, and Help, as well as a Data option.

   Some options are specific to Excel; for example, in addition to Delete, we
    find Delete Sheet and Move or Copy Sheet.
   Another example of a menu option specific to Excel is the Paste Special
     –   Values
     –   Formulas
     –   Formats
     –   Transpose

   Two main toolbars are Standard and Formatting.

   A dialog box is a separate window that provides several modifiable
    options on different tabs, or categories, within the box.

   To drag-and-drop, click, hold down on the mouse, and drag.

        Customizing Toolbars and Menus
   Excel allows us to customize any toolbar.
     – Tools > View > Toolbars > Customize
     – Tools > Customize

   Select the toolbar you wish to customize or select New to create a new
    toolbar or menu.

   Choose from available commands to add to your toolbar. (We will also
    learn how to add customized commands when we study VBA macros.)

   Formatting Cells

   General Formatting

   Conditional Formatting

                        Formatting Cells
   To format a cell, we either click on a cell and choose Format > Cells from
    the menu, or we right-click on the cell and choose Format Cells from the
    drop-down list.

   Formatting options include
     –   Number
     –   Alignment
     –   Font
     –   Border
     –   Patterns
     –   Protection

                     General Formatting
   Auto Fit can alter the dimensions of rows and columns, making them as
    wide as necessary to show all values entered.

   Auto Format option offers several preset table designs that we can
    apply to a spreadsheet.

   Style is used to apply general formatting to the entire worksheet.
                 Conditional Formatting
   Conditional Formatting only formats cells that meet a predetermined
    condition or a set of conditions.

   Excel objects are elements that you can manipulate in spreadsheet applications
    and VBA programming.

   Menus and toolbars create Excel’s user interface; they provide options for
    working in the spreadsheet environment.

   The two main toolbars on which you can find the most common operations are
    the Standard and Formatting toolbars.

   Customizing a toolbar allows you to edit the toolbars by modifying commands
    and display options.

   The format of a cell includes the following modifiable characteristics: Numbers,
    Alignment, Font, Border, Pattern, and Protection.

   Conditional Formatting formats cells only if they meet a specified condition.

                         Additional Links
   (place links here)

Spreadsheet-Based Decision Support Systems
                  Chapter 3: Referencing and Names

Prof. Name                                           name@email.com
Position                                               (123) 456-7890
University Name
   3.1 Introduction
   3.2 Referencing Cells
   3.3 Names for Cells, Ranges, and Worksheets
   3.4 Summary

   Referencing a cell or range of cells

   Different types of referencing affect how cell addresses are copied

   Use R1C1 notation

   Avoid circular referencing

   Various ways to name cells

   Creating basic formulas and constants using cell names

   Use natural range names

                     Referencing Cells
   Relative Referencing and Absolute Referencing

   R1C1 Notation

   Referencing Other Worksheets and Workbooks

   Circular Referencing

Relative Referencing and Absolute Referencing
   There are four basic types of referencing
     –   relative
     –   absolute
     –   row absolute
     –   column absolute referencing

   Relative Referencing: Row and column value will change (B2).

   Absolute Referencing: Neither row nor column value will change

   Row Absolute: Row value does not change, but column value will
    change (B$2).

   Column Absolute: Column value does not change, but row value will
    change ($B2).

                              Figure 3.1
   The SUM function is
    entered in column B16
    using relative

   When the function is
    copied to cell C16, the
    function values shift
    relative to the new

                           Figure 3.2(a)
   In cell E11, the
    sum from cell B16
    is multiplied by the
    value in cell E4:

   Copying this
    formula to cell
    F11, does NOT
    yield E4*C16, but
    rather F4*C16

                        Figure 3.2(b)
   Absolute
    referencing will
    keep E4 constant
    in both formulas:
    $E$4*B16 and

                     Referencing (cont’d)
   Row absolute referencing places the $ in front of the row number
     – column letter $ row number
     – A$1

   Column absolute referencing places the $ in front of the column letter
     – $ column letter row number
     – $A2

                       Figure 3.3(a)
   Numbers in row headings are same as column headings and we
    want one row number multiplied by one column number

                          Figure 3.3(b)
   When the formula is copied, only the column of the row absolute
    value will change and only the row of the column absolute value will

                           R1C1 Notation
   R1C1 notation: Refers to a cell’s position relative to the origin cell
    where the formula is entered.

   The cell in which the formula is entered is considered to have position
     – A formula entered in A1 to refer to A2 would be:
          R[1]C[0] or R[1]C
     – A formula entered in B1 which refers to A3 would be:
          R[2]C[-1]

   To switch row and column titles to R1C1 notation:
     – choose Tools > Options from the menu
     – Click on the General tab
     – Check R1C1 reference style from the list of options

                             Figure 3.4
   The first value of the table is
    referenced in cell R1C1 (or A1)

   The sum of the first two table
    values is calculated in cell
    R2C1 (or A2)

                              Figure 3.5
   The formula from cell R2C1 is copied to the three cells below.

   Notice the actual formula is identical in each cell.

                             Figure 3.6
   Excel offers a Formula View that shows cells by their formulas,
    instead of their calculated values.
     – Choose Tools > Options from the menu
     – Click on the View tab
     – Select Formulas from the Windows Options

Referencing Other Worksheets and Workbooks
   Cells can also contain formulas which reference cells outside of the
    current worksheet or workbook.

   Worksheet in the same workbook: Sheet1!A1

   Workbook: [Data.xls]Sheet1!A1

   Workbook with spaces in the title: ‘[Collected Data.xls]Sheet1’!A1

   Workbook not currently open in Excel:
    ‘C:\My Documents\Project\[Collected Data.xls]Sheet1’!A1

                     Figures 3.7 and 3.8
   Data is in one worksheet and calculations are made in another worksheet

                     Circular Referencing
   A referencing loop in a spreadsheet creates a circular reference.
     – Example: cell A1 has the value “=B1,” cell B1 has the value “=C1,” and cell
       C1 has the value “=A1.”
     – This referencing loop causes an error in Excel.

   The first possible solution to this problem requires us to rearrange our
    references or to modify our formula.

   However, if neither can be done, Excel offers another tool to aid in
    sequential calculations.
     – Tools > Options > Calculations and select Iteration
     – Excel performs a specified number of iterations, or repetitions, of the
       calculations, to try to find a solution applicable to all equations.


To top