X-IT (402) 2026-27

UNIT-2: ELECTRONIC SPREADSHEET (Advanced)

Ch.4: ANALYSE DATA USING SCENARIOS AND GOAL SEEK

KEY POINTS
➢ Analysing data is the process to extract useful information for making effective
decisions.
➢ Consolidate is a function used to combine information from multiple sheets of the
spreadsheet into one place to summarize the information.
➢ Consolidate option is available under Data tab. “Sum” is the default function of
Consolidate dialog box.
➢ The Subtotal tool in Calc creates the group automatically and applies common
functions like sum, average on the grouped data.
➢ It can group subtotals by using category and sorts them in ascending or descending
order so that one need not to use filters.
➢ Subtotal feature is available under Data tab. You can also use the 2nd Group and
3rd Group tabs to group the data in further levels.
➢ What-if scenario- It refers to set of values used to explore and compare various
alternatives based on changing conditions. It allows you to create different scenarios
on the same sheet.
➢ What-if tool – This tool is a planning tool for what-if questions. In this, the output
is not shown in the same cells, where as it uses a drop-down list to display the
output depending upon the input.
➢ Goal Seek is used to set a goal to find the optimum value for one or more target
variables, given with the certain conditions.
➢ Solver is the elaborate form of Goal Seek. It deals with equations with multiple
unknown variables.
➢ Scenario, Goal Seek and Solver features are available under Tools tab.

SUBJECTIVE TYPE QUESTIONS

1. What is Data Consolidation? Why do we consolidate data?
Ans: Data Consolidation means combining data from different sources into one place. 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. We consolidate
data to collect the contents of cells from several worksheets to a single worksheet.

Page: 1
X-IT (402) 2026-27

2. What are the criteria for consolidating sheets?

Ans: Criteria for consolidating sheets are:
1. Data types across all the sheets to be consolidated should be same.
2. Label should match from all the sheets which are used for consolidating.
3. Designate the first column as the primary column on the basis of which the data
is to be consolidated.
3. Differentiate between:
i. What-if scenario and What-if tool:
Ans: What-if Scenario: This refers to a set of values used to explore and compare various
alternatives based on changing conditions. It allows you to create different scenarios on
the same sheet, each with some different values.
What-if tool: This tool is a planning tool for what-if questions. In this, the output is
not shown in the same cells, whereas it uses a drop-down list to display the output
depending upon the input.
ii. Goal Seek and Solver:
Ans: Goal seek: Goal Seek in LibreOffice Calc is a feature that helps to find the right input
value for a formula to achieve the desired result. In other words we can say that it
helps in finding out the input for the specific output.
Solver: Solver follows the Goal Seek method. It is more elaborate form of Goal Seek.
The only difference between Goal Seek and Solver that the Solver can deals with
equations having multiple unknown variables. It is specifically designed to minimize or
maximize the result according to a set of rules that you define.
4. Explain about Subtotal in Calc.
Ans: The Subtotal feature is used to automatically create groups and apply inbuilt
functions like Sum, Count, Average, etc., to summarize the data. Subtotal function
is listed under the Mathematical category.
5. How can you name a range of cells?
Ans: To name a range of cells in LibreOffice Calc, you can do the following:
1. Select the range of cells you want to name
2. Go to Insert > Names > Define
3. In the Define Names dialog, type in a name for the range
4. Click Add and then OK

Page: 2
X-IT (402) 2026-27

6. Give any two advantages of data analysis tools.

Ans: Advantages of data analysis tools.

• Data analysis tool is used to retrieve, correlate, explore, and visualize the data.
• Data analysis tool is used to identify patterns, trends, and relationships.
• Data analysis tool is used to analyse the data and interpret the result from it.
• Data analysis is very useful in the beginning of any project to optimize the
output.
• Data analysis 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.
7. Which tool is used to create an outline for the selected data?
Ans: The Group and Outline tool 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.
8. Name any two tools for data analysis.
Ans. Tools used for data analysis are:
• Consolidating Data
• Groups and Subtotals
• What-if Scenarios
• Goal Seek
• What-if Analysis Tool
9. Explain the use of Scenarios.
Ans: The ‘Scenarios’ tool enables you to analyse the data by putting different input
values. In contrast, the ‘Multiple Operations’ tool creates a formula array, i.e.,
displays the result of applying formula to a list of alternative values for variables in
a separate range of cells.
10. Which tool is used to create an outline for the selected data?
Ans: The Groups and Outline feature in Calc is used to create an outline for the selected
data. It allows you to group data based on rows or columns and helps in expanding
or collapsing the grouped data for better understanding.

Page: 3
X-IT (402) 2026-27

11. Explain how the Scenario and Goal Seek tools differ in terms of functionality and
use case.
Ans:

Feature Scenario Manager Goal Seek

Create and compare multiple data Find required input for a specific
Purpose
situations output
Comparing different plans or Finding target value (e.g., break-
Use Case
budgets even)
Output Multiple results saved as scenarios One-time calculated value

COMPETENCY BASED QUESTIONS

1. Vijay is taking part in an election, where he requires 66% of votes to win the
election. Assuming that there are 200 total voting members, and currently he has only
98 votes, which is not a sufficient number because it only makes 49% of the total
voters. He wants to calculate how many more votes does he need? Suggest a feature of
Excel that he should use to get the value.
2. Vijay can use Goal Seek feature. 2 Mr. Amit, Sales Manager of ABC Sales
Corporation has created a spreadsheet in LibreOffice Calc that lists Sales for different
years in different regions in different worksheets. He wants to summarize and make
certain decisions based on it.
Help him by answering the following questions:
a. Which tool in Calc can be used to combine the sales data from multiple
sheets into a single summary sheet?
b. Name the Menu Option and Sub-Menu Option that can be used to generate
combined summary of all the worksheets.
c. Name the function that can be used to display total of all sales.
d. He wants to open a summary document stored at a different location from
within the sheet by clicking on a text stored in a cell. How can it be done?

A. a. Consolidate Tool
b. Menu : Data
Sub-menu : Consolidate
c. Function : SUM
d. Use HYPERLINK function

Page: 4
X-IT (402) 2026-27

3. Ritu runs a shop and uses a spreadsheet to calculate monthly profit. She wants to know
how her profit would change if the rent increases or sales decrease. Which feature
should she use and why?

A. Ritu should use the Scenario feature. This allows her to create different "what-if"
versions of her data by changing input values like rent or sales, and comparing the
outcomes without altering the original data.
4. A teacher wants to show students how their total marks would change if they
scored differently in a subject. How can she do this using spreadsheets?
A. The teacher can use the Scenario Manager to create multiple scenarios such as:
• “Improved Science Marks”
• “Extra Credit Added”
Each scenario will show different total marks based on changed input values.
Steps:
1. Go to Tools > Scenarios.
2. Define each scenario by changing the required cell values.
3. View each scenario to see the effect.

MULTIPLE CHOICE QUESTIONS

1. allows you to gather data from different worksheets into a master
worksheet.
a. Subtotal b. Goal Seek c. Solver d. Data Consolidation
2. We can consolidate data by .
a. Row Label b. Column Label c. Both a & b d. None of these
3. Which of the following function(s) are available in consolidate window?
a. Max b. Min c. Count d. All of these
4. If you select then any values modified in the source range are automatically
updated in the target range.
a. Link to source data b. Link to sheet data
c. Link to original data d. Link to source range
5. Which option is used to name a range of cells?
a. Range name b. Cell Range c. Define Range d. Select Range
6. Subtotals data arranged in an array (group of cells).
a. Add b. Average c. Find d. Clear

