1

UNIT 2

ELECTRONIC SPREADSHEET
(LIBRE OFFICE CALC)

1. In computers we can do the calculations on numbers

of records spread over the long sheet using the
spreadsheet software that helps to work with
numbers efficiently
2. It becomes easy to visualize a huge grid of numbers
when represented using charts in electronic
spreadsheet.
3. Advanced features of electronic spreadsheet using
Libreoffice calc to perform analysis, automate
repreated tasks, links, share and review data.
4. Macros is the another powerful feature of
spreadsheet.
5. Macros are a set of stored functions used to
automate process repeatedly
6. Macros tools which can be used to perform most of
the redundant tasks with relative ease.
7. To create multiple sheets and link the data in multiple
sheets can be done in 2 u ways.
* creating reference to other sheets by using keyboard
and mouse
* by linking external data
8. Libre office calc also provides the sharing feature
that allows to share the spreadsheet for editing
purpose.
9. Libre office calc saves to keep track of multiple
copies of the same spreadsheet corrected by different
users.
10. Sharing allows the users to work on the same
spreadsheet in collaboration.
 2

CHAPTER 4
ANALYSE DATE USING SENARIOS
AND GOAL SEEK

11. Spreadsheet is the process of extract useful

information for making effective decisions.
12. The Spreadsheet is one of the best software used for
data analysis.
13. Spreadsheet is used to retrieve, correlate, explore
and visualize data to identify patterns, trends and
relationship.
14. The spreadsheet component in Libreoffice known as
Calc includes several tools used to manipulate the
data in the spreadsheet.
15. Analyse the data using LibreOffice Calc

CONSOLIDATING DATA

16. Consolidate is a function used to combine

information from multiple sheets of the spreadsheets
into one place to summarize the information.
17. Consolidate data is used to view and compare
variety of data in a single spreadsheet for identifying
trends and relationships.
18. Match the labels from all the sheets which are used
for consolidating
19. To Consolidate data, select Data->Consolidate
option
20. The default function is sum is seen in the function
drop down
21. List of functions available on function drop down
22. The sources data range list contains existing named
ranges to quickly select fromt that.
23. Click on Add to add this range under the
„Consolidation ranges‟ of the consolidation dialog.
24. The target range specified under use copy result to
 3

25. Click on options that will list two check box under
consolidate by „Row labels‟ and „Column
labels‟ and link to source data under options.
26. Link to source data is checked to make the
modification automatically in the consolidated
(target) sheet while making any changes in the source
data.

GROUPS AND SUBTOTALS

27. Group and outline in Calc is used to create an

outline of the selected data and can group rows and
columns together. So that one can collapse + to hide
it or expand - it using a single click on it
28. To select the data to be grouped, click on Data-
>group and outline
29. The Subtotal tool in calc creates the group
automatically and applies common functions like
sum, average on the grouped data.
30. It can group subtotals by using category and sorts
them in ascending or descending order so that one
need not use filters.
31. The sheet where this is to be applied must have
labels to the column.
32. For subtotal, click Data menu and select Subtotals
(i.e) Data -> Subtotals
33. Grouping by list in the subtotal dialog which is to be
used for grouping the data in the sheet.
34. Under calculate subtotals for to create subtotals for.
35. Select he desired function, click the function under
use function.
36. Outline shows the hierarchical structure which can
be used to show or hide different levels by clicking on
the group indicators + sign to expand and - sign to
collapse the data.
37. If you want to remove the outline feature from the
sheet at any point of time then it is possible by just
 4

clicking on Data ->Group and Outline->Remove

outline

WHAT IF SCENARIOS

38. What if scenarios is a set of values that can be used

within the calculations in the spreadsheet.
39. A name is given to the scenario and several
scenarios can be created on the same sheet.
40. What is scenarios is used to explore and compare
various alternatives depending on changing
conditions.
41. What if scenarios can be used in the beginning of
any project to optimize the output.
42. What if Scenarios tool is used to predict the output
while changing the inputs which reflects the output
and thus one can choose the best plan of action based
on it.
43. To select multiple cells, hold Ctrl Key and click on
the cell to be selected .
44. To create scenarios, choose Tools->Scenarios
45. Enter a name for the new scenarios and leave the
other fields unchanged.
46. Create a new scenario which is activated
automatically.

WHAT IF ANALYSIS TOOL

47. What if tool is a planning tool for what if questions.

48. What if tool uses Data->Multiple operation and is
a planning tool for what if questions.
49. The Multiple operations creates a formula array to
display the list of results applying the formula on a
list of alternatives values used in the formula
50. Multiple operations tool uses two arrays of cells,
one array contains input values and the second
array uses the formula and display the result.
 5

