Unit-2: Electronic Spreadsheet (Advanced) using LibreOffice Calc

Chapter-4: Analyse Data using Scenarios and Goal Seek

A. Multiple Choice Questions

1. Which of the following features is not used for data analysis in a spreadsheet?

(e) Page layout

2. Which of the following office tools is known for data analysis?

(b) Calc

3. Which of the following operations cannot be performed using LibreOffice Calc?

(d) Mail merge

4. What is the extension of a spreadsheet file in Calc?

(d) .ods

5. The default function while using Consolidate is ____________.

(b) Sum

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

(d) Subtotal tool

7. Which tool is used to predict the output while changing the input?

(c) Goal Seek

8. Which of the following is an example of absolute cell referencing?

(b) $C$5

9. _____________ analysis tool works in reverse order, finding input based on the output.

(b) Goal Seek

Page 1 of 18
www.lumenseducation.net
B. True or False

1. Consolidate function is used to combine information from two or more sheets into one.

True

2. Consolidate function cannot be used to view and compare data.

False

3. Link to source data is checked to update the target sheet if any changes are made in the source

data.

True

4. Using Subtotal in Calc needs to use filtered data for sorting.

True

5. Subtotal tool can use only one type of summary function for all columns.

False

6. Only one scenario can be created for one sheet.

False

7. What-if analysis tool uses one array of cells.

True

8. Goal Seek analysis tool is used while calculating the output depending on the input.

True

9. The output of the What-if tool is displayed in the same cell.

True

Page 2 of 18
www.lumenseducation.net
C. Fill in the Blanks

1. Consolidate function is used to combine information from multiple sheets to analyze the information.

2. Data can be viewed and compared in a single sheet for identifying trends and relationships using the

Consolidate function.

3. Consolidate function under the Data menu can be used to combine information from multiple sheets into

one sheet to compare data.

4. The Subtotal tool in Calc creates the group automatically and applies functions on the grouped data.

5. What-if scenario is used to explore and compare various alternatives depending on changing conditions.

6. What-if analysis is a planning tool for what-if questions.

7. What-if analysis tool uses two arrays of cells: one array contains input values, and the second uses the

results.

8. Goal Seek helps in finding out the input for the specific output.

D. Answer the Following Questions

1. Define the terms:

o Consolidate function: A feature that combines data from multiple sheets into a single sheet for

analysis and comparison.

o What-if analysis: A tool to explore different scenarios by changing input values to see their impact

on results.

o Goal Seek: A tool used to find the required input value to achieve a specific output.

2. Difference between:

o Subtotal and What-if:

Subtotal is used to summarize data in a grouped manner, while What-if analysis explores possible

outcomes by changing input data.

Page 3 of 18
www.lumenseducation.net
 o What-if scenario and What-if tool:

What-if scenarios allow creating and comparing multiple sets of values, while the What-if tool

includes features like Goal Seek for predicting outcomes.

3. Advantages of data analysis tools:

o Help in identifying trends and patterns.

o Facilitate quick decision-making with precise calculations.

4. Two tools for data analysis:

o Consolidate function

o Goal Seek

5. Criteria for consolidating sheets:

o The data ranges in the sheets must be aligned.

o Ensure similar labels for rows or columns in all sheets.

6. Tool to create an outline for selected data:

o Group and Outline under the Data menu.

Page 4 of 18
www.lumenseducation.net
 Chapter-5: Using Macros in Spreadsheet

A. Multiple Choice Questions

1. Macro Recordings can be enabled from the ________ option in the menu bar.

(c) Tools

2. Which of the following is an invalid Macro Name?

(c) format*word

3. Which of the following Libraries contains modules with prerecorded macros and should not be

changed?

(b) LibreOfficeMacros

4. Identify which of the following is a programming language?

(b) BASIC

5. The Module can be executed from the IDE by pressing _________________.

(c) F5

6. Which of the following is the default name of the Macro _______________.

(b) Main

B. Fill in the Blanks

1. My Macros library is automatically loaded when the document is opened.

