SESSION 1: Analyze data using

SCENARIOS AND GOAL SEEK

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.
STEPS for Data Consolidation are :
1) Open the worksheet that contains the cell ranges to be
consolidated.
2) Choose the Consolidate option under the Data menu.
3) Select Source data range and click Add. The selected range
now appears on the Consolidation ranges list.
4) Select additional ranges and click Add after each selection.
5) Specify where you want to display the result by selecting a
target range from the Copy results to box.
6) Select a function from the Function list. The Sum function is
the default setting.
7) Select either Row labels or Column labels. The text in the
labels must be identical in all the specified Source range.
8) Click OK to consolidate the ranges.
NOTE : Use Data > Define Range to give name to a range
Creating Subtotals :
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. It is accessible from Data
menu.
Steps to insert subtotal values into a sheet:
1) Ensure that the columns have labels.
2) In the Subtotals dialog , in the Group by box, select the
column that you want to add the subtotals to.
3) In the Calculate subtotals for box, select the columns that
you want to subtotal.
4) In the Use function box, select the function.
5) Click OK.
Using “What If” 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.
Creating Scenarios :
1) Select the cells that contain the values that will change
between scenarios.
2) Choose Tools > Scenarios.
3) On the Create Scenario dialog , enter a name for the new
scenario. This name is displayed in the Navigator and on the
title bar of the scenario.
4) Optionally add some information to the Comment box.
5) Optionally select or deselect the options in the Settings
section.
6) Click OK to close the dialog
NOTE : You can create several scenarios for any given range of
cells
Goal Seek
Usually, you run a formula to calculate a result based upon
existing values. By contrast Goal Seek option under Tools
menu, helps to find values which will produce the result that
you want. for 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
Solver :
Solver option under Tools menu 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.
SESSION 2 : 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
and linked information. Multiple sheets help to keep the
information organized.
Inserting New Sheets
When you open a new spreadsheet, by default, it has a sheet
named Sheet1. 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:
1. Select Insert > Sheet from the menu bar, or
2. Right-click on the tab and select Insert Sheet, or
3. Click in an empty space at the end of the line of
sheet tabs.
Each method opens the Insert Sheet dialog box where you can
choose to put the new sheet before or after the selected sheet
and how many sheets to insert.

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.
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.
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
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.

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 dialog : Steps are
1. Open the Calc worksheet where the external data is
to be inserted. This is the target worksheet.
2. Select the cell where the upper left-hand cell of the
external data is to be inserted.
3. Choose Insert -> Link to External Data.
4. On the External Data dialog, type the URL of the
source worksheet
5. In the Available tables/range list, select the named
ranges or tables you want to insert.
6. Click OK to close this dialog and insert the linked
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. To register a data source
that is in *.odb format:
1. Choose Tools -> Options -> OpenOffice.org Base ->
Databases.
 2. Click the New button to open the Create Database
Link dialog.
3. Enter the location of the database file, or click
Browse to open a file browser and select the
database file.
4. Type a name to use as the registered name for the
database and click OK.

SESSION 3: SHARING WORKSHEET

DATA
Spreadsheet software allows the user to share the workbook
and place it in the network location where several users can
access it simultaneously. This is required to either speed up
data entry or simply make things easier for collaboration
purposes.
Setting Up A Spreadsheet For Sharing :
Open the spreadsheet document , choose Tools > Share
Document to activate the collaboration features for this
worksheet. A dialog opens where you can choose to enable or
disable sharing.
To enable sharing, select the box at the top of the dialog, and
then click OK. A message appears stating that you must save
the worksheet to activate shared mode. Click Yes to continue.
The word (shared) is then shown on the title bar after the
worksheet’s title.

Saving A Shared Spreadsheet :

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:
1. 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.
2. 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.
3. 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.
Note: Most spreadsheets software automatically turns off
some features in shared workbooks to simplify the workbook
since multiple people can be working on the file at the same
time. For example, shared workbooks don‘t allow merging
cells, conditional formatting, or inserting pictures/graphs/etc

Record Changes :
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. for example
If you are the sponsor of a youth baseball team. The coach has
submitted a budget to you and you are concerned that the
coach won’t see the changes you made, So you decided to use
Calc with the record changes feature turned on, so that the
coach can easily see the changes you have made.

How to turned on Record Changes feature ON :

1. Open the Shared Spreadsheet.
2. Select Edit > Changes > Record from the menu bar.
3. Begin editing the worksheet.
NOTE : A red colored border, with a dot in the upper left-hand
corner, appears around a cell where changes were made.

Viewing Changes :
Calc allows you to control what changes you see when
reviewing a worksheet. To change the available filters, select
Edit > Changes > Show You can filter based on:

1. Date – Only changes made in a certain time range are

displayed.
2. Author – Only changes made by a specific author are
displayed.
3. Range – Only changes made in a specific range of
cells are displayed.
4. Comment – Searches the content of the comments
and only displays changes which have comments.
5. Show accepted changes – Only changes you accepted
are displayed.
 6. Show rejected changes – Only changes you rejected
are displayed

Adding Comment to a Change :

1. Make the change to the spreadsheet.
2. Select the cell with the change.
3. Choose Edit > Changes > Comments.
4. Type your own comment and click OK.
NOTE : You can see the comment by hovering the mouse
pointer over the cell.

Editing Comment :
1. Select the cell with the comment that you want to
edit.
2. Select Edit > Changes > Comments.
3. Edit the comment and click OK.

Accepting or Rejecting Changes :

When you receive a worksheet back with changes. Now, as the original
author, you can step through each change and decide which change to
accept and which one to reject. To begin this process:

1. Open the edited worksheet.

2. Select Edit > Changes > Accept or Reject.
3. Calc steps through the changes one at a time. You
can choose to accept or reject each change

Merging Worksheets :
Sometimes, multiple reviewers return edited versions of a
worksheet at the same time. In this case, Calc provides the
feature of merging worksheets
1. Open the original worksheet.
2. Select Edit > Changes > Merge Document.
3. A file selection dialog opens. Select a file you want to
merge and click OK.
4. Accept or Reject Changes dialog opens and you can
accept or reject the changes.
NOTE : Changes from different authors appear in different
colors in the worksheet.
Comparing Documents :
When sharing worksheets reviewers may forget to record the
changes they make. 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. To compare them:
1. Open the edited worksheet that you want to
compare.
2. Select Edit > Compare Document.
3. An open worksheet dialog appears. Select the
original worksheet and click Insert.
4. Calc finds and marks the changes
SESSION 4: CREATE AND USE
MACROS IN SPREADSHEET
Macro :
A macro is a saved sequence of commands or keystrokes that
are stored for later use. Macros are especially useful to repeat
a task the same way over and over again

Using the macro recorder :

Use Tools > Macros > Record Macro to start the macro
recorder. The Record Macro dialog is displayed with a stop
recording button. Click Stop Recording to stop the macro
recorder.

Advantages of using Macro in Calc :

1. Macros automates the repetitive and routine tasks.
2. Macros speed up your process and reduce time.