SESSION 1: ANALYZE DATA USING SCENARIOS AND

GOAL SEEK
 Consolidating data
o 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.
o 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 subtotals
o 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.
o 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.
o 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
o 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.
o 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.
o 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
o To create a scenario, select all the cells that provide the data for the scenario.
o Tools > Scenarios
 Goal Seek
o Using Goal Seek option under Tools menu, you can discover what values will
produce the result that you want.
 Using Goal Seek
o 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.
 Using the Solver
 o 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.
o 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.
o 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.
o For arguments that you would like to change, you need to add two rules to define a
range of possible values:
o 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.

SESSION 2: Link Data and Spreadsheets Using

Multiple Workbook 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
o 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
o 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. Or, select Home > Insert > Insert Sheet.
 Renaming Worksheets
o There are three ways you can rename a worksheet
 Double-click on one of the existing worksheet names.
 Right-click on an existing worksheet name, then choose Rename from the
resulting Context menu.
 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
o 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 > [Link]
Calc > General > Updating, whenever the file is opened.
  Create or change a cell reference
o 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.
o 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
o There are two ways to reference cells in other sheets: by entering the formula
directly using the keyboard or by using the mouse.
o 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.
o 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 : (=’[Link] &File
Name’#$[Link])
 Working with Hyperlinks
o 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
o 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.
o 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
o You can insert tables from HTML documents, and data located within named ranges
from an [Link] Calc or Microsoft Excel spreadsheet, into a Calc
spreadsheet.
o You can do this in two ways: using the External Data dialog or using the Navigator.
o Insert -> Link to External Data.
o Tools -> Options -> [Link] Base -> Databases

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 is simultaneously. in this exercise, you will learn how
to share a worksheet.
 Now to share the spreadsheets do the following.
o At any time, you can set up a spreadsheet for sharing with others. With the
spreadsheet document open, choose Tools > Share Document to activate the
collaboration features for this worksheet. A dialog opens where you can choose to
enable or disable sharing.
o 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 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 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
 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 [Link] (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