CSI 1306

1
CSI 1306

• SPREADSHEETS 2
• OBJECT LINKING EMBEDDING

2
Printing

• Print paging scheme
• worksheet broken into pages, first vertically
  down then horizontally over
• information within a row or column is not broken
  up
• if row will not fit at bottom of page, the entire
  row is moved to the following page
• if column will not fit at right edge of page, the
  entire column is moved to a new page
• Print preview
• choose tool (magnifying glass) on standard
  toolbar or Print Preview from File menu
• afterwards, page breaks are shown as dotted lines

3
Printing

• Page setup
• choose from File menu gtgt Page Setup or Print
  dialog box contains 4 tabs
• page
• orientation, scaling, first page number
• margins
• top, bottom, left right
• header/footer (text that appears on every page)
• defaults are name of worksheet and page number
  (or none)
• customize (date, time, font, page 1 of 5)
• sheet
• rows to repeat as titles on each page
• columns to repeat at left edge of each page
• gridlines, column letters row numbers

4
Printing

• Manual page break
• position cell pointer
• as in splitting a window into panes, top left
  edges of cell pointer are used to determine where
  placed
• choose Page Break from Insert menu
• to remove
• position cell pointer
• in row below a horizontal page break
• in column to right of vertical page break
• choose Remove Page Break from Insert menu
• Printing formulas
• choose Options from Tools menu
• select View tab click on Formulas check box

5
Printing

• Printing
• Print tool on standard toolbar gives a single
  copy of all the information in the current
  worksheet
• choose Print from File menu to change page
  settings in the Print dialog box
• print currently selected cells
• print current worksheet or currently selected
  worksheets
• print entire workbook
• print multiple copies
• print all pages or specific pages

6
Worksheets Workbooks

• Workbook
• stored as a single file with .XLS extension
• can contain up to 256 worksheets (looseleaf pages
  in a notebook)
• click on sheet tab to move to it (use tab
  scrolling buttons)
• rename by selecting Rename from sheet tab
  shortcut menu

7
Worksheets Workbooks

• Group related worksheets in a workbook
• sales by product line and month for each store
• (1) perform group editing
• select multiple worksheets
• enter common information in only 1 worksheet
  appears in all selected worksheets
• (2) attach macros to workbook
• available to other worksheets in workbook

8
Worksheets Workbooks

• (3) quickly compare values in worksheets
• display different worksheets
• choose New Window from Window menu
• click on tab of 2nd worksheet to be displayed
• continue for next worksheet
• choose Arrange from Window menu
• tiled, horizontal, vertical, cascade
• (4) print information from all worksheets in one
  report
• one print operation

9
Worksheets Workbooks

• (5) easily create a summary worksheet
• to total values stored in other worksheets
• to reference cell in another worksheet
• sheet name!A4
• enter worksheet addition formula in one cell of
  summary worksheet copy to all other cells
• changes in worksheet values are reflected in
  summary worksheet

10
Macro

• A series of commands to accomplish a routine task
  that can be recorded and played back
• for example, entering a company name with a
  particular font type and size on a worksheet
• recording and playing back processes are similar
  to those used in Word
• good habit to start with the Go To command

11
Macro

• A macro is attached to the workbook in which it
  was created
• to use it with any workbook, choose Personal
  workbook from the Record Macro dialog box before
  recording the macro
• Personal.xls is a hidden workbook which is
  automatically opened when you start Excel
• The commands in a macro are recorded in a module
  sheet (a worksheet without cells) in a language
  called Visual Basic
• we will create more complex macros using Visual
  Basic programming later in the course

12
Goal Seek

• Used to find a specific value that yields a
  predetermined result in a formula
• saves your time and effort in a trial and error
  search
• to use
• select cell containing formula
• choose Goal Seek from Tools menu
• enter in dialog box
• Set cell
• To value
• By changing cell (cannot contain a formula)
• formula in Set cell must depend directly or
  indirectly on the value in changing cell

13
Solver

• Used to analyze multiple variable problems
• to find the optimum value for a specific cell
• by adjusting the values of one or more cells or
• to apply specific limitations to one or more
  values involved in the calculation
• to use
• choose Solver from the Tools menu
• enter in dialog box
• Set target cell (objective function)
• Equal to Min, Max or Value
• By changing cells (decision variables)
• Subject to the constraints

14
Solver

• Note that
• maximum of 2 constraints for each changing cell
• up to 200 changing cells
• 3 types of problems - linear, non-linear and
  integer
• data tables and scenarios for displaying and
  storing alternatives

15
Additional Capabilities

• Spreadsheet Software
• Calculation
• Charts/Graphs
• Data Management

16
Charts/Graphs

• Graphical representation of data
• X Axis
• horizontal axis
• column headings in first row become category
  labels
• Y Axis
• vertical axis
• values in each row constitute a data series
• row headings in first column become legend labels
• To produce a chart
• use the ChartWizard

17
Charts/Graphs

• ChartWizard tool
• Step 1 of 4
• select a chart type
• Step 2 of 4
• select the cells that contain the data and labels
  for the chart
• Step 3 of 4
• select options
• titles for the chart, x-axis, y-axis
• location of legend
• existence of gridlines
• Step 4 of 4
• place the chart
• on a new sheet or on the existing sheet

18
Charts/Graphs

• Move or resize the chart
• Edit the chart by
• clicking on objects, or
• using the Chart toolbar
• Use the Drawing toolbar to
• add a text box
• add an arrow

