CA202 Spreadsheet Application

1. CA202Spreadsheet Application Combining Data from Multiple Sources Lecture # 6

2. Objectives ✔ Use an existing data list as a template for other lists. ✔ Work with more than one set of data. ✔ Link to data in other workbooks. ✔ Summarize multiple sets of data. ✔ Group multiple data lists.

3. Using Existing Data list as Template • To ensure that the workbook for every year has a similar appearance, you can create a workbook with the desired characteristics and save it as a pattern for similar workbooks you create in the future • The benefit of ensuring that all of your sales data worksheets have the same layout is that you and your colleagues will immediately know where to look for specific totals • when you create a summary worksheet, you will know in advance which cells to include in your calculations

4. Using Excel Templates to Store Customization (contd.) • Excel template contain the following • The no of sheets in the workbook, and their names and types • Formatting for individual cell, entire sheet, cell style, page format, custom print area • Repeating text or values, page header, row and column labels • Protected and hidden sheet, row, column and cells • Setting from Tools  Options dialog box • Custom toolbars, macros, hyperlinks and active control

5. Using Excel Templates to Store Customization • Excel claims support both workbook and worksheet templates • Worksheet template is simple workbook containing one sheet • Actually there is no difference between worksheet and workbook template

6. Creating and Saving Workbook template (contd.) • Once you have settled on a design for your workbook, you can save one of the workbook as template • Excel template has .xlt extension • Choose File  Save As • Choose Template in Type box, upon selection Excel will change the active directory to the template directory • To Create a new default workbook • Save the workbookin the XLStart folder or AlternateStartup folder, and name it “book.xlt”

7. Creating and Saving Workbook Template • To create a new default sheet template save a one sheet workbook in one of the Startup folder using the name “sheet.xlt” • Excel uses this template for new worksheet when you choose Insert  Worksheetcommand • To create any other template, save the workbook in the template folder or any of its subfolder • Template stored in Template folder appear on File  New  On my Computer  General Tab • Subfolder appear on separate tab

8. Inserting a New Worksheet based on a Custom Template • Worksheet added by Insert  Worksheet command is always based on the default worksheet template • To insert different template Right Click on worksheet tab, Choose Insert, It will display available template here • If you choose workbook template here, it will insert all sheets that template contains

9. Activity on Page 99

10. Working with More than One Workbook • When you store your data in more than one workbook, you need a way to work with multiple workbooks at the same time • In the Open dialog box, hold down the Ctrl key, click the files you want to open • When you open more than one Excel file, the active workbook often hide the inactive workbooks on the screen

11. Working with More than One Workbook • You can arrange the workbooks in Excel • Choose Window  Arrange

12. Working with More than One Workbook • Another way you can work with more than one workbook is to copy a worksheet from another workbook to the current workbook • You can copy worksheets from another workbook by right-clicking the tab of the sheet you want to copy and, from the shortcut menu that appears, clicking Move or Copy to display the Move or Copy dialog box • Selecting Create a copy leaves the copied worksheet in its original place

13. Changing Sheet Location • To change a worksheet location, just drag its sheet tab to the desired location on the tab bar • To Hide worksheet, choose Format  Sheet  Hide • To UnHide worksheet, choose Format  Sheet  UnHide

14. Activity on Page 103

15. Linking to Data in Other Worksheet • Copying and Pasting data from one workbook to another is a quick and easy way to gather related information at one place • The major problem is, if data from original place changes, the change is not reflected in the cell you copied • You can ensure that the data in the target cell will reflect any changes in the original cell by creating a link between the two cells

16. Linking to Data in Other Worksheet • To create a link between cells, open both the workbook with the cell from which you want to pull the value and the workbook with the target cell • =[TotalByHour2001.xls]Sheet1!$D$8 gives three pieces of information: the workbook, the worksheet, and the cell you clicked in the worksheet • This type of reference is known as a 3-D reference, reflecting the three dimensions (workbook, worksheet, and cell) that you need to point to a cell in another workbook

17. Linking to Data in Other Worksheet • Whenever you open a workbook with a link to another document, Excel will try to update the information in linked cells • If the program can’t find the source, an alert box appears, indicating that there is a broken link • At that point, you can click the Update button and then the Edit Links button to find which link is broken. • To fix the link, click the cell, delete its contents, and then either retype the link or create it with point and click method

18. Activity on Page 108

19. Summarizing Multiple Sets of Data • When all monthly sales worksheet in one workbook follows same column and rows for data, one can use links to bring total sales of all months to one worksheet. • This helps in combining data from several spots to a single spot

20. Summarizing Multiple Sets of Data • Fortunately, there is an easier way to combine data from multiple worksheets in a single worksheet. This process is called data consolidation • This lets you define ranges of cells from multiple worksheets and have Excel summarize the data • Note: You can define only one data consolidation summary per workbook

21. Grouping Multiple Data List • If you want to open a set of files simultaneously, you can define them as part of a workspace, which uses a single Excel file name to reference several workbooks instead of one • To define a workspace, you open the files you want to include and then open the Save Workspace dialog box • The extension of this file will be .xlw

22. Chapter 6 Key Points • If you create a lot of workbooks with the same layout and design, saving a workbook with the common elements (and no data) will take you much less time • You can change the default folder where Excel looks for templates • When you work with several workbooks at once, you can change their arrangement on the Windows desktop • To close all workbooks press Shift + File  Close all • You can move worksheets in the workbook • You can change the color of sheet tab, to make it different • You can use data in other worksheets or workbooks in your formulas. • If you always work on a group of workbooks at the same time, create a workspace so that you can open them all at once.