Transcript Class 06 Excel CA202.ppt

CA202
Spreadsheet Application
Combining Data from
Multiple Sources
Lecture # 6
1
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.
2
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
3
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
4
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
5
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 workbook in the XLStart folder or Alternate
Startup folder, and name it “book.xlt”
6
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  Worksheet command
• 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
7
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
8
Activity on Page 99
9
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
10
Working with More than One
Workbook
• You can arrange the
workbooks in Excel
• Choose Window 
Arrange
11
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 rightclicking 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
12
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
13
Activity on Page 103
14
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
15
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
16
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
17
Activity on Page 108
18
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
19
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
20
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
21
Chapter 6 Key Points
1. 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
2. You can change the default folder where Excel looks for
templates
3. When you work with several workbooks at once, you can
change their arrangement on the Windows desktop
4. To close all workbooks press Shift + File  Close all
5. You can move worksheets in the workbook
6. You can change the color of sheet tab, to make it different
7. You can use data in other worksheets or workbooks in
your formulas.
8. 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.
22