2. IDE stands for Integrated Development Environment.

3. Macro as a function is capable of accepting parameters and returning a value.

4. Macro Organizer allows us to add, delete a module.

5. The code of a macro begins with Sub followed by the name of the macro and ends with End Sub.

6. By default, a macro is saved in the document.

Page 5 of 18
www.lumenseducation.net
C. True or False

1. Macro is a group of instructions executing a single instruction.

True

2. Once created, Macro can be used any number of times.

True

3. By default, the Macro recording feature is turned on.

False

4. It is not possible to stop recording of a Macro.

False

5. Every Macro should be given a unique name.

True

6. A macro once created can be edited later.

True

D. Answer the Following Questions

1. What is a Macro?

A Macro is a set of instructions that can be recorded and executed to automate repetitive tasks in a

software application.

Real-life situations:

o Data Entry.

o Document Formatting.

2. List the actions that are not recorded by a Macro.

o Actions like dragging and dropping cells.

o Modifications made to settings in dialog boxes.

Page 6 of 18
www.lumenseducation.net
3. How is LibreOffice Macros Library different from My Macros?

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

5. Rules for naming a Macro:

o Must start with a letter.

o Cannot contain spaces or special characters (e.g., *).

o Should be unique within the same library.

6. Advantage of Macros:

o Reduces repetitive tasks, saving time and effort.

Page 7 of 18
www.lumenseducation.net
 Chapter-6: Linking Spreadsheet Data

A. Multiple Choice Questions with Options

1. Insert Sheet dialog can be invoked from _______________.

(a) Sheet

(b) Insert

(c) Tools

(d) Windows

Answer: (a) Sheet

2. ______________ refers to cell G5 of the sheet named My Sheet.

(a) $My Sheet.‟G5‟

(b) $My Sheet_‟G5‟

(c) $„MySheet‟_G5

(d) $„MySheet‟.G5

Answer: (d) $„MySheet‟.G5

3. The path of a file has ____________ forward slashes.

(a) Four

(b) Three

(c) Two

(d) One

Answer: (b) Three

4. Which of the following features is used to jump to a different spreadsheet from the current

spreadsheet in LibreOffice Calc?

(a) Macro

(b) Hyperlink

Page 8 of 18
www.lumenseducation.net
 (c) Connect

(d) Copy

Answer: (b) Hyperlink

5. A relative hyperlink stores the location with respect to the ______________ location.

(a) Target file

(b) Source file

(c) Current file

(d) System root

Answer: (c) Current file

6. While inserting tables from a webpage, ______________ selects the entire HTML document.

(a) Select All

(b) Select Table

(c) Select Rows

(d) Select Page

Answer: (a) Select All

7. The extension of LibreOffice Base is ______________.

(a) .ods

(b) .odt

(c) .odb

(d) .odg

Answer: (c) .odb

8. ______________ are used to enclose sheet names as there might be a space within sheet names.

(a) Double quotes (“ ”)

(b) Parentheses (() )

(c) Single quotes („ ‟)

Page 9 of 18
www.lumenseducation.net
 (d) Square brackets ([ ])

Answer: (c) Single quotes („ ‟)

9. The From File option of ______________ Dialog box allows inserting a sheet from another file.

(a) Tools

(b) Insert Sheet

(c) Hyperlink

(d) Data

Answer: (b) Insert Sheet

C. True or False

1. A sheet can only be added before the current sheet.

False

2. If the „sales‟ sheet has a reference to the „cost‟ sheet, then any changes made to the „cost‟ sheet will

be reflected in the sales sheet as well.

True

3. It is not possible to link a sheet as a reference in another sheet.

False

4. We can insert data from a table created on a web page into a spreadsheet.

True

5. A hyperlink once created on a sheet cannot be deleted.

False

Page 10 of 18
www.lumenseducation.net
D. Answer the Following Questions

1. Name the two ways to link the sheets in LibreOffice Calc.

o Using cell references (e.g., =Sheet2.A1).

