; A1
Documents
User Generated
Resources
Learning Center
Your Federal Quarterly Tax Payments are due April 15th

# A1

VIEWS: 22 PAGES: 35

• pg 1
```									Spreadsheet-Based Decision Support Systems

Chapter 1: Introduction

Manuel Gonçalves
mgoncalves@fe.unl.pt
FEUNL
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

2
Chapter 2: Excel Basics and Formatting

Manuel Gonçalves
mgoncalves@fe.unl.pt
FEUNL
Overview
   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

4
Introduction
   The Excel object hierarchy

   Basic data entry and data copying and pasting in cells

   Customizing and creating new menus and toolbars

   Formatting cells

   Conditional formatting

5
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.

6
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.

7

   Toolbars

8
   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
option.
–   Values
–   Formulas
–   Formats
–   Transpose

9
Toolbars
   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.

10
   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

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

11
Formatting
   Formatting Cells

   General Formatting

   Conditional Formatting

12
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

13
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

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

15
Summary
   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

   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.

16

17
Chapter 3: Referencing and Names

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

19
Introduction
   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

20
Referencing Cells
   Relative Referencing and Absolute Referencing

   R1C1 Notation

   Referencing Other Worksheets and Workbooks

   Circular Referencing

21
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
(\$B\$2).

   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).

22
Figure 3.1
   The SUM function is
entered in column B16
using relative
referencing:
=SUM(B4:B13)

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

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

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

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

25
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

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

27
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
change

28
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
R[0]C[0].
– 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

29
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)

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

   Notice the actual formula is identical in each cell.

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

32
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

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

34
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.

35

```
To top