19
Databases

• Definition
• a collection of related information, organized
  into records and fields
• Record
• an entity a person, place or thing for which we
  store information
• ie. a part in inventory
• represented as a row in a worksheet
• Field
• an attribute a characteristic or quality
  describing a particular entity
• ie. part number, description, unit cost, quantity
  on hand
• represented as a column in a worksheet

20
Databases

• Data Entry
• Use a data form to add, delete or edit records in
  a database
• Creation
• enter the row of column headings used as field
  names
• enter one sample record in the next row, with
  each field formatted
• select the 2 rows of cells choose Form from
  Data menu
• a data form appears on the screen
• a calculated field appears on the form but
  without a text box for data entry
• Adding records
• click on New enter information for each field
• click on Close when all records are entered

21
Databases

• Finding records
• click on Criteria button in Form
• enter search criteria in the blank text boxes
• can use the and ? wildcard characters
• , gt, gt, lt, lt, ltgt operators
• click Find Next button let search engine find
  the records
• 1st record meeting the criteria appears in the
  Data Form
• click Find Next button 2nd record meeting the
  criteria appears continue until you find the
  desired record

22
Databases

• Filtering Records
• To hide all records in the database except the
  ones you want to see
• choose Filter from the Data menu
• choose AutoFilter
• drop-down list buttons appear on each cell with a
  field name
• click enter values for each search field
• copy the filtered records elsewhere so you can
  work on them
• choose Show All to display all the records in the
  database
• Use custom autofilters for more complicated
  searches
• choose Custom from a fields drop-down list
• Custom AutoFilter dialog box appears
• select fields, operators, AND/OR operators

23
Databases

• Sorting records
• new records are added to the bottom of the
  database
• to sort records in a particular sequence
• choose Sort from Data menu
• Sort dialog box appears
• select up to 3 sort fields from the Sort By
  drop-down list box
• choose ascending or descending sequence for each

24
Object Linking and Embedding
25
Transferring Information

• Where?
• within a document
• between documents in the same application
• between documents in different applications
• How?
• moving
• copying
• embedding
• Linking

26
Moving Information

• Cut deletes the selected information from the
  source document and copies it to the clipboard
• Paste inserts the information on the clipboard
  into the destination document

27
Copying Information

• Copy leaves the selected information in the
  source document and copies it to the clipboard
• Paste inserts the information on the clipboard
  into the destination document

28
Pasting

• But what happens when we move or copy between
  documents in different applications?
• Usually pasted in a format that the destination
  application can edit
• Excel worksheet becomes a Word table
• Word text becomes text in an Excel worksheet cell
• If it cannot do this, will embed the information
• If it cannot embed, will insert as a static
  picture

29
Object Linking Embedding

• The information that is to be transferred is
  treated as an object
• The information can be one of a variety of
  formats
• text, numbers, worksheet, graphics, sound, video
• The information can then be embedded or linked
• normally used when pasting a different kind of
  information into a window
• a sound, spreadsheet or picture into a word
  processing document

30
Embedding

• Cut or copy the selected information in the
  source document
• Position the cursor in the destination document
• Choose Paste Special from the Edit menu
• select the Paste option button
• select the type of object from the list
• Excel worksheet, enriched text

31
Embedding

• Result
• The name of the program that created that object
  is embedded along with the transferred
  information
• In the destination document, the transferred
  information will have a box around it
• The destination program cannot edit the
  information
• Double clicking on the object brings the program
  that created the object to the screen so that the
  object can be edited
• Advantage
• The functionality of the source program is
  retained
• However, the embedded information is not linked
  to the source information

32
Linking

• Cut or copy the selected information in the
  source document
• Position the cursor in the destination document
• Choose Paste Special from the Edit menu
• select the Paste Link option button
• select the type of object from the list

33
Linking

• Result
• Looks just like embedding
• With a link there is only one copy of the
  information
• Not really pasting the object pasting the
  objects filename
• A double click on the information in the
  destination document opens the source program and
  the original document for editing
• Advantage
• When information is changed in the source, it is
  automatically updated in the destination since
  there is only one real version of the object

34
OLE

• Paste, Embed or Link?
• paste objects that you will never want to change
• embed objects that you will want to edit later
• link objects if you want several programs to
  share the same version of an object

35
Demonstration

• Transfer Word text to Excel
• Paste
• Embed
• Link
• Transfer a Word table to Excel
• Paste
• Embed
• Link
• Transfer an Excel worksheet to Word
• Paste
• Embed
• Link

36
Homework
37

• Cashflow Forecast
• Draw the layout (title, row and column headings,
  sample data, etc.) for a cashflow forecast
  worksheet
• You want to be able to experiment with some
  what-if? scenarios by varying the hourly rate
  of pay, so set the worksheet up so that this can
  easily be accomplished (ie. in formulas, anchor
  any reference to the hourly rate of pay).
• Enter some formulae
• Show the effects of copying the formulas to other
  cells (i.e. the automatic changing of cell
  references)
• Show the use of arguments in functions by using
  the PV function to find the present value of the
  ending balance

38

• Car Features
• Draw the layout (title, row and column headings,
  sample data, etc.) for a spreadsheet that
  compares the features of different cars,
  assigning weights to each of the features and
  scores for each car
• Enter some formulas
• Show the effects of copying the formulas to other
  cells (i.e. the automatic changing of cell
  references)