Page: 5
X-IT (402) 2026-27
7. In Subtotals we can select up to groups of arrays.
a. 3 b. 2 c. 4 d. Infinite
8. We can shift from one scenario to another by .
a. Navigator b. Data Source b. Data filter d. None of these
9. Scenarios are tool to test questions.
a. if else b. what else c. what if d. if
10. Identify the correct sequence.
a. First open subtotals window and then select the data where we need to apply
subtotals.
b. First Select data and then open subtotals window.
c. Both of the above are correct
d. None of these
11. Which option is suitable to calculate the effect of different interest rates on an
investment?
a. Scenario b. Subtotal c. Consolidate d. None of these
12. Default name of first scenario created in Sheet1 of Calc is .
a. Sheet1_Scenario1 b. Sheet1_Scenario_1
c. Sheet_1_Scenario1 d. Sheet_1_Scenario_1

13. is more elaborate form of Goal Seek.

a. Scenario b. Subtotal c. Solver d. All of these
14. Which of the following elements are present in “Insert Sheet” dialog box.
a. After Current Sheet b. Before Current Sheet
c. No. of Sheets d. All of these
15. We can rename an existing sheet in Calc by .
a. Double click on one of the existing sheet.
b. Right click on existing sheet and then choose rename.
c. Both a & b.
d. None of these.
16. Formula to refer a cell A3 in sheet named S1 is .
a. =S1A3 b. =S1.A3 c. =’S1′.A3 d. None of these
17. The cell reference for cell range G2 to M12 is .
a. G2.M12 b. G2;M12 c. G2:M12 d.G2=M12

Page: 6
X-IT (402) 2026-27

18. Rohit scored 25 out of 30 in English, 22 out of 30 in Maths. He wants to calculate

the score in IT he needs to achieve 85 percent in aggregate. Suggest him the suitable
option out of the following to do so.
a. Macro b. Solver c. Goal Seek d. Subtotal
19. feature of Calc is used to test ‘what-if’ questions.
a. Solver b. Goal Seek c. Scenario d. Styles
20. feature adds data arranged in a group of cells in Calc with labels for
columns and/or rows.
a. Average b. Subtotal c. Goal Seek d. Solver
21. Which of the following is a best tool if you have a problem with the multiple
unknown variables?
a. Goal Seek b. Subtotals c. Scenarios d. Solver
22. Which function cannot be performed through Subtotal in a Spreadsheet?
a. Sum b. Product c. Average d. Percentage
23. State whether True or False.
“Data can be consolidated from two sheets only”.
a. True b. False
24. State whether True or False.
“We can create only 3 scenario for a given range of cells”.
a. True b. False

25. Which of the following feature is not used for data analysis in spreadsheet?
a. Page layout b. Goal Seek c. Subtotal d. Consolidating data
26. Which of the following office tool is known for data analysis?
a. Writer b. Calc c. Impress d. Draw
27. Which of the following operations cannot be performed using LibreOffice Calc?
a. Store and manipulate data b. Create graphical representation of data
c. Analysis of data d. Mail merge
28. What is the extension of spreadsheet file in Calc?
a. .odb b. .odt c. .odg d. .ods
29. The default function while using Consolidate is .
a. Average b. Sum c. Max d. Count

Page: 7
X-IT (402) 2026-27

30. Group by is used in tool to apply summary functions on columns.

a. Consolidate function b. Group and Outline
c. What-if scenario d. Subtotal tool
31. Which tool is used to predict the output while changing the input?
a. Consolidate function b. What-if scenario
c. Goal seek d. Fine and Replace
32. Which of the following is an example for absolute cell referencing?
a. C5 b. $C$5 c. $C d. #C
33. analysis tool works in reverse order, finding input based on the output.
a. Consolidate function b. Goal seek
c. What-if analysis d. Scenario
34. Which of the following is the correct sequence to remove Subtotals from your
worksheet, within the range?
a. Choose Data > Subtotal > Remove. b. Choose Subtotal > Data > Remove
c. Choose Remove> Subtotal > Data d. Choose Data > Remove > Subtotal
35. When you use the Subtotals feature in a worksheet, a hierarchy of groups appears,
known as an Outline. To remove the outline from the worksheet, Click on
.
a. Data > Group and Outline > Remove Outline.
b. Group and Outline > Data > Remove Outline.
c. Remove Outline > Data > Group and Outline.
d. None of these

36. To delete a scenario, right-click on it in the and select Delete.

a. Status bar b. Navigator pane c. Sidebar d. None of these
37. Which of these actions should be perform first while using the Subtotals command?
a. Sort Data b. Filter data c. Format Data d. Consolidate Data
38. Pooja has last year sales report of North, East, West, and South Zones. She wants
to combine all the data to view the total sales of every month. Which feature of Calc
should she use?
a. Consolidate b. Subtotal c. Solver d. Goal Seek
39. While consolidating data, a cell range can be named using option.
a. Name range b. Consolidate name

Page: 8
X-IT (402) 2026-27
c. Define range d. Define name
40. is a set of values that can be used within the calculations in the
spreadsheet to explore and compare various alternatives depending on changing
conditions.
a. Sort b. Filter c. What-if Scenarios d. Comments

BLANKS
1. Consolidate function is used to combine information from multiple sheets to
the information.
2. Data can be viewed and compared in a single sheet for identifying trends and
relationships using function.
3. under Data menu can be used to combine information from multiple
sheets into one sheet to compare data.
4. The tool in Calc creates the group automatically and applies functions on
the grouped data.
5. scenario is used to explore and compare various alternatives depending
on changing conditions.
6. is a planning tool for what-if questions.
7. What-if analysis tool uses array of cells, one array contains input values
and the second uses the .
8. helps in finding out the input for the specific output.
9. If you want to link the data of the consolidated range to the source data, then
click on Options and select the Link to Source data option in the in the
dialog box.

STATE WHETHER THE FOLLOWING STATEMENTS ARE TRUE OR FALSE

1. Consolidate function is used to combine information from two or more sheets
into one.
2. The Consolidate function cannot be used to view and compare data.
3. Link to source data is checked updates the target sheet if any changes made in
the source data.
4. Using subtotal in Calc needs to use filter data for sorting.
5. Subtotal tool can use only one type of summary function for all columns.
6. Only one scenario can be created for one sheet.
7. What-if analysis tool uses one array of cells.

Page: 9
X-IT (402) 2026-27

8. Goal seek analysis tool is used while calculating the output depending on the
input.
9. The output of What-if tool is displayed in the same cell.

Answers:
MCQS
1. d 2.c 3. d 4.a 5.c 6.a 7.a
8. a 9.c 10.b 11.a 12.b 13.c 14.d
15. c 16.b 17.c 18.c 19. c 20.b 21.d
22. d 23.b 24.b 25.a 26.b 27.d 28.d
29.b 30.d 31.b 32.b 33.b 34. a 35.a
36.b 37.a 38.a 39.c 40. c

BLANKS
1. Summarize 2. Consolidate 3. Subtotal 4 Subtotal
5. What-if 6. What-if tool 7. Two, Formula and display output
8. Goal seek 9. Consolidate
True or False

1. True 2. False 3. True 4. False 5. False 6. False 7. False

8. False 9. False

Page: 10
X-IT (402) 2026-27

Ch.5: USING MACROS IN A SPREADSHEET

