Electronic Spreadsheet – Advanced

What is Track Changes?

Track Changes is a feature that records all edits made to a spreadsheet, showing who made the change, when, and what was changed. This is extremely useful when multiple people are collaborating on a spreadsheet.

• Enable: Edit → Track Changes → Record Changes.
• View changes: Edit → Track Changes → Show Changes — highlights modified cells.
• Accept/Reject: Edit → Track Changes → Accept or Reject Changes.
• Changed cells appear with a coloured border and a small triangle in the corner.
• Hover over a changed cell to see a tooltip with the editor's name and the original value.

Share Document vs Track Changes

Share Document (Tools → Share Spreadsheet) allows multiple users to edit the same document simultaneously over a network. Track Changes logs what changes were made. These two features work together for collaborative editing.

Recording and Running Macros

A Macro records a sequence of actions so you can replay them with a single click or keyboard shortcut.

• Record: Tools → Macros → Record Macro → perform your actions → click Stop Recording.
• Run: Tools → Macros → Organize Basic Macros → select macro → Run.
• Assign to button: You can assign a macro to a toolbar button or a keyboard shortcut.
• Macros are stored in Modules under My Macros or within the document.
• LibreOffice macros use the LibreOffice Basic programming language (similar to VBA).

⚠️ Exam Tip: The correct path to record a macro is Tools → Macros → Record Macro. This is the same in both Writer and Calc.

Scenarios in LibreOffice Calc

Scenarios allow you to save and switch between different sets of input values to see how they affect the results of formulas. This is called What-if Analysis.

• Create a Scenario: Select input cells → Tools → Scenarios → Add Scenario → Give it a name → Enter scenario values.
• Switch between scenarios: Use the Scenarios Navigator or the dropdown that appears on the sheet.
• Delete a Scenario: Tools → Scenarios → Select → Delete.
• Use case example: A student wants to test how different discount rates (5%, 10%, 15%) affect the total revenue of a shop.

Goal Seek — Related Tool

Goal Seek works the opposite way to Scenarios: you specify the desired result of a formula, and Calc finds the input value needed to achieve it.

• Access: Tools → Goal Seek.
• Example: "I want Cell B5 (Total) to equal ₹50,000. What should the unit price be?"

Consolidate — Merging Data from Multiple Sheets

Consolidate combines data from multiple source ranges (on the same sheet or different sheets) into a single destination range using a selected function.

• Access: Data → Consolidate.
• Default function: Sum — When the Consolidate dialog opens, Sum is selected by default. ⭐
• Other available functions: Average, Max, Min, Count, etc.
• You can check "Link to source data" to keep the consolidated data updated automatically.

Absolute vs Relative Hyperlinks

A Hyperlink is a clickable link in a cell that opens a web page, another document, or jumps to a cell in the same spreadsheet.

Insert hyperlink: Insert → Hyperlink (or Ctrl+K).

Creating Pivot Tables in Calc

A Pivot Table (called DataPilot in older versions of LibreOffice) summarises and analyses large data sets, allowing you to group, filter, and aggregate data interactively.

• Access: Insert → Pivot Table (or Data → DataPilot → Start).
• Drag fields to: Row Fields, Column Fields, Data Fields, Filter Fields.
• Data Fields show aggregated values (Sum, Count, Average, etc.).
• Pivot Tables are excellent for generating quick sales summaries, attendance reports, etc.