What-if Scenarios

●​ What-if scenario is a set of values that can be used within the

calculations in the spreadsheet.
●​ It can be used in the beginning of any project to optimize the output.
●​ This tool is used to predict the output while changing the inputs and
thus one can choose the best plan.

Steps to create scenario are given below

1.​ Select the cells which contain values in the sheet that need to
be changed.
2.​ Choose Tools>Scenarios.
3.​ Enter a name for the new scenario.
4.​ Click on OK button

What-if Analysis Tool

●​ What-if tool uses Data > Multiple Operations and is a planning tool for
what-if questions.
●​ The Multiple Operations tool creates a formula array to display the list
of results on a list of values used in the formula.
●​ This tool uses two arrays of cells, one array contains the input values
and the second array uses the formula and display the result.
●​ What-if analysis tool is very helpful to know how much profit we earn
for a particular product for a series of selling units.

Following steps are used for what-if analysis tool:

1.​ Enter the data in the cells and then enter a formula to calculate a
result from the data entered.
2.​ Create an array of input values on the basis of which the output
is to be generated.
3.​ Select the cell range of input array and output array.
4.​ Click on Data>Multiple Operations
 5.​ Enter the cell address where we applied formula in the dialog
box.
6.​ Enter the address of variable cell.
7.​ Click on OK

Goal Seek
●​ In general we fill in the values in the cells and then create formula on
these values to get the required result.
●​ Goal seek helps in finding out the input for the specific output. for
example

Steps for Goal Seek are given below:

1.​ Enter the values and write the formula in the cell.
2.​ Choose Tools > Goal Seek.
3.​ Enter the address of cell in the “formula cell” box.
4.​ Enter the address of cell whose value is to be predicted or
unknown in “Variable cell” box.
5.​ Enter the target value and click on OK.

Recording a Macro
●​ A macro is a single instruction that executes a set of instructions.
●​ These set of instructions can be a sequence of commands or
keystrokes that can be used for any number of times later.
●​ By default the macro recording feature is turned off when LibreOffice
is installed on our computer.
●​ Macro recording can be enabled by selecting Tools > Options >
LibreOffice > Advanced.
●​ Observe the Optional Features.
●​ There are two options which are not check marked.
●​ Put the checkmark on the option “Enable macro recording”

Following actions are not recorded in Macro

 1.​ Opening of windows
2.​ Actions carried out in another window than where the recording
was started.
3.​ Window switching
4.​ Actions that are not related to the spreadsheet contents. For
example, changes made in the Options dialog, macro organizer,
customizing.
5.​ The macro recorder works only in Calc and Writer.

Steps given below to record a macro

1.​ Click on Tools > Macros and then click on the Record Macro.
2.​ Now start taking actions that will be recorded.
3.​ Click on “Stop Recording” button to stop the recording of
actions.
4.​ Basic Macros dialog window open to save the Macro.
5.​ Select the object in the Save Macro to list box.
6.​ Type the name of the macro and click on Save button.

NOTE: By default the name of the macro is Main and is saved in the
Standard Library in Module1. A Library is a collection of modules which in
turn is a collection of macros.

Rules for naming a Macro, Module or a Library:

While naming a Macro, Module or a Library the name should :

1.​ Begin with a letter

2.​ Not contain spaces
3.​ Not contain special characters except for _ (underscore)

Running a Macro
To run a macro we need to perform the following steps.

1.​ Click Tools > Macros > Run Macro

 2.​ Macro Selector dialog box will open.
3.​ Select the library that contains the macro then select the macro
under ‘Macro name’.
4.​ Click on Run to run the macro.

Code of a Macro
●​ The action recorded by a macro is recorded as instructions in a
programming language called BASIC.
●​ It is also possible to view and thus edit the code of a macro.
●​ But remember, it is advised to edit a macro only if you have
knowledge of the language.
●​ We can view the code generated for the macros by going to Tools >
Macros > Edit Macros.
●​ Choose the macro name from the Object Catalog and the associated
code will be visible.

NOTE: The code of a macro begins with Sub followed by the name of the
macro and ends with End Sub. Do not make any changes to the code
unless you are aware of the language.

Creating and Organising a Simple Macro

Steps to organize the macro.

1.​ Click on Tools > Macros > Organize Macros > Basic
2.​ Basic Macro Dialog window open.
3.​ Click Organizer to open the Basic Macro Organizer dialog.
4.​ Click on Library > New to create library to store macro.
5.​ Click on Module tab and then New to create Module to store
macro.
Inserting New Sheet in Spreadsheet
In LibreOffice Calc, by default there is only one sheet but we can insert
multiple sheets. There are three ways to insert new sheet.

1. To add a new sheet in the spreadsheet, click on the Add new sheet
icon(+) sign located on the Sheet tab of the spreadsheet

2. Right click anywhere on the sheet tab and select Insert sheet option from
the drop-down list. Insert Sheet dialog box will open as shown below. It
gives us a choice to put the new sheet, after current sheet or before current
sheet or assign the name of the sheet etc.

3. Click on Sheet>insert sheet to open Insert sheet dialog box which will
help us to insert a new sheet.