51. Multiple operation tool is useful to check in the

beginning to understand from the output for the
efficiency.
52. What if analysis tool is very helpful when we want
to know how much profit we earn for a particular
product for a series of selling units.
53. Create an array of input values on the basis of which
the output is to be generated using the formula
54. To display the multiple operations dialog window,
click on Data ->Multiple operations.
55. Enter the cell address in the Formula bar from the
sheet which contains the formula.
56. Click on OK will generate all the possible outputs
based on the formula

GOAL SEEK

57. Goal Seek helps in finding out the input for the
specific output.
58. If you want to know the number of units produced to
get the desired output then use Goal Seek analysis
tool
59. To apply the Goal Seek, choose Tools->Goal Seek
60. Formula cell box will have the correct formula
61. place the cursor on the variable cell box and click on
the cell that contains the value to be changed.
62. Enter the desired result in the Target value box

CHAPTER 5
USING MACROS IN SPREADSHEET

1. Macros is used to automate repeated tasks that

performs on data.
2. A Macro is a single instruction that executes a set of
instructions.
 6

3. These set of instructions can be a sequence of

commands, or keystrokes that can be used for any
number of times later.
4. It is important to know that by default the Macro
recording feature is turned off when Libre Office
is installed on your computer.
5. To record a Macro ensure that the Macro recording
is enabled.
6. Macro recording can be enabled using Tools options
from main menu., select Tools->options-
>Libreoffice->Advanced
7. Check Mark on the option Enable Macro
Recording
8. To get Record Macro option, Tools->Macros
9. Selections are recorded only if they are done by using
cursor travelling , but not when the keyboard,
Mouse is used.
10. The Macro recorder works only in Calc and Writer
11. Click Record Macro option , recording of acions
starts and a small alert will be displayed.
12. Click Stop Recording button will stop the recording
of actions.
13. To save the macro in Save Macro to list box
14. The name of the macro by default is Main and is
saved in the standard library in module 1
15. Click Save button to save Recording Macro
16. A Library is acollection of modules which in turn is
a collection of macros.
17. If all the macros will be given will be given the same
name then they will overwrite the previous macro
created by that name
18. Rules for naming a macro, Module or a library.
* Begin with a letter
* Not contain spaces
* Not contain special characters except for under
scrore
 7

RUNNING A MACRO

19. Run the macro for another sheet which requires its
heading in cell A1 to be given the same format as
recorded in the format heading macro
20. To run a Macro, use Tools->Macros->Run Macro
to open the Macro Selector dialog box
21. Libre office macros library is provided by Libre
Office and contains modules with pre recorded
macros and should not be changed.
22. My Macros contain macros that we write or add to
Libre Office.
23. Untitled 1 is the name of the worksheet we are
working on.
24. Since at this time, we have not saved the worksheet
with another name it is showing Untitled 1
25. Select Library and Module in the library list
26. Select the macro in the macro name list
27. Click Run to run the macro.

CREATING AND ORGANISING A SIMPLE MACRO

28. The Recorded Macro is internally stored as

instructions written is a programming language that
are executed when the macro is executed or run.
29. We selected a library or module to store our
recorded macro, similarly while creating a macro,
either create a new library /module or edit an
existing module stored in a library.
30. To organize Macro, use Tools->macros->organize
macro->Libreoffice Base to open the Libreoffice
Basic Macro dialog window
31. Click organizer to open the Basic Macro organizer
dialog.
32. To create a new library containing modules, click on
Library->New
33. To create a module to store macro
 8

34. Choose Modules tab, select Module, click New to

create a new module
35. The module can be executed from the IDE by either
clicking the Run button or pressing F5
36. Consider a situation wherein you need to perform
calculations that are repetitive in nature. Assume that
the same formula needs to be applied to different data
in different sheets and there is no predefined function
for it. In such a situation will it not be convenient if
we could create a macro that performs the
calculations? It will save as the effort of remembering
and typing the formulas. It is possible to do so if we
use Macro as a function
37. Instead of writing instructions in between sub and
End sub , we can write instructions in between
Function and End function
38. A function is a capable of accepting arguments or
values.
39. A function can perform operations on the
arguments, perform calculations and return the result.
40. The text in grey is preceeded by single quote
indicating it is a comment.
41. A comment is written to write descriptive text to
support the code.

CHAPTER 6
LINKING SPREADSHEET DATA
1. Retyping or Copying the marks can be one solution
but it will be time consuming and also there are
chances of committing typing errors.
2. Link the data in multiple sheets can be done in two
ways.
3. Two ways to link the data in multiple sheets _ and __
Ans: 1. Creating reference to other
sheets/documents by using keyboard and mouse
2. by linking external data