KEY POINTS
➢ The Macro feature of Calc allows you record a set of actions that you perform
repeatedly in a spreadsheet.
➢ Macro recorder is tool that allows you to record macros. By default the macro
recording feature is turned off.
➢ The Macro Recorder has some limitations, some of the actions cannot to recorded
like, Opening of windows, Actions carried out in another window than where the
recording was started and Window switching.
➢ 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.
➢ While naming a Macro, Module or a Library the name should: Begin with a letter,
Not contain spaces, Not contain special characters except for _ (underscore).
➢ LibreOffice macros are usually written in a language called LibreOffice Basic.
➢ The code of a macro begins with Sub followed by the name of the macro and ends
with End Sub.
➢ The module can be executed from the IDE by either clicking the Run button or pressing
F5.
➢ A function is a line of code that executes when you call it. When you invoke a
function, it returns a value.
➢ To define a macro as function, use the keyword Function. Each function has a name
and may have parameters whose values you pass when you invoke the function.
SUBJECTIVE TYPE QUESTIONS
1. What is a Macro? List any two real-life situations where they can be used.
Ans: A macro is a sequence of instructions or commands that automate repetitive tasks in
software applications. 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. A sequence of actions such as
keystrokes and clicks can be recorded and then run as per the requirement.
Two real-life situations are
• Typing school name, address, contact numbers with a specific formatting
• Apply the same formula at a particular cell for different sheets in a workbook
• Data Entry

Page: 11
X-IT (402) 2026-27
• Document Formatting

2. How can we record a Macro? List any two advantage of macros.

Ans: Step 1. Click on Tools > Macros and then click on the Record Macro option.
Step 2. Now start taking actions that will be recorded.
Step 3. Once you click on Record Macro option, recording of actions starts and a
small alert will be displayed. Clicking on “Stop Recording” button will stop the recording
of actions.
Step 4. This will open the Basic Macros dialog window to save and run the created
macro.
Step 5. To save the macro, first select the object where you want the macro to be saved
in the Save Macro to list box.
Step 6. The name of the macro by default is Main and is saved in the Standard
Library in Module1. You can change the name of the macro.
Step 7. Click on Save button.
A Library is a collection of modules which in turn is a collection of macros.
Advantages: It will ensure that we maintain the standardization in terms of font
style without any typing mistake.

3. List the actions that are not recorded by a macro.

Ans: The following actions are not recorded by a Macro.
• Opening of windows.
• Actions were carried out in another window than where the recording was
started.
• Window switching.
• Actions that are not related to the spreadsheet contents. For example, changes
made in the Options dialog, macro-organizer, and customizing.
• Selections are recorded only if they are done by using the keyboard (cursor
traveling), but not when the mouse is used.
• The macro recorder works only in Calc and Writer.

Page: 12
X-IT (402) 2026-27

4. Write the syntax to define a macro as a function.

Ans: Syntax:
Function Function_Name( )
Body of Function
Function_Name=Result
End Function
5. How is LibreOffice Macros Library different from my Macros?
Ans:
LibreOffice Macros Library My Macros
This library is inbuilt in LibreOffice. This is user defined library.
This library contains inbuilt macros This library contains macros recorded by
which cannot be changed. user which cannot changed at any time.

6. Differentiate between predefined functions in Calc and Macros as a function.

Ans:
Predefined function Macros as a function
These are built in functions These are user defined functions.
It does not involve any programming It involves writing code in Basic.
It cannot be customized It can be customized
It can return values & are precompiled Macro does not

7. List the rules that should be kept in mind while naming a macro.
Ans: Rules that should kept in mind while naming a Macro, Module or Library.
• The name should begin with a letter.
• The name should not contain spaces.
• The name should not contain special characters except for _ (underscore)

8. By default, which library is located in Calc?

Ans: The name of the macro by default is Main and is saved in the ‘Standard Library’ in
Module1.

Page: 13
X-IT (402) 2026-27

COMPETENCY BASED QUESTIONS

1. Tina has created a macro. Suggest how she can run the created macro.
A. Tina can follow the steps given:
• To run a macro, select the Tools menu on the menu bar and choose Macros
> Run Macro.
• The Macro Selector dialog box opens. Locate your macro and select it. For
example, click on My Macros > Standard > Header > Macro.
• Click on Run.
2. Rohan spends a lot of time formatting cells in the same way (bold, red font, and
border). How can using a macro help him save time?

A. Rohan can record a macro to automate the cell formatting. Once recorded, he can
apply the same formatting with a single click or keyboard shortcut, saving time and
ensuring consistency.
Steps:
1. Go to Tools > Macros > Record Macro.
2. Format a cell (bold, red, border).
3. Stop recording and save the macro.
4. Assign it to a button or shortcut.
3. Priya created a macro to apply currency formatting to sales data. She wants to use
it in another spreadsheet. What must she do to make the macro available there?

A. Priya needs to store the macro in “My Macros” or in a shared library, or export the
macro and then import it into the new spreadsheet.
Steps:
• Save macro in My Macros > Standard.
• In the new file, go to Tools > Macros > Organize Macros > LibreOffice Base and
import the module if needed.
4. Nithin maintains his daily expanses in a spreadsheet. He calculate the total
expanses and applies some formatting weekly. Suggest a feature of Calc using
which he can do the task quickly rather than by repeating the entire steps every
time.
A. Nithin can create macros for the task he repeats weekly. A macro is used to record
a set of actions that we perform repeatedly in a spreadsheet.

Page: 14
X-IT (402) 2026-27
MULTIPLE CHOICE QUESTIONS
1. A is a saved sequence of commands or keystrokes that are stored for later use.
a. Solver b. AutoSum c. Consolidate d. Macro
2. Macros are especially useful to the same way over and over again.
a. Report a task b. Repeat a task
c. Reject a task d. Comment a task
3. Use Macro to start the macro recorder.
a. Tools > Macros > Record Macro b. Tools > Record > Record Macro
c. Data > Macros > Record Macro d. None of these
4. Click to stop the macro recorder.
a. Close Recording b. End Recording
c. Stop Recording d. None of these

5. To edit macro, go to .
a. Tools> Macros> Organize Macros b. Edit> Macros> Organize Macros
c. View> Macros> Organize Macros d. None of these
6. When a document is created and saved, it automatically contains a library
named .
a. Module Library b. Macro Library c. Standard Library d. None of these
7. An argument can be passed through a macro function .
a. By Value b. By Reference c. Both a & b d. None of these
8. State whether True or False.
“Function names in Calc are not case sensitive”.
a. True b. False

9. Macro Recordings can be enabled from the option in the menu bar.
a. Sheet b. Data c. Tools d. Window.
10. Which of the following is a valid Macro Name?
a. 1formatword b. format word c. format*word d. Format_word
11. Identify which of the following is a programming Language.
a. Calc b. BASIC c. Writer d. Macro.
12. Which of the following Libraries contains modules with prerecorded macros and
should not be changed?
a. My Macros c. Untitled1 d. Test d. LibreOffice Macros

Page: 15
X-IT (402) 2026-27

13. The Module can be executed from the IDE by pressing .

a. F3 b. F4 c. F5 d. F6
14. Which of the following is the default name of the Macro .
a. Default b. Main c. Macro1 d. Main_Macro
15. Adhya maintains her daily expenses in a spreadsheet. She calculate the total
expanses and applies some formatting weekly. Suggest a feature of Calc using
which she can do the task quickly rather than by repeating the entire steps every
time.
a. Solver b. AutoSum c. Macro d. Consolidate
16. The recorded macros are actually stored as .
a. A set of instructions in a programming language
b. A sequence of data cells
c. A document
d. A list of values

