Unit 2: Electronic Spreadsheet (Advanced)
Chapter 4: Analyse Data using Scenarios and Goal Seek
This chapter focuses on tools used to manipulate data, interpret results, and make decisions
based on calculations.
1. Consolidating Data
Definition: Consolidation is a function used to combine information from multiple sheets or
ranges into one place to summarize the information.
● Purpose: To view and compare a variety of data in a single spreadsheet (e.g.,
combining monthly sales from different branches into a yearly report).
● Menu Option: Data > Consolidate.
● Functions Available: Sum (Default), Average, Max, Min, Count, StDev, Var, etc.
● Key Settings:
○ Source Data Range: The ranges from other sheets to be combined.
○ Copy Results to: The destination cell for the consolidated data.
○ Consolidate by: You can link data by "Row labels" or "Column labels".
○ Link to Source Data: If checked, changes in the source sheets automatically
update the consolidated sheet.
2. Subtotals
Definition: The Subtotal tool creates groups automatically and applies common functions (like
Sum, Average) to the grouped data.
● Prerequisite: The data must be sorted/grouped by the column you want to subtotal.
● Menu Option: Data > Subtotals.
● Features:
○ Group By: Select the column to group data by (e.g., "Class" or "Category").
○ Calculate Subtotals For: Select the columns containing numbers to calculate.
○ Use Function: Select the function (Sum, Count, Average).
○ Groups: You can have 1st Group, 2nd Group, and 3rd Group for nested
subtotals.
● Outline: Subtotals create an outline on the left side with + and - signs to expand or
collapse details.
3. What-if Scenarios
Definition: A Scenario is a set of saved values that can be used within calculations. It allows
you to swap different sets of data (variables) to view different outcomes.
● Use Case: Estimating loan payments with different interest rates or principal amounts.
● Menu Option: Tools > Scenarios.
● Properties:
○ Name: Give a unique name to the scenario.
○ Display Border: Highlights the scenario range with a specific color.
○ Copy Back: Prevents changes made to the active scenario from saving back to
the original if unchecked.
● You can switch between scenarios using the Navigator or the drop-down list created on
the sheet.
4. Goal Seek
Definition: A tool used for backward calculation. It determines the input value required to
achieve a specific target output.
● How it works: Usually, we calculate Output = Function(Input). Goal Seek does Input = ?
for a fixed Output.
● Menu Option: Tools > Goal Seek.
● Components:
1. Formula Cell: The cell containing the formula (the result you want to change).
2. Target Value: The numeric result you want to achieve.
3. Variable Cell: The cell that needs to be modified to reach the target.
● Limitation: It can only vary one input variable at a time.
5. Solver (Advanced Goal Seek)
● Note: While Goal Seek deals with one variable, Solver deals with equations with
multiple unknown variables and constraints. (Mentioned in some curricula as "What-if
Analysis tool" via Data > Multiple Operations).
Chapter 5: Using Macros in Spreadsheet
1. Introduction to Macros
Definition: A Macro is a saved sequence of commands or keystrokes that are stored for later
use. It is used to automate repetitive tasks.
● Language: LibreOffice macros are typically written in BASIC language.
2. Recording a Macro
Before recording, you must ensure macro recording is enabled: Tools > Options > LibreOffice >
Advanced > Enable Macro Recording.
● Steps to Record:
○ Go to Tools > Macros > Record Macro.
○ Perform the actions (formatting, typing, formulas) you want to record.
○ Click "Stop Recording".
○ Save the macro in a Module/Library.
● Limitations: Macro recorder does not record:
○ Opening of windows.
○ Window switching.
○ Actions not related to spreadsheet content (e.g., changing Options).
○ Mouse selections (only keyboard selections are recorded reliably).
3. Saving and Organizing Macros
● Structure: Library $\rightarrow$ Module $\rightarrow$ Macro.
○ Standard Library: Available effectively everywhere.
○ My Macros: Global macros available to all documents.
○ Document Macros: Available only in the specific file.
● Naming Rules: Macro names cannot contain spaces, must begin with a letter, and
cannot use special characters (except underscore _).
4. Running a Macro
● Menu Option: Tools > Macros > Run Macro.
● Select the library, module, and specific macro name, then click Run.
5. Macro as a Function
● You can write custom functions in the IDE (Integrated Development Environment).
● Menu Option: Tools > Macros > Organize Macros > LibreOffice Basic.
Structure:
Basic
Function FunctionName(Arguments)
' Code calculation
FunctionName = Result
End Function
●
● These custom functions can be used in spreadsheet cells just like =SUM() or
=AVERAGE().
Chapter 6: Linking Spreadsheet Data
This chapter deals with connecting data across different sheets and different files.
1. Multiple Sheets
● Insert Sheet: Insert > Sheet or click the + icon at the bottom.
● Rename Sheet: Right-click sheet tab $\rightarrow$ Rename.
● Tab Color: Right-click sheet tab $\rightarrow$ Tab Color.
2. Referencing Cells (Linking)
To use data from one place in another place.
A. Referencing Other Sheets (Same File):
● Format: $'SheetName'.CellAddress
● Example: If you want data from cell C4 of "Term1" sheet: =$'Term1'.C4
● Note: Single quotes ' are mandatory if the sheet name contains spaces.
B. Referencing External Data (Different File):
● Format: '[Link]
● Method: Sheet > Insert Sheet > From File. You can check the "Link" box to keep live
data. If the source updates, the destination updates upon reloading (Tools > Options >
Calc > General > Updating).
3. Hyperlinks
Definition: A link that takes you to another location (cell, sheet, document, or website) when
clicked.
● Menu Option: Insert > Hyperlink or Ctrl+K.
● Types of Hyperlinks:
1. Relative: Stores location relative to the current file (e.g., admin/[Link]). If you
move the whole folder, the link still works.
2. Absolute: Stores the complete path (e.g., C:\User\Docs\[Link]). If you move
the file, the link breaks.
4. Linking to External Data (Web/HTML)
● You can import tables from HTML pages (Websites).
● Steps: Sheet > Link to External Data $\rightarrow$ Enter URL $\rightarrow$ Select
Tables.
Chapter 7: Share and Review a Spreadsheet
This chapter covers collaboration—working on the same file with multiple people.
1. Sharing a Spreadsheet
● Purpose: Allows multiple users to access and edit the file simultaneously.
● Menu Option: Tools > Share Spreadsheet.
● Action: Check the box "Share this spreadsheet with other users".
● Visual Cue: The title bar will show (Shared) after the filename.
● Limitations: In shared mode, certain features are disabled (e.g., merging cells, inserting
charts, deleting sheets).
2. Track Changes
Definition: A feature that records every edit made to the document (who made it, when, and
what changed).
● To Enable: Edit > Track Changes > Record.
● Visual: Modified cells usually get a colored border. Hovering over the cell shows the
history.
● Protecting: You can password protect tracking via Edit > Track Changes > Protect.
3. Comments
● Purpose: To add notes or suggestions without changing the cell data.
● Steps: Insert > Comment or Edit > Track Changes > Comment.
● Visual: A small colored dot (usually red) appears in the top corner of the cell.
● Editing: Right-click cell $\rightarrow$ Show Comment / Edit Comment / Delete
Comment.
4. Reviewing Changes (Accept/Reject)
● The owner of the document reviews edits made by others.
● Menu Option: Edit > Track Changes > Manage.
● Dialog Box: You can see a list of changes. You can:
○ Accept: Make the change permanent.
○ Reject: Revert to the original data.
○ Accept All / Reject All.
5. Merging and Comparing Documents
● Merging: If multiple users worked on copies of the same file (not shared mode), you can
combine them.
○ Step: Open original $\rightarrow$ Edit > Track Changes > Merge Document
$\rightarrow$ Select the copy.
● Comparing: Checks differences between the original and an edited version.
○ Step: Open edited version $\rightarrow$ Edit > Track Changes > Compare
Document $\rightarrow$ Select original.
Important Keyboard Shortcuts for Board Exams:
● F11: Styles and Formatting.
● Ctrl + Shift + N: Manage Templates.
● Shift + F2: Edit Cell comment (common in Excel, often works in Calc depending on
version).
● Ctrl + K: Insert Hyperlink.
● F5: Navigator.
● Alt + D: Data Menu (access to Consolidate/Subtotal).
Key Differences to Remember:
● Goal Seek vs. Solver: Goal Seek handles one variable; Solver handles multiple
variables and constraints.
● Relative vs. Absolute Hyperlink: Relative moves with the file structure; Absolute is a
fixed path.
● Function vs. Sub (in Macros): A Function returns a value (like a math formula), while a
Sub performs an action (like formatting).