CBSE IT 10th Notes Electronic Spreadsheet Advance (adv.)
Analyze data using scenarios and goal seek
Consolidating data
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
or workbooks and summaries it into a single worksheet that you
can update easily.
The data from the consolidation ranges and target range are saved
when you save the worksheet. If you later open a worksheet in
which consolidation has been defined, this data will again be
available. It is available under Data menu->Consolidate option
Creating subtotal:
SUBTOTAL is a function listed under the Mathematical category when
you use the Function Wizard (Insert > Function). Because of its
usefulness, the function has a graphical interface. It is accessible
from Data menu
SUBTOTAL, totals/adds data arranged in an array—that is, a group of
cells with labels for columns and/or rows. Using the Subtotals dialog,
you can select arrays, and then choose a statistical function to apply
to them. For efficiency, you can choose up to three groups of arrays
to which to apply a function.
When you click OK, Calc adds subtotals and grand totals to the
selected arrays, using the Result and Result2 cell styles for them. It
is available under Data -> Subtotals
Scenarios
Scenarios are a tool to test “what-if” questions. Each scenario is
named, and can be edited and formatted separately. When you
print the spreadsheet, only the content of the currently active
scenario is printed.
A scenario is essentially a saved set of cell values for your
calculations. You can easily switch between these sets using the
Navigator or a drop-down list which can be shown beside the
changing cells.
For video lectures Youtube – Platinum Classes Website : www.crackmycbse.com
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. Formulas that rely on the
values changed by your scenario are updated when the scenario is
opened. Available Tools > Scenarios
Creating scenarios
To create a scenario, select all the cells that provide the
data for the scenario.
Tools > Scenarios
Goal Seek
Using Goal Seek option under Tools menu, you can discover what
values will produce the result that you want.
Using Goal Seek
Tools > Goal Seek reverses the usual order for a formula.
Usually, you run a formula to get the result when certain
arguments are entered. By contrast, with Goal Seek, you
work with a completed formula to see what values you need
in an argument to get the results that you want.
For video lectures Youtube – Platinum Classes Website : www.crackmycbse.com
Using the Solver
Tools > Solver amounts to a more elaborate form of Goal
Seek. The difference is that the 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.
Each of these rules sets up whether an argument in the formula
should be greater than, lesser than, or equal to the value you
enter.
If you want the argument to remain unchanged, you enter a rule that
the cell that contains it should be equal to its current entry.
For arguments that you would like to change, you need to add two
rules to define a range of possible values:
the limiting conditions : For example, you can set the constraint that
one of the variables or cells must not be bigger than another
variable, or not bigger than a given value. You can also define the
constraint that one or more variables must be integers or binary
values.
Link Data and Spreadsheets Using Multiple Workbooks
and Linking Cells
Spreadsheet also allows you to link the cells from various
worksheets and from various spread sheets to summarize data from
several sources. In this manner, you can create formulas that span
different sources and make calculations using a combination of local
and linked information.
Multiple sheets help keep information organized;
Setting up multiple sheets
Identifying sheets
When you open a new spreadsheet it has, by default, it has a sheet
named Sheet1 which is managed using tabs at the bottom of the
spreadsheet, as shown below.
Inserting new sheets
There are several ways to insert a new sheet. The first step, in all
cases, is to select the sheet that will be next to the new sheet. Then
do any of the following:
Select the plus icon at the bottom of the screen.
For video lectures Youtube – Platinum Classes Website : www.crackmycbse.com
Or, select Home > Insert > Insert Sheet.
Renaming Worksheets
There are three ways you can rename a worksheet
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.
Insert Sheet from different Spreadsheet
If you prefer, select the Link option to insert the external sheet as a
link instead as a copy. This is one of several ways to include “live”
data from another spreadsheet. The links can be updated manually to
show the current contents of the external file; or, depending on the
options you have selected in Tools > Options > OpenOffice.org Calc
> General > Updating, whenever the file is opened.
Create or change a cell reference
A cell reference refers to a cell or a range of cells on a worksheet
and can be used to find the values or data that you want formula to
calculate.
In one or several formulas, you can use a cell reference to refer to:
• Data from one or more contiguous cells on the worksheet.
• Data contained in different areas of a worksheet.
• Data on other worksheets in the same workbook.
Creating reference to other sheets
There are two ways to reference cells in other sheets: by entering
the formula directly using the keyboard or by using the mouse.
Calc can link different files together. The process is the same, but
we add one more parameter to indicate which file the sheet is in.
Creating The Reference With 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
Working with Hyperlinks
Hyperlinks can be used in Calc to jump to a different location from
within a spreadsheet and can lead to other parts of the current file,
to different files or even to web sites.
Relative And Absolute Hyperlinks
Hyperlinks can be stored within your file as either relative or
absolute. An absolute link will stop working only if the target is
moved. 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.
To change the way that OOo saves the hyperlinks in your file, select
Tools > Options > Load/Save > General and choose if you want
URLs saved relatively when referencing the File System, or the
Internet, or both. 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. To
turn existing text into a link, highlight it before opening the Hyperlink
dialog.
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.
Insert -> Link to External Data.
Tools -> Options -> OpenOffice.org Base -> Databases
For video lectures Youtube – Platinum Classes Website : www.crackmycbse.com
Session 3 : Sharing Worksheet Data
In most office settings, there is a shared drive where teams can
store common files for everyone to use. This usually leads to sighting
of the message:
“The document [file name] is locked for editing by another user. To
open a read-only copy of this document, click“!!
This message appears because someone else already has the file
open. Sometimes however, it is necessary to have multiple people
working on a file at the same time. This can be to either speed up
data entry or simply make things easier for collaboration purposes.
Spreadsheet software allows the user to share the workbook and
place it in the network location where several users can access it
simultaneously. in this exercise, you will learn how to share a
worksheet.
Now to share the spreadsheets do the following.
At any time, you can set up a spreadsheet for sharing with others.
With the spreadsheet document open, choose Tools > Share
For video lectures Youtube – Platinum Classes Website : www.crackmycbse.com
Document to activate the collaboration features for this worksheet. A
dialog opens where you can choose to enable or disable sharing.
When you save a shared spreadsheet, one of several situations may
occur:
● If the worksheet was not modified and saved by another user since
you opened it, the worksheet is saved.
If the worksheet was modified and saved by another user since
you opened it, one of the following events will occur:
● If the changes do not conflict, the worksheet is saved, the dialog
below appears, and any cells modified by the other user are shown
with a red border.
● If the changes conflict, the Resolve Conflicts dialog is shown. You
must decide for each conflict which version to keep, yours or the
other person’s. When all conflicts are resolved, the worksheet is
saved. While you are resolving the conflicts, no other user can save
the shared worksheet.
● If another user is trying to save the shared worksheet and resolve
conflicts, you see a message that the shared spreadsheet file is
locked due to a merge-in in progress. You can choose to cancel the
For video lectures Youtube – Platinum Classes Website : www.crackmycbse.com
Save command for now, or retry saving later. When you successfully
save a shared spreadsheet, the worksheet shows the latest version
of all changes that got saved by all users.
Calc has the feature to track what data was changed, when the
change was made, who made the change and in which cell the
change has occurred. Edit > Changes > Record from the menu bar.
A colored border, with a dot in the upper left-hand corner, appears
around a cell where changes were made. Other reviewers then
quickly know which cells were edited. A deleted column or row is
marked by a heavy colored bar.
Calc automatically adds to any recorded change a comment
describing what was changed (for example, Cell B4 changed from ‘9’
to ‘4’). Reviewers and authors can add their comments to explain
their changes. Edit > Changes > Comments.
When you receive a worksheet back with changes, the beauty of the
recording changes system becomes evident. Now, as the original
author, you can step through each change and decide how to
proceed. To begin this process: Edit > Changes > Accept or Reject
For video lectures Youtube – Platinum Classes Website : www.crackmycbse.com
When sharing worksheets reviewers may forget to record the changes
they make. This is not a problem with Calc because Calc can find
the changes by comparing worksheets. In order to compare
worksheets you need to have the original worksheet and the one that
is edited. Edit > Compare Document.
Session 4 : Create and use Macros in Spreadsheet
A macro is a saved sequence of commands or keystrokes that are
stored for later use.
An example of a simple macro is one that “types” your address. The
OpenOffice.org (OOo) macro language is very flexible, allowing
automation of both simple and complex tasks. Macros are especially
useful to repeat a task the same way over and over again. Tools >
Macros > Record Macro