BLANKS
1. Macros are useful to a task the same way over and over again.
2. is the process of arranging data into meaningful order.
3. library is automatically loaded when the document is opened.
4. IDE stands for .
5. Macro as a function is capable of accepting and returning a .
6. Macro allows us to add, delete a module.
7. The code of macro begins with followed by the name of the macro and
ends with .
8. By default, a macro is saved in the .

STATE WHETHER THE FOLLOWING STATEMENTS ARE TRUE OR FALSE

1. Macro is a group of instructions executing a single instruction.
2. Once created, Macro can be used any number of times.
3. By default, the Macro recording feature is turned on.
4. It is not possible to stop recording of a Macro.
5. Every Macro should be given a unique name.
6. A macro once created can be edited later.
7. Function names are not case sensitive.

Page: 16
X-IT (402) 2026-27

Answers:
MCQS:
1. d 2.b 3.a 4.c 5.a 6.c 7.a
8. a 9.c 10.d 11.b 12.d 13.c 14.b
15.c 16. a

BLANKS
1. Repeat 2. Sorting 3. Standard
4. Integrated Development Environment 5. arguments/values,
result/value
6. Organizer 7. Sub, End Sub 8. Standard Library

True or false
1. False 2. True 3. False 4. False 5. True 6. True 7.
True

Page: 17
X-IT (402) 2026-27

Ch.6: LINKING SPREADSHEET DATA

KEY POINTS
➢ Linking spreadsheet data enables you to keep the information updated without
editing multiple locations every time the data changes.
➢ When you open a Calc on your computer, it opens one worksheet by default name
is Sheet1.
➢ Insert Sheet dialog box can be invoked from the menu option Sheet > Insert Sheet.
➢ 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.
➢ Single quotes (‘ ’) are used as there is a space between Term and 1 in the sheet
name.
➢ You can create reference to other sheets (within a Spreadsheet) by using keyboard and
mouse.
➢ You can create references to other documents / workbooks by using keyboard and
mouse.
➢ Hyperlink is a coloured and underlined text or graphic that you click to open a file,
location in a file, or a web page.
➢ 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.
➢ A relative hyperlink stores the location with respect to the current location.
➢ You can create / insert hyperlinks in four types: Internet, Mail, Document and New
Document.
➢ You can edit and delete hyperlinks by clicking on Edit Hyperlink and Remove
Hyperlink options.
➢ In Calc, it is possible to link to external data, such as from an HTML document,
Calc spreadsheet or Excel spreadsheet in to the current sheet as a link.
➢ The External Data dialog box is used to create a link quickly and easily if a source
file has named ranges or tables.
➢ In Calc, you can insert the data from different databases and other data sources.
For this, first you need to register the data source with LibreOffice.
➢ Registering a data source means telling Calc what type of data source it is and
where the file is located.
Page: 18
X-IT (402) 2026-27
SUBJECTIVE TYPE QUESTIONS
1. How can we rename a worksheet?
Ans: There are three ways you can rename a worksheet, and the only difference
between them is the way in which you start the renaming process.
You can do any of the following:
a. Double-click on one of the existing worksheet names.
b. Right-click on an existing worksheet name, then choose Rename from the
resulting Context menu.
c. 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.
2. What is linking data between spreadsheets, and why is it useful?
Ans: Linking data means creating a connection where a cell in one spreadsheet
automatically updates based on the data in another spreadsheet or worksheet. It is
useful because it maintains consistency and saves time by avoiding manual updates
across multiple files.
3. Name the two ways to link the sheets in a LibreOffice Calc. Ans:
The two ways to link the sheets in a LibreOffice Calc are:
1. Creating reference to other sheets/documents by using keyboard and mouse.
2. By linking external data.
4. Differentiate between relative and absolute hyperlinks.
Ans: 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.

Page: 19
X-IT (402) 2026-27

5. Why do you link the data of spreadsheets? (Or) List any two benefits of linking
spreadsheets.
Ans: Linking spreadsheet data enables you to keep the information updated without editing
in multiple locations, every time the data changes. The ability to create links
eliminates the need of having identical data entered and updated in multiple sheets.
This saves time, reduces errors, and improves data integrity. It is a quick way to get
the data from one worksheet to another by using the ‘copy and paste’ method.
6. What are Hyperlinks in Calc? Explain four types of hyperlinks that can be applied
in spreadsheet
Ans: Hyperlinks are clickable text strings that can be used to jump to a different
location in a spreadsheet, or to other files or web pages.
Four types of hyperlinks are:
• Internet: the hyperlink points to a web address, normally starting with http://
• Mail and News: the hyperlink opens an email message that is pre-addressed to a
particular recipient.
• Document: the hyperlink points to a place in either the current worksheet or
another existing worksheet.
• New document: the hyperlink creates a new worksheet.
7. Write steps to extract a table from a web page in a spreadsheet.
Ans: Steps to extract a table from a web page in a spreadsheet are:
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.
7. Table will be inserted in the spreadsheet.
8. Write steps to register a data source that is in *.odb format.
Ans: 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.

Page: 20
X-IT (402) 2026-27

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.
9. State advantages of extracting data from a web page into a spreadsheet.
Ans: Advantages of extracting data from a web page into spreadsheet are:
1. Accuracy: Extracting data directly from a webpage, ensure that the information
is up-to-date and accurate.
2. Efficiency: Extracting data automates the process of gathering data from a
webpage.
3. Collaboration: It also facilitates organization and collaboration of data.
10. What are the two parts of a cell reference while referencing data on other sheet?
Explain with example.
Ans: The reference has two parts:
1. The sheet name enclosed within single quotes.
2. The cell reference/address.
Both the parts are separated by a period (.) or exclamatory mark(!)
Syntax: =‘SheetName’.CellAddress
for example: = ‘Savings Account’. F3.
In this example, Savings Account is the name of the sheet and it is enclosed in
single quotes whereas F3 is the cell of this sheet that is being referenced.
11. How do you insert a new sheet in a workbook?
Ans: To insert a new sheet in a workbook, you can:
• Click on the Add Sheet button to insert a new sheet.
• Choose Sheet > Insert Sheet from the menu bar.
• Right-click on the tab and select Insert Sheet.
• Click on an empty space at the end of the line of sheet tabs, and the Insert Sheet
dialog box will appear. Enter the required number of sheets and click OK.
12. How can you name a range in a spreadsheet?
Ans: To create a named range in Calc, follow these steps:
• Open a spreadsheet (source sheet) from which data is to be retrieved via a link.
• Select the range of cells that contain the data that you want to link to.
• Click on the Data menu and then Define Range option.
• The Define Database Range dialog box opens. Specify a name for the range in the
Name field and then click on OK.

Page: 21
X-IT (402) 2026-27

13. Explain the difference between linking data within the same spreadsheet and
linking data from a different spreadsheet file.
Ans: Within the same spreadsheet: Linking cells between different sheets of the same
file using formulas like =Sheet1!A1.
Between different spreadsheet files: Linking cells by referencing the external file
with its full path, e.g., ='C:\Documents\[[Link]]Sheet1'!A1.
Linking within the same file is simpler and faster, while linking external files is
useful to consolidate data from multiple sources.

COMPETENCY BASED QUESTIONS

1. Sheet1 contains monthly sales data, and Sheet2 contains yearly summary. How can
you display the sales data of January from Sheet1 into Sheet2 so that it updates
automatically when Sheet1 changes?