o Using hyperlinks to navigate between sheets.

2. Differentiate between Relative and Absolute Hyperlink.

o Relative Hyperlink: Stores the link relative to the current file's location.

o Absolute Hyperlink: Stores the full path of the linked file or webpage, independent of the current

file's location.

3. Steps to extract a table from a web page into a spreadsheet:

o Open the web browser and navigate to the webpage containing the table.

o Copy the table data.

o Open LibreOffice Calc, go to the desired sheet, and paste the data.

o Use the Text to Columns tool if necessary to organize the data.

4. Steps to register a data source that is in .odb format:

o Open LibreOffice and go to Tools > Options > LibreOffice Base > Databases.

o Click New and select the From File option.

o Browse to the location of the *.odb file and select it.

o Provide a name for the data source and click OK.

5. Advantages of extracting data from a web page into a spreadsheet:

o Automates data entry by directly importing the table, saving time.

o Allows further analysis and visualization of web data within the spreadsheet.

o Ensures consistent and accurate transfer of data.

Page 11 of 18
www.lumenseducation.net
 Chapter-7: Share and Review a Spreadsheet

A. Multiple Choice Questions

1. Sharing allows editing the spreadsheet by:

(a) Single user

(b) Different users simultaneously

(c) One by one users

(d) One after other users

Answer: (b) Different users simultaneously

2. The Sharing Spreadsheet feature allows saving changes in:

(a) Multiple sheets

(b) User‟s sheet

(c) In the same sheet

(d) In a different sheet

Answer: (c) In the same sheet

3. The Recording Changes feature of LibreOffice Calc provides different ways to record changes

made by __________ in the spreadsheet.

(a) One user

(b) Other user

(c) The user

(d) One or other users

Answer: (d) One or other users

4. In Calc, comments are added:

(a) Automatically

(b) By author

Page 12 of 18
www.lumenseducation.net
 (c) By reviewer

(d) All of the above

Answer: (d) All of the above

5. The changes by team members in the spreadsheet can be accepted or rejected by:

(a) The team members

(b) Any of the users

(c) Owner

(d) Other users

Answer: (c) Owner

B. State Whether the Following Statements Are True or False

1. Spreadsheet cannot be shared to work with more than one user.

False

2. Some features become unavailable when the spreadsheet is in shared mode.

True

3. You can record changes in the spreadsheet when the spreadsheet is opened in shared mode.

True

4. File menu is used to Record changes for the spreadsheet.

False (The Edit menu is used.)

5. You can add a note or suggestion in the spreadsheet using Insert Comment.

True

6. Formatting comments can be used to change the font color of the comment.

True

Page 13 of 18
www.lumenseducation.net
C. Fill in the Blanks

1. The title bar of the document shows [Shared] along with the filename for the shared mode of the

spreadsheet.

2. The shared mode spreadsheet allows multiple users to access and edit the spreadsheet at the same time.

3. Recording changes automatically enables the shared mode of a spreadsheet.

4. Click on the Edit menu, Track Changes, and then select Record to record the changes in the spreadsheet.

5. The border color of the changed cell will be red.

6. Insert Comment is used to add notes or suggestions to a cell in a spreadsheet.

7. The comment box can be formatted just like formatting the cell contents.

D. Answer the Following Questions

1. Define the terms:

(a) Sharing Spreadsheet: A feature in LibreOffice Calc that allows multiple users to work on a

spreadsheet simultaneously, ensuring collaboration and efficiency.

(b) Record Changes: A feature in LibreOffice Calc that tracks and highlights modifications made by

different users in a spreadsheet, enabling easy review and management of changes.

2. Write the commands to perform:

(a) Sharing Spreadsheet:

o Go to Tools > Share Spreadsheet.

o Check the box Share This Spreadsheet With Other Users.

(b) Record Changes:

o Go to Edit > Track Changes > Record.

Page 14 of 18
www.lumenseducation.net
 3. Which menu is used to perform the functions:

(a) Track Changes: Edit menu

(b) Saving Spreadsheet: File menu