Creating Reference to Other Sheets by

Using Keyboard and Mouse

Creating reference using Mouse

Create the ‘Sheet1’ and ‘Sheet2’ in LibreOffice Calc as shown below:

Now we want the total marks in another Sheet named ‘Result’

To calculate the final marks for English in ‘Result’ sheet, follow the
following steps.

1. First copy the Student Name and subject Name from ‘Sheet1’ to ‘Result’
sheet.

2. Type =SUM() in a cell and click between the brackets.

3. Now click on the ‘Sheet1’ sheet and click the English Marks for the first
student and write (+) for the next value. Now click on the ‘Sheet2’ sheet and
click the English Marks for the first student as shown below

4. Press Enter key, the total marks of subject English will be displayed in
cell B2 of ‘Result’ sheet.

5. Then use fill handle to fill the cells up to the last student’s data. You can
copy the same formula for other subjects

NOTE: Any changes made to marks in ‘Sheet1’ and ‘Sheet2’ sheet will be
reflected in the ‘result’ sheet as well. That is how the sheets are linked
together

Creating reference using Keyboard

●​ To refer to a cell in another sheet precede the cell reference with a ‘$’
sign.
●​ It is then followed by the name of the sheet in ‘ ’ (single quotes)
followed by a . (dot) and then the cell address.
●​ For example, to refer a cell B2 of sheet named Sheet1 we will type:
$‛Sheet1’.B2

In above practical, we can directly type the following formula in cell B2 of

Sheet named ‘Result’ and then drag the formula to calculate the sum of the
marks

=SUM($’Sheet1′.B2 + $’Sheet2′.B2)

OR

=SUM($Sheet1.B2 + $Sheet2.B2)

NOTE: Single quotes (‘ ’) are mandatory if there is a space in the Sheet

name like ‘Sheet 1’.
NOTE: To refer to a cell in a different spreadsheet we write in single quotes
the path of the file followed by #$ then the name of the sheet followed by a .
(dot) and then the cell address.​
For example: ‘[Link]

The path of a file has three forward slash ///. A filename can have space
within its name hence single quotes (‘ ‘) are used. It is also possible to
insert a sheet from another file. The From file option of Insert Sheet Dialog
box allows us to insert sheet from another file as well.

Hyperlinks to the Sheet

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.

Relative and Absolute Hyperlinks

A hyperlink can be either absolute or relative. An absolute hyperlink stores
the complete location where the file is stored. So, if the file is removed from
the location, absolute hyperlink will not work. For example:
C:\Users\ADMIN\Downloads\[Link] is an absolute link as it defines the
complete path of the file.

A relative hyperlink stores the location with respect to the current location.
For example: Admin\Downloads\[Link] is a relative hyperlink as it is
dependent on the current location. If the complete folder containing the
active spreadsheet is moved the relative link will still be accessible as it is
bound to the source folder where the active spreadsheet is stored.

Creating Hyperlinks
Suppose, you have to hyperlink a “Sheet1” of “Result-X-A” spreadsheet
document in the “Result-X-B” spreadsheet document, then follow the
following steps:
1. Open the “Result-X-B” spreadsheet document.

2. Click on Insert > Hyperlink. An Hyperlink dialog box will open.

3. Click on the Document on the left pan of dialog box. Click on the button
located after the Path. Select the document “Result-X-A”

4. Then click on the Target button to choose the sheet which is to be

hyperlinked. Here in our case we will select the sheet “Sheet1” as shown
above.

5. Click on Apply and Close button.

6. Enter the text in the Text box to assign the hyperlink to that text.

7. Click on Apply and Close button.

NOTE: To open the hyperlinked sheet, press the Ctrl key and click on the
hyperlinked word “ResultX-A”, the sheet will be opened in the new window

Editing Hyperlinks
To edit an existing link, place the cursor anywhere in the link. Right click
and choose Edit Hyperlink…, the Hyperlink dialog box will be displayed,
where we can do required changes in the hyperlink.

On clicking the Remove Hyperlink option, the link will be removed from the
text.

Linking to External Data

Internet is a rich source of information, which is stored in the form of web
pages. The versatility of a spreadsheet allows us to insert tables from
HTML documents into Calc. The steps for the same are given below
1. Open the spreadsheet where external data is to be inserted.

2. Select Sheet > External Links…

3. The External Data dialog box will open.

4. Type the URL of the source document and press enter.

5. A dialog box is displayed to select the language for import. Selecting

Automatic shows data in the same language as in the webpage.

6. From the Available Tables/Ranges list, choose the desired table and click
OK. (as shown below)

7. Table will be inserted in the spreadsheet

NOTE: If you choose HTML_all option, then the entire HTML document is
selected.

Linking to Registered Data Sources

LibreOffice Calc allows us to link spreadsheet documents with databases
and other data sources. The data source needs to be registered with
LibreOffice.

The extension of LibreOffice Base is .odb. To register a data source that is

in *.odb format, follow the steps given below.

1. Select Tools > Options > LibreOffice Base > Databases. The Options –
LibreOffice Base-Databases dialog box appears.

2. Click the New button to open the Create Database Link dialog box.

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