Electronic Spreadsheet (Advanced) using LibreOffice Calc - Comprehensive Study Guide

Unit Introduction: Electronic Spreadsheet (Advanced) using LibreOffice Calc

In the modern digital world, counting and calculations are among the most essential tasks. Every profession, household, institution, and smart device manages numbers. Computers facilitate calculations on vast records spread over sheets using spreadsheet software, which functions like an accountant’s ledger book where everything is recorded under headings. While basic spreadsheet functions including calculations and tabular data presentation are learned in Class 9, this unit focuses on advanced features in LibreOffice Calc.

Core Learnings of the Unit

• Analysis of Data: Extracting useful information to make effective decisions.
• Automation: Using Macros to handle repeated and redundant tasks.
• Collaboration: Linking, sharing, and reviewing data among multiple users.
• Data Management: Creating multiple sheets and linking data via keyboard/mouse referencing or external links.

Chapter 4: Analyse Data using Scenarios and Goal Seek

Analysing data is defined as the process of extracting useful information to facilitate effective decision-making. Spreadsheet software like Calc is used to retrieve, correlate, explore, and visualise data to identify patterns, trends, and relationships.

Consolidating Data

Consolidate is a function used to combine information from multiple sheets into a single location to summarize information. It allows for the comparison of diverse data to identify relationships.

Prerequisites for Consolidation

• Data Type Match: Ensure data types across sheets are consistent.
• Label Match: Match labels from all sheets used for consolidation.
• Primary Column: Enter the first column as the primary column used as the basis for consolidation.

Steps to Consolidate Data

1. Open the spreadsheet containing the data.
2. Create a new sheet for the consolidated results.
3. Navigate to Data > Consolidate.
4. Select a function from the Function drop-down menu. The default function is Sum. Other functions (Average, Max, Count, etc.) are available.
5. Select the Source data range. If undefined, click and select the range from the specific sheet.
6. Click Add to move the range to "Consolidation ranges". Repeat for all sources.
7. Specify the Target range under "Copy results to".
8. Click Options to select "Row labels", "Column labels", or "Link to source data".    - Link to source data: If checked, modifications in source sheets automatically update the target sheet.
9. Click OK.

Practical Activity 4.1: Sales Consolidation

• Scenario: Shop branches ABC and XYZ have sales records for January and February in sheets ABC_Branch and XYZ_Branch.
• Goal: Summarize monthly sales per product using the Sum function.
• Keyboard Shortcut: $ALT+D$ opens the Consolidate dialog.

Groups and Subtotals

Group and Outline

This feature allows users to create an outline of selected data, grouping rows or columns so they can be collapsed ($-$-) to hide details or expanded ($+$) to show them.

• Command: Data > Group and Outline > Group (choose Rows or Columns).
• Removal: Data > Group and Outline > Remove Outline.

Subtotal Tool

The Subtotal tool creates groups automatically and applies summary functions like Sum or Average. It groups subtotals by category and sorts them automatically.

Steps to apply Subtotals:

1. Ensure columns have labels.
2. Navigate to Data > Subtotals.
3. In the Group by list, choose the column for grouping.
4. Under Calculate subtotals for, select the target columns.
5. Under Use function, select the desired operation (Default: Sum).
6. Use 2nd Group and 3rd Group tabs for hierarchical levels.

Practical Activity 4.3: Class Marks Analysis

• Scenario: Average marks for subjects (English, Hindi, Maths, Science, Social Science) for students in Class X across different classes.
• Procedure: Group by "Class" and apply the "Average" function to subject columns.

What-if Scenarios

A What-if scenario is a set of values used within spreadsheet calculations. Multiple scenarios can be created on one sheet to compare alternatives.

Common Application: Car Loan EMI

• Variables: Principal amount, number of years.
• Formula for EMI: $= \text{ABS}(\text{PMT}(B2/1200, E1 \times 12, B1))$
• Formula for Total Amount: $= B3 \times E1 \times 12$
• Formula for Total Interest: $= B4 - B1$

Creating Scenarios:

1. Select cells containing variables (use $Ctrl + Click$ for multiple).
2. Navigate to Tools > Scenarios.
3. Assign a name to the scenario.
4. Use the Navigator icon to switch between created scenarios.

What-if Analysis Tool (Multiple Operations)

This tool (found via Data > Multiple Operations) is a planning tool for what-if questions. Unlike scenarios, results are displayed as a formula array in separate cells. It uses two arrays: one for input values and one for formula results.

Example: Profit Calculation

• Formula: $= B4 \times (B1 - B2) - B3$ ($B4$=items, $B1$=Selling Price, $B2$=Cost Price, $B3$=Fixed Cost).
• Absolute Referencing: Use symbols like $B$5 to fix the formula cell.