4. What do you understand by reviewing the changes in the spreadsheet?

Reviewing changes involves analyzing modifications made by users, which are tracked and

displayed with highlights or comments. The spreadsheet owner can accept or reject these changes to

finalize the content.

5. Differentiate between Merging and Comparing Spreadsheet:

Feature Merging Spreadsheet Comparing Spreadsheet

Definition Combines data from multiple shared Highlights differences between two

spreadsheets. spreadsheets.

Purpose Consolidating changes from team members. Identifying discrepancies or changes.

Action Changes are integrated into one sheet. Changes are marked for review.

Previous Years Questions:

Multiple-Choice Questions

1. What is the function of the Subtotal tool in Calc?

o (a) Grouping data and applying functions like sum or average

o (b) Sorting data

o (c) Adding new sheets

o (d) Formatting cells

Answer: (a) Grouping data and applying functions like sum or average
Page 15 of 18
www.lumenseducation.net
2. What is the purpose of the Goal Seek tool in Calc?

o (a) To create multiple scenarios

o (b) To calculate unknown input for a desired output

o (c) To sort and filter data

o (d) To add comments

Answer: (b) To calculate unknown input for a desired output

3. Which feature in Calc helps combine data from multiple sheets into one?

o (a) Solver

o (b) Consolidate

o (c) Scenarios

o (d) Track Changes

Answer: (b) Consolidate

4. What is a What-If scenario used for in Calc?

o (a) Testing hypothetical situations

o (b) Formatting cells

o (c) Protecting worksheets

o (d) Printing scenarios

Answer: (a) Testing hypothetical situations

5. Sharing allows editing of a spreadsheet by:

o (a) Single user

o (b) Different users simultaneously

o (c) One by one users

o (d) One after other users

Answer: (b) Different users simultaneously

6. Which menu is used to access the Subtotal tool?

o (a) Insert > Subtotal

o (c) View > Subtotal

o (d) Edit > Subtotal

Answer: (b) Data > Subtotal

7. In LibreOffice Calc, scenarios are used to:

o (a) Automatically check for errors

o (b) Explore alternatives based on input changes

o (c) Create new sheets

o (d) None of the above

Answer: (b) Explore alternatives based on input changes

8. The default name for the first scenario in Calc is:

o (a) Sheet1_Scenario1

o (b) Sheet1_Scenario_1

o (c) Sheet_1_Scenario1

o (d) Sheet_1_Scenario_1

Answer: (b) Sheet1_Scenario_1

Fill in the Blanks

1. The Subtotal tool is used to _______ data and apply functions like sum or average.

Answer: Group

2. What-If tools are used to explore and compare various _______ based on changing conditions.

Answer: Alternatives

3. The _______ function combines data from multiple sheets to summarize it.

Answer: Consolidate

Page 17 of 18
www.lumenseducation.net
 4. Goal Seek helps to calculate the _______ input for a specific output.

Answer: Unknown

Short Answers

1. Differentiate between Goal Seek and Solver.

 Goal Seek: Used to find a single input value that meets a specific target output for a formula.

 Solver: Used for complex optimization problems involving multiple constraints and variables.

2. Differentiate between a Relative and Absolute Hyperlink.

a. Relative hyperlinks depend on the relative location of files; moving the folder preserves the link.

b. Absolute hyperlinks work as long as the target remains in the same location, irrespective of the source.

3. Explain the Consolidate function.

a. The Consolidate function merges data from multiple sheets into one, allowing easy comparison and trend

analysis.

4. Describe the Scenario tool.

a. Scenarios allow testing of "what-if" questions by saving and comparing multiple sets of input values,

useful for decision-making.

5. How to enable the Subtotal tool in Calc?

 Navigate to Data > Subtotal, and choose the grouping criteria for creating subtotals.

6. Steps to use Scenarios in LibreOffice Calc:

 Select the range of cells.

 Go to Tools > Scenarios.

 Name the scenario, add a description, and set any constraints.

Page 18 of 18
www.lumenseducation.net