UNIT-2: ELECTRONIC SPREADSHEET
(ADVANCED)
What is Data Consolidation?
Data Consolidation allows you to gather together your data from separate worksheets
into a master worksheet. In other words, the Data Consolidation function takes data
from a series of worksheets and summaries it into a single worksheet.
What is Subtotals ?
SUBTOTAL is used to add /total data arranged in an array or group of cells with labels
for columns and/or rows.
By Using the Subtotals dialog, we can select arrays, and then choose a statistical
function to apply to them. It is accessible from Data menu.
What are Scenarios ?
Scenarios are a tool to test “what-if” questions. Each scenario is named, and can be
edited and formatted separately. You can easily switch between different scenarios by
using the Navigator. For example, if you wanted to calculate the effect of different
interest rates on an investment, you could add a scenario for each interest rate, and
quickly view the results.
What is Goal Seek?
Goal Seek helps to find values which will produce the result that you want. for example
Example
Chief Financial Officer has a good idea of the company’s income in the first three
quarters, because of the contracts that are already signed. For the fourth quarter,
however, no definite income is available. So how much must the company earn in Q4
to reach its goal? Then Chief Financial Officer runs a goal seek on the empty cell for Q4
sales and receives the answer
What is a Solver ?
Solver is a more elaborate form of Goal Seek. Solver deals with equations with multiple
unknown variables. It is specifically designed to minimize or maximize the result
according to a set of rules that you define.
How can we Link Data and Spreadsheets Using Multiple Workbooks
and Linking Cells
Spreadsheet allows you to link the cells from various worksheets to summarize data
from several sources. In this manner, you can create formulas using a combination of
local andlinked information. Multiple sheets help to keep the information organized.
Renaming Sheets
There are three ways you can rename a worksheet. You can do any of the following:
1. Double-click on one of the existing worksheet names.
2. Right-click on an existing worksheet name, then choose Rename
from the resulting Context menu.
, 3. Select the worksheet you want to rename (click on the worksheet
tab) and then select the Sheet option from the Format menu. This
displays a submenu from which you should select the Rename option.
What is cell reference? Give two ways of referencing cells?
Cell Reference : A cell reference refers to a cell or a range of cells on a
worksheet that can be used in a formula to calculate values.
Referencing Other Sheets
There are two ways to reference cells in other sheets :
1) By entering the formula directly using the keyboard. :
Typing the reference is simple once you know the format the reference takes. The
reference has three parts to it: Path and file name . Sheet name . Cell name
The general format for the reference is =’file:///Path &File
Name’#$SheetName.CellName
2) By using the mouse.
Question) What is Hyperlink? Explain two types of hyperlinks
Hyperlinks : Hyperlinks can be used in Calc to jump to a different location from
within a spreadsheet to other parts of the same file or to different files or even
to web sites.
Hyperlinks can be stored within your file as either relative or absolute
Two types of hyperlink are
Absolute Hyperlink
An absolute link will stop working only if the target is moved.
Relative hyperlink
A relative link will stop working only if the start and target locations change relative
to each other.
For instance, if you have two spreadsheets in the same folder linked to each other and
you move the entire folder to a new location, a relative hyperlink will not break.
You can insert and modify links using the Hyperlink dialog. To display the dialog, click
the Hyperlink icon on the Standard toolbar or choose Insert > Hyperlink from the
menu bar.
How can we do Linking To External Data :
You can insert tables from HTML documents, and data located within named ranges
from an OpenOffice.org Calc or Microsoft Excel spreadsheet, into a Calc spreadsheet
You can do this in two ways: using the External Data dialog or using the Navigator
Using the External Data
Linking To Registered Data Sources :
You can access a variety of databases and other data sources and link them into Calc
worksheets. First you need to register the data source with OpenOffice.org.
(ADVANCED)
What is Data Consolidation?
Data Consolidation allows you to gather together your data from separate worksheets
into a master worksheet. In other words, the Data Consolidation function takes data
from a series of worksheets and summaries it into a single worksheet.
What is Subtotals ?
SUBTOTAL is used to add /total data arranged in an array or group of cells with labels
for columns and/or rows.
By Using the Subtotals dialog, we can select arrays, and then choose a statistical
function to apply to them. It is accessible from Data menu.
What are Scenarios ?
Scenarios are a tool to test “what-if” questions. Each scenario is named, and can be
edited and formatted separately. You can easily switch between different scenarios by
using the Navigator. For example, if you wanted to calculate the effect of different
interest rates on an investment, you could add a scenario for each interest rate, and
quickly view the results.
What is Goal Seek?
Goal Seek helps to find values which will produce the result that you want. for example
Example
Chief Financial Officer has a good idea of the company’s income in the first three
quarters, because of the contracts that are already signed. For the fourth quarter,
however, no definite income is available. So how much must the company earn in Q4
to reach its goal? Then Chief Financial Officer runs a goal seek on the empty cell for Q4
sales and receives the answer
What is a Solver ?
Solver is a more elaborate form of Goal Seek. Solver deals with equations with multiple
unknown variables. It is specifically designed to minimize or maximize the result
according to a set of rules that you define.
How can we Link Data and Spreadsheets Using Multiple Workbooks
and Linking Cells
Spreadsheet allows you to link the cells from various worksheets to summarize data
from several sources. In this manner, you can create formulas using a combination of
local andlinked information. Multiple sheets help to keep the information organized.
Renaming Sheets
There are three ways you can rename a worksheet. You can do any of the following:
1. Double-click on one of the existing worksheet names.
2. Right-click on an existing worksheet name, then choose Rename
from the resulting Context menu.
, 3. Select the worksheet you want to rename (click on the worksheet
tab) and then select the Sheet option from the Format menu. This
displays a submenu from which you should select the Rename option.
What is cell reference? Give two ways of referencing cells?
Cell Reference : A cell reference refers to a cell or a range of cells on a
worksheet that can be used in a formula to calculate values.
Referencing Other Sheets
There are two ways to reference cells in other sheets :
1) By entering the formula directly using the keyboard. :
Typing the reference is simple once you know the format the reference takes. The
reference has three parts to it: Path and file name . Sheet name . Cell name
The general format for the reference is =’file:///Path &File
Name’#$SheetName.CellName
2) By using the mouse.
Question) What is Hyperlink? Explain two types of hyperlinks
Hyperlinks : Hyperlinks can be used in Calc to jump to a different location from
within a spreadsheet to other parts of the same file or to different files or even
to web sites.
Hyperlinks can be stored within your file as either relative or absolute
Two types of hyperlink are
Absolute Hyperlink
An absolute link will stop working only if the target is moved.
Relative hyperlink
A relative link will stop working only if the start and target locations change relative
to each other.
For instance, if you have two spreadsheets in the same folder linked to each other and
you move the entire folder to a new location, a relative hyperlink will not break.
You can insert and modify links using the Hyperlink dialog. To display the dialog, click
the Hyperlink icon on the Standard toolbar or choose Insert > Hyperlink from the
menu bar.
How can we do Linking To External Data :
You can insert tables from HTML documents, and data located within named ranges
from an OpenOffice.org Calc or Microsoft Excel spreadsheet, into a Calc spreadsheet
You can do this in two ways: using the External Data dialog or using the Navigator
Using the External Data
Linking To Registered Data Sources :
You can access a variety of databases and other data sources and link them into Calc
worksheets. First you need to register the data source with OpenOffice.org.