Goal Seek

Goal Seek performs backward calculation to find the input required for a specific output.

Steps for Goal Seek:

1. Place the cursor in the formula cell.
2. Navigate to Tools > Goal Seek.
3. Formula cell: The cell containing the result.
4. Target value: The desired outcome value.
5. Variable cell: The cell to be changed by the system to meet the target.

Practical Activity 4.9: Marks Target

• A student has marks for 4 subjects and needs a specific aggregate ($70$). Goal Seek calculates the required marks for the 5th subject. If the variable cell is empty, Calc will prompt to add a $0$ value before proceeding.

Chapter 5: Using Macros in Spreadsheet

Macros are sets of stored instructions (keystrokes or clicks) used to automate repetitive tasks. This ensures standardization and avoids typing errors.

Recording a Macro

• Enabling Macros: By default, recording is OFF. Enable via Tools > Options > LibreOffice > Advanced > Enable macro recording.
• Command: Tools > Macros > Record Macro.
• Non-recorded Actions: Opening windows, window switching, actions in other windows, mouse-based selections (use keyboard/cursor keys instead), and customizing options.
• Saving: Macros are typically saved in the Standard Library in Module1. The default name is Main.

Rules for Naming Macros/Modules/Libraries

• Must begin with a letter.
• Must not contain spaces.
• Must not contain special characters (except underscore, _).

Running and Editing Macros

• Running: Tools > Macros > Run Macro. Select from "My Macros" or the document's library.
• Editing: Macros are written in BASIC. View code via Tools > Macros > Edit Macros. Code begins with Sub and ends with End Sub.

Macro as a Function

Used for repetitive calculations where no predefined function exists. It can accept arguments and return results.

• Structure: Code is written between Function and End Function.
• Comments: Text preceded by a single quote (') appears in grey and is ignored by the compiler.

Chapter 6: Linking Spreadsheet Data

Linking avoids retyping and ensures data accuracy. If source data changes, linked references update automatically.

Setting up Multiple Sheets

• Add Sheet: Click the ($+$) sign at the bottom or Sheet > Insert Sheet.
• Referencing sheets in the same document: Use the syntax $'SheetName'.CellAddress</code>. Example: <code>$'Term 1'.C4.

Referencing Other Documents

• Syntax: 'file:///Path/FileName.ods'#$SheetName.CellAddress.
• Live Data: Linking allows access to updated data. Set update preferences via Tools > Options > LibreOffice Calc > General > Updating.

Hyperlinks

• Absolute Hyperlink: Stores the full path (e.g., C:\Users\ADMIN\try.ods). Fails if the file is moved.
• Relative Hyperlink: Stores location relative to the current file (e.g., Admin\try.ods). Moves with the folder.
• Command: Insert > Hyperlink.

External and Registered Data

• External Data: Insert tables from HTML/web pages via Sheet > Link to External Data. Use "Automatic" for language settings.
• Registered Data: Link databases (LibreOffice Base extension is $.odb$). Register via Tools > Options > LibreOffice Base > Databases.

Chapter 7: Share and Review a Spreadsheet

Sharing

Sharing allows multiple users to access and edit a spreadsheet simultaneously.

• Command: Tools > Share Spreadsheet.
• Signifier: The title bar displays "(shared)".
• Limitations: Features like Undo, Redo, Paste, and Links to External Files are disabled in shared mode.
• Conflict Resolution: If two users save conflicting changes, a "Resolve Conflict" dialog appears.

Track Changes

• Recording: Disable shared mode first, then navigate to Edit > Track Changes > Record. Changed cells get a red border.
• Comments: Reviewers can add comments via Edit > Track Changes > Comment or Insert > Comment (Notes/Suggestions). A colored dot appears in the cell corner.
• Reviewing: Use Edit > Track Changes > Manage to "Accept" or "Reject" changes.

Merging and Comparing

• Merging: Combine two versions of the same file into one via Edit > Track Changes > Merge Document.
• Comparing: Compare the current sheet with another via Edit > Track Changes > Compare Document to identify differences.

Questions & Discussion

Q: What is the default function in Consolidation? A: The default function is Sum.

Q: Which tool works in reverse order to find input based on output? A: Goal Seek.

Q: What is the file extension for Calc? A: The extension is $.ods$.

Q: True or False: Subtotal tool requires manual filters. A: False. It creates groups and sorts automatically.

Q: True or False: Relative hyperlinks fail if the folder is moved. A: False. They are bound to the source folder and will work as long as the relative path remains the same.

Q: Describe the rules for naming a Macro. A: It must start with a letter and contain no spaces or special characters except for the underscore (_).