A. In Sheet2, enter a formula linking to Sheet1’s January sales cell using the syntax:
=Sheet1!A2 (assuming January sales is in cell A2 of Sheet1).
This way, any change in Sheet1’s A2 will reflect in Sheet2 automatically.
2. Aman wants to ensure that if the source spreadsheet moves to a different folder,
the linked spreadsheet still works without errors. What practice should he follow?

A. A man should:
• Use relative linking where possible (link files stored in the same folder).
• Avoid absolute paths or update links when moving files.
• Alternatively, use file management practices to keep linked files in fixed relative
locations.
3. What happens if the source spreadsheet file is deleted or renamed after linking?
How can you fix broken links?
A. If the source file is deleted or renamed:
• The linked spreadsheet shows an error or displays #REF!.
• To fix, restore the file with the original name or update the link manually using the
Edit Links option in the spreadsheet software.

Page: 22
X-IT (402) 2026-27
MULTIPLE CHOICE QUESTIONS
1. Hyperlink in Calc can be used .
a. to jump from one sheet to another sheet.
b. to jump from one sheet to website
c. to jump from one section to another section of same sheet
d. All of these

2. 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 .
a. Not work b. Work c. May work d. None of these
3. Hyperlink dialog box shows types of hyperlinks on left hand side.
a. 1 b. 2 c. 3 d. 4
4. Hyperlink dialog box in Calc shows option(s) on left hand side.
a. Internet b. Document c. New Document d. All of these
5. In LibreOffice Calc ‘link to external data’ option is present in menu.
a. File b. Insert c. Sheet d. View
6. Which of the following is an absolute hyperlink?
a. [Link]
b.\[Link]
c. //[Link]
d. None of these
7. The dialog box is used to create a link quickly and easily if a source file
has named ranges or tables.
a. External Data b. Internal Data c. Both a & b d. None of these
8. In the formula = SUM ('Records of Students'!B4:D4), ‘Records of Students’ is a
.
a. Sheet name b. Range c. Database d. None of these
9. A refers to a cell or range of cells on a worksheet whose data values can
be used in a formula.
a. Sheet b. Cell c. Cell reference d. Cell data
10. State whether True or False.
“We can link one worksheet to another worksheet”.
a. True b. False
11. Which of the following elements are present in “Insert Sheet” dialog box.

Page: 23
X-IT (402) 2026-27
a. After Current Sheet b. Before Current Sheet
c. No. of Sheets d. All of these
12. We can rename a new sheet in Calc .
a. After inserting a new sheet
b. While inserting a new sheet
c. Both a and b
d. None of these

13. State whether True or False.

“Hyperlink in Calc can be either relative or absolute”.
a. True b. False
14. State whether True or False.
“A relative link will stop working only if the target is moved”.
a. True b. False
15. Insert Sheet dialog can be invoked from .
a. Sheet b. Insert c. Tools d. Windows
16. refers to cell G5 of sheet named My Sheet.
a. $My Sheet.’G5’ b. $My Sheet_’G5’
c. $ ‘MySheet’.G5 d. $ ‘MySheet’_G5
17. The path of a file has forward slashes.
a. Four b. Three c. Two d. One
18. Which of the following feature is used to jump to a different spreadsheet from the
current spreadsheet in LibreOffice Calc?
a. Macro b. Hyperlink c. Connect d. Copy
19. is the keyboard shortcut to open hyperlink feature.
a. Ctrl+A b. Ctrl+H c. Ctrl+K d. Ctrl+C
20. The ‘Hyperlink’ option is available in the menu.
a. Insert b. View c. Data d. Tools
21. Which of the following is a keyboard shortcut to open the ‘Data Source View’ pane.
a. Ctrl+Shift+F4 b. Ctrl+Alt+F4 c. Shift+Alt+F4 d. Ctrl+Alt+F7
22. It opens the ‘External Data’ dialog box:
a. Tools > Link to External b. Sheet > Link to External
c. Data> Link to External d. Insert> Link to External
23. To add a new sheet in the spreadsheet, click on the sign located at the
left bottom of the spreadsheet.

Page: 24
X-IT (402) 2026-27
a. * b. / c. % d. +

BLANKS
1. At the bottom of each worksheet window is a small tab that indicates the
of the worksheets in the workbook.
2. A 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.

3. A relative hyperlink stores the location with respect to the location.

4. While inserting tables from a webpage selects the entire HTML document.
5. The extension of LibreOffice Base is .
6. are used to enclose sheet names as there might be a space within sheet
names.
7. The From file option of dialog box allows to insert sheet from another file.

STATE WHETHER THE FOLLOWING STATEMENTS ARE TRUE OR FALSE

1. A sheet can only be added before the current sheet.
2. If ‘sales’ sheet has a reference to ‘cost’ sheet then any changes made to ‘cost’
sheet will be reflected in the sales sheet as well.
3. It is not possible to link a sheet as a reference in another sheet.
4. We can insert data from a table created on a web page into a spreadsheet.
5. A hyperlink once created on a sheet cannot be deleted.

Answers:
MCQS:
1. d 2.b 3.d 4.d 5.c 6.a 7. a
8. a 9. c 10.a 11. d 12.c 13.a 14. b
15.a 16.c 17.b 18.b 19.c 20.a 21.a
22.b 23. d

BLANKS
1. Name 2. Cell reference 3. Current 4. HTML_all 5. .odb
6. Single quotes (‘ ’) 7. Insert Sheet

True or False
1. False 2. True 3. False 4. True 5. False

Page: 25
X-IT (402) 2026-27

Ch.7: SHARE AND REVIEW A SPREADSHEET

KEY POINTS
➢ Sharing spreadsheet means giving access to the other users to work on the same
spreadsheet at the same time.
➢ The Track Changes feature of Calc enables you to keep a track of the changes
done by you or the other users in a spreadsheet.
➢ In Calc, the comments are automatically added. Also, the author or reviewer can
add their own comments as well.
➢ To add a comment choose Insert > Comment (or) keyboard shortcut Ctrl+Alt+C.
➢ Once the comment is typed in the text box, a coloured dot in the upper-hand
corner of the cell where the comment is added using insert comment. This type of
comments are known as notes or suggestions in the spreadsheet.
➢ Merging spreadsheets helps in reviewing all the changes done in different sheets
in one go.
➢ Compare Document feature to compare the edited document with the original one.

SUBJECTIVE TYPE QUESTIONS

