Electronic Spreadsheet (Advanced) — LibreOffice Calc
1. Analysing Data using Consolidation
Consolidation combines data from several worksheets or cell ranges into one summary range using a function such as Sum, Average, Max, Min or Count. It is useful when similar data (e.g., monthly sales of different branches) is kept on separate sheets and a combined total is required.
- Open the sheet where the consolidated result is required and choose Data > Consolidate.
- Select the function (Sum, Average, etc.).
- In Source data ranges, select each range (from different sheets) and click Add.
- Set the Copy results to target range.
- Under Options, tick Row labels / Column labels to consolidate by labels, and Link to source data to keep the result updated automatically. Click OK.
2. Subtotals
The Subtotals tool automatically inserts group-wise totals (or other functions) in sorted data. Choose Data > Subtotals, select the column to Group by, tick the columns to calculate, choose the function (Sum, Average, Max, Min, Count …) and click OK. Calc sorts the data, inserts subtotal rows and a grand total, and shows an outline on the left to expand/collapse groups. Remove subtotals with Data > Subtotals > Remove/Delete.
3. What-if Analysis: Scenarios, Goal Seek and Solver
Scenario: a named, saved set of cell values that can be substituted into a worksheet to see "what-if" results. Created using Tools > Scenarios; each scenario appears with a coloured border and can be switched from the Navigator or the border list box.
- Goal Seek (Tools > Goal Seek): works backwards — you specify the Formula cell, the Target value you want it to reach, and the single Variable cell Calc may change. Calc calculates the required input value. Example: what marks are needed in the last test to make the average 90?
- Solver (Tools > Solver): an advanced form of Goal Seek that optimises (maximises or minimises) a target cell, can change multiple variable cells at once, and supports limiting conditions (constraints). Used for problems like maximising profit under resource limits.
4. Linking Data and Spreadsheets
A formula can refer to cells on another sheet of the same file or even in another file:
| Reference | Meaning |
|---|---|
=Sheet2.A1 | Cell A1 of Sheet2 (sheet name and cell separated by a dot) |
=Sheet2.A1+Sheet3.A1 | Adds A1 of Sheet2 and A1 of Sheet3 |
='Annual Report'.B5 | Sheet names containing spaces are enclosed in single quotes |
A1 (relative) | Changes when the formula is copied to another cell |
$A$1 (absolute) | Never changes when copied; $A1 / A$1 are mixed references (press Shift+F4 to cycle) |
Other linking methods: Insert > Hyperlink creates a clickable link to a website, e-mail or another document/cell; Sheet > Insert Sheet from File (with the Link option) and Sheet > External Links bring in data from another spreadsheet or a web page (external data) that can update automatically. To register data ranges, the Navigator (F5) can also drag ranges as links.
5. Sharing Worksheets and Tracking Changes
Sharing (Tools > Share Spreadsheet): ticking "Share this spreadsheet with other users" allows several users to open and edit the same file at the same time (collaboration). In shared mode some features are disabled (e.g., inserting pictures, charts, conditional formatting, merging cells). When two users change the same cell, a conflict-resolution dialog appears on saving.
Recording changes: choose Edit > Track Changes > Record (Ctrl+Shift+C). Changed cells get a coloured border, deleted content is marked, and pointing at a change shows who changed it and when. Comments can be added to a change via Edit > Track Changes > Comment (regular cell comments: Insert > Comment).
Reviewing: Edit > Track Changes > Manage lists every change; each change can be Accepted or Rejected individually or all together. Comparing documents: Edit > Track Changes > Compare Document marks the differences between the edited copy and the original as recorded changes. Merging: Edit > Track Changes > Merge Document combines the recorded changes from several reviewers' copies into one document for a single review.
6. Macros
Macro: a saved, named sequence of commands or keystrokes that can be run again and again to automate a repetitive task. Calc macros are stored in LibreOffice BASIC language inside modules.
Steps to record a macro:
- Enable the recorder: Tools > Options > LibreOffice > Advanced and tick Enable macro recording.
- Choose Tools > Macros > Record Macro. A small Stop Recording toolbar appears.
- Perform the actions to be recorded (typing, formatting, formulas).
- Click Stop Recording; in the LibreOffice Basic Macros dialog select the library (My Macros > Standard > Module1), type a macro name and click Save.
Running: Tools > Macros > Run Macro (or Tools > Macros > Basic Macros), select the macro and click Run. Editing: Tools > Macros > Edit Macros opens the Basic IDE, where the recorded code appears between Sub MacroName and End Sub. A macro can also be written directly in BASIC; user-defined functions created as macros can even be used in cell formulas, e.g. a function TotalMarks(a,b). Macro-containing files should be saved and opened with macro security set suitably (Tools > Options > Security).
7. Useful Calc Functions
| Function | Syntax | Purpose / Example |
|---|---|---|
| SUM | =SUM(A1:A10) | Adds the values of a range |
| AVERAGE | =AVERAGE(B2:B6) | Arithmetic mean of a range |
| MAX / MIN | =MAX(A1:A10) / =MIN(A1:A10) | Largest / smallest value |
| COUNT | =COUNT(A1:A10) | Counts numeric entries |
| COUNTIF | =COUNTIF(C2:C20,">40") | Counts cells meeting a condition |
| IF | =IF(D2>=33,"Pass","Fail") | Returns one of two values based on a test |
| ROUND | =ROUND(A1,2) | Rounds to given decimal places |
| VLOOKUP | =VLOOKUP(E2,A2:C10,3,0) | Searches the first column of a range and returns a value from the given column |
| TODAY / NOW | =TODAY() / =NOW() | Current date / date-time |
8. Important Menu Paths at a Glance
| Task | Menu Path |
|---|---|
| Consolidate data | Data > Consolidate |
| Insert subtotals | Data > Subtotals |
| Create scenario | Tools > Scenarios |
| Goal Seek / Solver | Tools > Goal Seek / Tools > Solver |
| Share spreadsheet | Tools > Share Spreadsheet |
| Record changes | Edit > Track Changes > Record (Ctrl+Shift+C) |
| Accept/Reject changes | Edit > Track Changes > Manage |
| Compare / Merge documents | Edit > Track Changes > Compare Document / Merge Document |
| Record / Run macro | Tools > Macros > Record Macro / Run Macro |
Exam Tip: Goal Seek changes only one variable cell; Solver can change many cells and apply constraints — this one-line difference is a favourite 2-mark question. Also remember: scenarios are created from Tools > Scenarios, not the Data menu.
Exam Tip: In sheet references the separator is a dot (=Sheet2.A1), unlike Excel's exclamation mark. Absolute reference $A$1 stays fixed when the formula is copied — practise one "copy this formula" question before the exam.