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 >> 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 • =, >, >=, <, <=, <> 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 field’s 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 • Butwhathappens 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 object’s 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)