1. Define the following terms:
Ans: (a) Sharing Spreadsheet: Sharing a spreadsheet allows multiple users to work on
the same spreadsheet simultaneously. It enables collaborative editing, where
changes made by different users are merged in real-time.
(b) Record changes: Recording changes refers to the feature that tracks modifications
made to a spreadsheet. It logs details of edits, such as the user who made the change,
the type of change, and the time it was made, allowing for review and approval of
these changes.
2. Write the commands to perform:
Ans: (a) Sharing Spreadsheet:
– Open the spreadsheet.
– Go to Tools -> Share Document…
– In the dialog box that appears, check the option “Share this spreadsheet
with other users”.
– Click OK.
(b) Record changes:
– Open the spreadsheet.
– Go to Edit -> Track Changes -> Record.
Page: 26
X-IT (402) 2026-27
– Ensure the option is checked to start recording changes.
3. Which menu is used to perform the functions:
Ans: (a) Track Changes: The Edit menu is used to access the Track Changes feature.
(b) Saving Spreadsheet: The File menu is used to save the spreadsheet. You can
use the Save or Save As… options.
4. What do you understand by reviewing the changes in the spreadsheet?
Ans: Reviewing changes in a spreadsheet means checking the modifications that have been
recorded. It helps you see what changes were made, by whom and when. You can accept
or reject each change to ensure the final spreadsheet is accurate and as required.
5. Differentiate between Merging and Comparing Spreadsheet.
Ans: Merging Spreadsheet: Merging involves combining changes from multiple versions
of a spreadsheet into a single document. This is typically used when multiple users
have worked on separate copies of a shared document and their changes need to be
consolidated.
Comparing Spreadsheet: Comparing a spreadsheet involves identifying differences
between two versions of the same spreadsheet. This helps in pinpointing what
changes were made in different versions, allowing users to understand variations and
decide which changes to incorporate.
6. What are comments? What is the purpose of adding comments in a shared
spreadsheet?
Ans: Comments help in providing some extra information on the data stored in a cell.
They play an important role to add some facts, tips, or feedback for the user. Reviewers
and authors can add their comments to explain their changes.
7. How can we add comments to the changes made?
Step 1. Select from main menu bar and click on Edit > Track Changes > Comment
Step 2. This will open the Add comment window. Enter your comments.
Step 3. Now to view the entered comment,
Step 4. You can also insert comments to a cell. Click on the cell where you want to
insert comments. Then select from main menu Insert > Comment.
Step 5. This type of comments is known as notes or suggestions in the Spreadsheet.
Step 6. Once the comment is added, you can display, edit or delete it. To perform
these operations, right click on the cell where you have inserted the comments.
8. Why do you compare and merge spreadsheet?
Ans: Sometimes, you have different versions of the same spreadsheet, and you want to

Page: 27
X-IT (402) 2026-27

view all the changes and comments of all the users in one go. In such a case, the
Compare and Merge Workbook feature of Calc can be used. It is a useful tool that
allows you to compare all the changes made by the different users and merge them
into a single file. It also addresses the users when you accept or reject the changes.

COMPETENCY BASED QUESTIONS

1. Chetan has shared a spreadsheet with his friend Mithin to enter some data. Help
Mithin to open the shared spreadsheet in Calc.

A. Mithin can follow the steps given:

• To open a shared spreadsheet, locate it in the network location and double-click
to open it.
• A message appears stating that 'the spreadsheet is in the shared mode and some
• features are not available in this mode.
• Click on OK. The spreadsheet will open in the shared model

2. Adhya has received a spreadsheet, which is reviewed by her friend Drithi. Drithi
made all the corrections after turning on the Changes option. Help Adhya to Accept
or Reject the changes in the spreadsheet.

A. Adhya can follow the steps given, to accept or reject the changes,
• Click on the Edit menu and choose Track Changes > Manage.

• The Manage Changes dialog box opens containing the list of changes.

• Click on the Accept or Reject button to accept or reject a change. Or

• Click on the Accept All or Reject All button to accept or reject all changes at
once.
3. Suppose, you have sent a worksheet to your friend, and he reviewed the worksheet
without activating the track changes? Which feature of Excel can you use to easily
identify the changes?
A. Compare Document feature.

Page: 28
X-IT (402) 2026-27
4. Rahul created a budget spreadsheet and wants his teammates to review and suggest
changes without directly editing the original data. Which feature should he use and
why?
A. Rahul should use the “Track Changes” feature. This allows users to make
suggestions, and all changes are marked and recorded. The original data remains
intact, and Rahul can later accept or reject each change.
5. Preethi shared her spreadsheet with multiple users. She wants to see who made
changes and what those changes were. Which tool can help her do this?
A. Preethi should enable “Record Changes” from the Edit > Track Changes menu. She
can then use “Show Changes” to view details such as:
• Who made the change
• What was changed
6. When it was changed 6 Rishi is working on a team project and his role is to
consolidate inputs from various team members into a final spreadsheet. How can
he manage this efficiently using spreadsheet tools?
A. Rishi can:
1. Share a copy of the spreadsheet with team members.
2. Ask them to make changes with Track Changes enabled.
3. Later, use Merge Documents to integrate their inputs into the master file.
4. Review and accept/reject changes as needed.
7. Priya shared a spreadsheet via email for review. Her classmate made some changes
and sent it back. How can Priya compare her version and the edited one?

A. Priya can use the “Compare Document” or “Merge Document” feature (under
Edit > Track Changes > Merge Document) to combine both versions and see the
differences. She can then review changes and accept or reject them as needed.

MULTIPLE CHOICE QUESTIONS

1. Suman and his friends wants to work together in a spreadsheet. They can do so by
.
a. Sharing Workbook b. Linking Workbook
c. Both a & b d. None of these
2. In Calc “Share Document” dialog box can open by clicking on menu.
a. File b. Edit c. View d. Tool
3. Which of the following buttons are present on “Resolve Conflict” dialog box which

Page: 29
X-IT (402) 2026-27
appear during saving shared worksheet?
a. Keep Mine b. Keep Other c. Keep All Mine d. All of these
4. Any cells modified by the other user in shared worksheet are shown with a
border.
a. Blue b. Green c. Red d. Yellow

5. Which feature of Calc help to see the changes made in the shared worksheet?
a. Record Changes b. Solver
c. Subtotal d. None of these
6. Which of the following is not true?
a. People can work simultaneously on shared worksheet.
b. All the other users can save the shared file while you resolve the conflicts.
c. A shared spreadsheet can be modified by the other user.
d. None of these.
7. A coloured border, appears around a cell where changes were made in
shared worksheet.
a. Blue b. Yellow c. Green d. Red
8. Record Changes feature of Calc help .
a. Authors and other reviewers to know which cells were edited.
b. To record the screen.
c. To make changes permanent.
d. None of these
9. Which of the following changes are not recorded in shared worksheet?
a. Changes any number b. Changes any text
c. Cell Formatting d. None of these
10. When sharing worksheets authors may forget to record the changes they make.
Calc can find the changes by worksheets.
a. Duplicating b. Comparing c. Checking d. None of these
11. Krish and Kritika have done a survey of age wise literacy rates of their locality as a
school project, which they have created in a Spreadsheet. They both want to work
simultaneously to complete it on time. Which option they should use to access the
same Spreadsheet to speed up their work.
a. Consolidate Worksheet b. Shared Worksheet
c. Link Worksheet d. Lock Worksheet
12. You can use feature to compare the edited document with the original one.
Page: 30
X-IT (402) 2026-27
a. Merged Document b. Compare Document
c. Manage Document d. Conflict Document
13. Sharing allows to edit the spreadsheet by .
a. Single user b. Different users simultaneously
c. One by one users d. One after other users

14. State whether True/ False.

“After adding comment to a changed cell of shared worksheet, we can see it by
hovering the mouse pointer over the cell”.
a. True b. False
15. A coloured border, with , appears around a cell where changes are made
in a shared worksheet.
a. A dot in the upper left-hand corner
b. A dot in the lower left-hand corner
c. A cross in the upper left-hand corner
d. A cross in the upper right-hand corner
16. State whether True or False.
“Original author of the Worksheet can accept or reject changes made by other
users”.
(Or)
“Anil is the author of shared worksheet so he has the right to accept or reject
changes made by the reviewers”.
a. True b. False
17. Sharing spreadsheet feature allows to save the changes in .
a. Multiple sheets b. User’s sheet
c. In a same sheet d. In different sheet
18. The Recording Changes feature of LibreOffice Calc provides different ways to
record the changes made by in the spreadsheet.
a. One user b. Other user c. The user d. One or other users
19. In Calc, the comments are added .
a. Automatically b. By author c. By reviewer d. All of these

20. The changes by team members in the spreadsheet can be accepted or rejected
by .
a. The team members b. Any of the user

Page: 31
X-IT (402) 2026-27
c. Owner d. Other users
21. is the keyboard shortcut key to add a comment.
a. Ctrl+ Alt + C b. Ctrl+ Shift + C c. Ctrl+ Alt + D d. Ctrl+ Shift + D
22. Which of the dialog box allows you to accept or reject changes in spreadsheet?
a. Manage Changes b. Track Changes
c. Record Changes d. Manage Records

23. Which of the following menu contains the ‘Share Spreadsheet’ option?
a. Tools b. Edit c. File d. Insert
24. Abhilasha is a computer teacher. She has entered the marks of 80 students, subject
wise, in a spreadsheet and sent the file to Kritika (another teacher of the school) to
verify the information. Which feature should be activated by Kritika before
reviewing the data in the spreadsheet so that any changes made by her can easily
be identified by Abhilasha?
a. Record Changes b. Track Changes
c. Share document d. Resolve conflicts
25. In Calc, shared workbooks allow .
a. Merging cells b. Conditional formatting
c. Inserting pictures/graphs d. Adding text
26. Kawal and his friends are working on a Spreadsheet for entering data and updating
records. They wish to keep a track of changes. Which of the following options will
help in knowing who made the changes and what changes were done in the
spreadsheet?
a. View changes b. Record changes
c. Store changes d. Track changes
27. Which of the following is correct choice to record changes in a spreadsheet?
a. Changes>Track Change b. Track Changes > Record
c. Track Record > Changes d. None of these
28. To add your own comments in Calc, select > Track changes > Comment.
a. File b. Edit c. Insert d. Data

BLANKS
1. Spreadsheet software allows the user to share the workbook and place it in the
location where several users can access.
2. Spreadsheet software can find the changes by Sheets.

Page: 32
X-IT (402) 2026-27
3. The title bar of the document shows along with the filename for the
shared mode of the spreadsheet.
4. The shared mode spreadsheet allows users to access and edit the
spreadsheet at the same time.
5. Recording changes automatically the shared mode of a spreadsheet.

6. Click on Edit menu, Track Changes and then select to record the
changes in the spreadsheet.
7. The border color of the changed cell will be .
8. is used to add notes or suggestions to a cell in a spreadsheet.
9. The comment box can be formatted just like formatting the .

STATE WHETHER THE FOLLOWING STATEMENTS ARE TRUE OR FALSE

1. Spreadsheet cannot be shared to work with more than one user.
2. Some of the features become unavailable when the spreadsheet is in shared
mode.
3. You can record changes in the spreadsheet when the spreadsheet is opened in
shared mode.
4. File menu is used to Record changes for the spreadsheet.
5. You can add a note or suggestion in the spreadsheet using Insert Comment.
6. Formatting comment can be used to change the font colour of the comment.
7. No other user will be able to save the shared file while you resolve the conflicts.

Answers:
MCQS:
1. a 2.d 3.d 4.c 5.a 6. b 7.d
8. a 9.c 10.b 11.b 12.b 13.b 14. a
15.a 16.a 17.a 18.d 19.d 20.c 21. a
22.a 23.a 24.b 25.d 26.b 27.b 28. c
BLANKS

1. Network 2. Comparing 3. Shared 4. Many 5. Turn off

6. Record 7. Red 8. Comment 9. Cell contents
True or False
1. False 2. True 3. False 4. False 5. False 6. True 7. True

Page: 33
X-IT (402) 2026-27

NCERT Text Book Chapter End Solutions for Answer the following
questions
Part B: Unit 2 – Electronic Spreadsheet Scenario Analysis
Answer the following questions
1. Define the terms
(a) Consolidate Function
(b) What – if analysis
(c) Goal Seek
A. (a) Consolidate function: Consolidate means that to combine a number of things into a single unit.
Consolidating of data means that the process of combining the number of data organised into different
sheets into one worksheet or cell.
(b) What-if analysis: What-if analysis is a tool that shows how changing one or more values will affect
the outcome of set formulas. It helps to finds what business operations or targets would look like
through within a given a range of various inputs.
(c) Goal seek: Goal seek is one of the powerful features of LibreOffice Calc. It is a feature that reverses
the usual order for a formula that is we run a formula to get the result with certain arguments
whereas with Goal Seek we work with the output to see what values were used to get the result.
2. Give one point of difference between
(a) Subtotal and What – if
(b) What – if Scenario and What – if tool
A. (a) Subtotal and What-if: Subtotals are used to summarize data within a dataset, while What-If
scenarios involve creating hypothetical situations for analysis.
(b) What-if scenario and What-if tool: What-If Analysis is the process of changing the values in cells
to see how those changes will affect the outcome of formulas on the worksheet. Three kinds of What-If
Analysis tools come with Excel: Scenarios, Goal Seek, and Data Tables. Scenarios and Data tables take
sets of input values and determine possible results.
3. Give any two advantages of data analysis tools.
A. Advantages of data analysis tools are:
1. Informed Decision-Making: Data analysis provides insights that help organizations make evidence-
based decisions, reducing reliance on intuition or guesswork.
2. Identifying Trends and Patterns: By analysing data, businesses can uncover trends and patterns that
inform strategic planning and forecasting.
3. Improved Efficiency: Data analysis can reveal inefficiencies in processes, allowing organizations to
optimize operations and reduce costs.
4. Name any two tools for data analysis.
A. Consolidating Data, Subtotal, What – if Scenario, Goal Seek, Solver and Multiple Operations.
5. What is the criteria for consolidating sheets?
A. Each column must have a label (header) in the first row and contain similar data. There must
be no blank rows or columns anywhere in the list. Put each range on a separate worksheet,
but don't enter anything in the master worksheet where you plan to consolidate the data.
7. Which tool is used to create an outline for the selected data?
A. You can create an outline of your data and group rows and columns together so that you can
collapse and expand the groups with a single click.

Page: 34
 X-IT (402) 2026-27
Auto Outline: If the selected cell range contains formulas or references, LibreOffice
automatically outlines the selection.

Show Details: Shows the details of the grouped row or column that contains the cursor. To
show the details of all of the grouped rows or columns, select the outlined table, and then
choose this command.

Using Macros in Spreadsheet

Answer the following questions
1. What is a Macro? List any two real life situations where they can be used.
A. A Macro refers to a sequence of user actions or commands that are recorded and can be played back
later to automate repetitive tasks.

Macros can be used to apply consistent formatting to cells or ranges, such as setting fonts,
colors, borders, and alignment.
• Macros can assist in sorting, filtering, and analyzing data. For example, you can create a
macro to automatically sort a table based on certain criteria.
• Macros can be employed to automate the creation and customization of charts and graphs
based on the data in your spreadsheet.
• You can use macros to automate the process of entering data into specific cells or ranges.
This is particularly useful for repetitive data entry tasks.
• You can create a macro that automatically formats a range of cells, inserts a chart, or
prints a spreadsheet.
• You can create macros that use LibreOffice Calc's built-in functions to perform complex
calculations, such as calculating the standard deviation of a data set or finding the maximum
value in a range of cells.
• You can use macros to generate reports by extracting data from your spreadsheet
and arranging it in a specific format.
2. List the actions that are not recorded by a Macro.
A. Spreadsheet provides a feature called macro in which user can record the commands, tasks or the
activities that needs to be performed regularly in specified order.
3. How is LibreOffice Macros Library different from my Macros?
A. LibreOffice Macros library is provided by library office and contains modules with pre-recorded macros
and should not be changed whereas My Macros contain macros that we write or add to LibreOffice.

Page: 35
 X-IT (402) 2026-27
4. Differentiate between predefined function in Calc and Macros as a function.
A. Differences between predefined function and Macros as a function:

Predefined Functions: Macros as a Function

1. Built-in formulas: Already available in the 1. User-defined scripts: Record or write a
program, like SUM, AVERAGE, COUNT, etc. series of commands to automate tasks.

2. Customized functionality: Perform complex

2. Perform specific calculations: Automatically
operations, interact with user inputs, and adapt to
execute a particular calculation or operation.
specific needs.

3. No user input required: Simply enter the 3. User input required: Create and edit macros
function name and required arguments. to suit specific requirements.

4. Fixed functionality: Cannot be modified or 4. Flexible functionality: Can be modified,

customized. edited, or deleted as needed.

Key differences:

Predefined functions perform specific calculations, while macros automate tasks and offer
customized functionality.

5. List the rules that should be kept in mind while naming a macro.
A. 1. Use a letter as the first character. (Names aren't case sensitive, but they preserve capitalization.)
2. Use only alphanumeric characters and the underscore character ( _ ). Spaces and other symbols are
not allowed.
3. Use fewer than 255 characters.
4. Avoid names that match Visual Basic or Reflection commands. Or, if you do use a macro name that is
the same as a command, fully qualify the command when you want to use it.
5. Give unique names to macros within a single module. Visual Basic doesn't allow you to have two
macros with the same name in the same code module.
6. Give any one advantage of macros.
A. Advantages of macros are:
1. It saves user's time.
2. Helps in easy calculations for complex problems.
3. Reduces error occurring with repetitive tasks.
4. User can use their names in each macro.

Linking SpreadsheetData
Page: 36
X-IT (402) 2026-27
Answer the following questions
1. Name the two ways to link the sheets in a LibreOffice Calc.
A. Hyperlinks can be stored within your file as either relative or absolute.
a) An absolute link will stop working only if the target is moved. It contains the complete URL.
b) A relative link will stop working only if the start and target locations change relative to each other. For
instance, if you have two spreadsheet in the same folder linked to each other and you move the entire
folder to a new location, a relative hyperlink will not break.
2. Differentiate between Relative and Absolute Hyperlink.
A. Differences between Relative and Absolute Hyperlink are:

Relative Hyperlink Absolute Hyperlink

1) Absolute hyperlink always include the domain

1) Relative links only point to a file or a file path
name of the website.

2) Relative hyperlinks will stop working only if

2) Absolute hyperlinks will stop working only if
the source and target locations change relative
the target is moved.
to each other.

3. Write steps to extract a table from a web page in a spreadsheet.

A. Steps to extract a table from a webpage in a spreadsheet are as follows:
Step 1: Open the worksheet where the external data has to be linked. Step
2: Now select the required cell where the data has to be inserted. Step 3:
Click Sheet
→ External Link option.
Step 4: External Data dialog box is displayed.
Step 5: Enter the URL of the webpage and press Enter. Import options dialog box is displayed.
Step 6: Select Automatic option from the Import Options dialog box and Click OK.
Step 7: When OK is clicked on the Import Options window, all the existing HTML tables
will be displayed in the External Data Window.

Step 8: Here, select any of the HTML file or if you want to select all the displayed files, you
can perform by using CTRL key.
Step 9: Check the Update every check box, and change the seconds accordingly. When the
specified time is given automatically if any change appears in the website it will also be
updated in the sheet.
Step 10: Now, the linked sheet can be viewed in the document.

4. Write the steps to register a data source that is in .odb format.

Page: 37
X-IT (402) 2026-27
A. Steps to register a data source in .odb format:
Step 1: Click Tools → Options → from the Left Pane, Select LibreOffice Base → Database
Step 2: Click the New button, Create Database Link dialog box appears. Repeat this process
for all other objects that you want to add in the same group.

Step 3: Enter the location of the database file or click Browse to open a file browser and select the
database file.

Step 4: Type a name to use as the registered name for the database and click OK.
Here the database is added to the list of registered databases
5. State advantages of extracting data from a web page into spreadsheet.

A. 1. Save Cost. Web Scraping saves cost and time as it reduces the time involved in the data extraction task.

2. Accuracy of Results. Web Scraping beats human data collection hands down.
3. Time to Market Advantage. Accurate results help businesses save time, money, and human labor.

Share and Review Spreadsheet

Answer the following questions
1. Define the terms
(a) Sharing Spreadsheet
(b) Record Changes
A. (a) Sharing Spreadsheet: Sharing spreadsheet allows many users to open the same
worksheet / workbook for entering and editing the data at the same time. This feature
enables to share the spreadsheet file with several users and edit the same workbook without
keeping track of multiple versions.
(c) Record changes: This feature of LibreOffice Calc provides different ways to record the changes made
by one or other users in the spreadsheet. While recording the changes, the spreadsheet will turn off
its shared feature.
2. Write the commands to perform
(a) Sharing Spreadsheet
(b) Record Changes
A. (a) Sharing Spreadsheet: Tools → Share
Spreadsheet Steps to Share a Spreadsheet:

Step 1: Create a spreadsheet in LibreOffice application.

If a document has to be shared with multiple users for viewing, editing and to review, the
following steps has to be followed.
Page: 38
X-IT (402) 2026-27
Step 2: Click Tools and select Share Spreadsheet option.
Step 3: Share Document window is displayed where the user has an option to share or do
not share the document.
Step 4: Once the document is selected it will prompt the user to save the document to activate
the shared mode. Click Yes to continue.

Step 5: After it is saved, the document name (Shared) will be displayed on the title bar. It
means that the document is in shared mode.

(b) Record Changes: Edit → Track Changes -→ Record

Changes Steps to Record a Spreadsheet:

Step 1: Open the Spreadsheet where the recording changes have to done. Now deselect the
document from the sharing mode. To deselect the document from sharing mode Select Tools
→ Share Spreadsheet. Uncheck the box at the top of the window.
Step 2: Select Edit option, goto Track Changes option and select Record option
3. Which menu is used to perform the functions
(a) Track Changes
(b) Saving Changes
A. (a) Track Changes: Edit → Track Changes
(c) Saving Changes: File → Save
4. What do you understand by reviewing the changes in the spreadsheet?
A. Once the spreadsheet is edited by all the members of the team. It is the final stage
before submitting the spreadsheet. In this stage, we will go through the changes
to accept or reject to prepare the final spreadsheet after looking at all the changes
made by the team members.
5. Differentiate between Merging and Comparing Spreadsheet?
A. In LibreOffice Calc, the main difference between merging and comparing
spreadsheets is that merging combines multiple edited versions of a spreadsheet
into one, while comparing shows the differences between two similar
spreadsheets:

Merging Comparing

Page: 39
X-IT (402) 2026-27
Combines multiple edited versions of a
spreadsheet into one. You can use this feature Shows the differences between two similar
when multiple reviewers have edited a spreadsheets. You can use this feature to find
spreadsheet and you want to review all the potential problems, like broken formulas or
changes at once. To merge documents, you can: manually-entered totals. You can compare two
1. Open the original document documents that don't have revisions marked with
2. Select Edit > Track Changes > Merge Document Track Changes.
Select the files you want to merge and click Open

Page: 40