Electronic Spreadsheets (Advanced)

1. Introduction to Advanced Spreadsheets

In basic spreadsheets, users enter tabular data, apply standard arithmetic operators, and format simple cells. Advanced Spreadsheet analysis in software like LibreOffice Calc and Microsoft Excel allows users to perform multi-source data consolidation, run what-if simulations, reverse-calculate target values, collaborate simultaneously with multiple team members, track every revision, and automate repetitive operations through macros.

These features are widely used in enterprise budget planning, inventory control, school grade management across academic terms, and financial risk forecasting.

2. Consolidating Data

Data Consolidation is the process of gathering and summarizing data from multiple independent worksheets or workbooks into a single master summary worksheet. Instead of manually copying and summing figures from multiple branches or quarters, the Consolidate tool automates the process.

3. Step-by-Step Data Consolidation Workflow

To consolidate sales figures from Term 1, Term 2, and Term 3 sheets into an Annual Summary sheet in LibreOffice Calc:

1. Open the workbook containing the source sheets and create or open a target sheet (e.g., Annual_Summary).
2. Click on the cell where you want the top-left corner of the consolidated data to appear (e.g., cell A1).
3. Click Data > Consolidate... from the main menu bar.
4. In the Consolidate dialog box, select the required function from the Function dropdown (e.g., SUM).
5. Click in the Source data range field, switch to the first sheet, select the cell range, and click Add. Repeat for all source sheets.
6. Expand the Options section: check Row labels and Column labels to match data headers automatically.
7. Check Link to source data if you wish the summary table to reflect future edits made in the source sheets.
8. Click OK. The combined data will populate the destination range.

4. Creating Subtotals

The Subtotals tool automatically groups related rows together, inserts subtotal summary rows with chosen functions, and provides an overall Grand Total at the bottom. It also adds an outline bar on the left with levels (1, 2, 3) to expand or collapse details.

• Prerequisite: Data must be sorted by the grouping column before applying subtotals. If data is not sorted, duplicate group headers and scattered subtotals will occur.
• Group by: The column used to split records into categories (e.g., Department, Class, City).
• Calculate subtotals for: The numeric columns you want to aggregate (e.g., Salary, Marks, Sales).
• Use function: Aggregate function such as SUM, AVERAGE, MAX, or COUNT.

Steps to apply Subtotals: Select the data range > Sort by grouping column > Select Data > Subtotals... > Choose the group column, calculate subtotal column, and function > Click OK.

5. 'What-If' Scenarios

Scenarios are a powerful What-If Analysis tool used to test different sets of input variables on the same worksheet without overwriting data or creating multiple duplicate sheets. Each scenario has its own name and can be selected instantly from a drop-down list inside the sheet border.

For instance, an event manager can create three scenarios for an annual school fete:

• Best-Case Scenario: High ticket sales (500 tickets @ ₹200) + low stall costs.
• Expected Scenario: Moderate sales (350 tickets @ ₹200) + standard costs.
• Worst-Case Scenario: Low ticket sales (150 tickets @ ₹200) + high contingency costs.

6. Managing and Comparing Scenarios

1. Select the cells that contain the variable input values you wish to alter.
2. Select Tools > Scenarios... from the menu.
3. In the Create Scenario dialog box, enter a clear descriptive name (e.g., Budget_Optimum).
4. Add an optional description comment and choose a border colour for clear identification.
5. Check Display border and Copy back (which writes changes made while viewing the scenario back into the scenario data).
6. Click OK. Change the values in the selected cells to reflect the scenario.
7. To switch between scenarios, click the small dropdown arrow on the scenario border header in the worksheet.

7. Using Goal Seek

Normally, a formula calculates an output from known input cells (forward calculation). Goal Seek works in reverse: when you know the final target result you want from a formula, Goal Seek determines what input value is needed in a designated cell to achieve that result.

8. Goal Seek Practical Applications

Academic Example: A student has scored 75, 82, 68, and 70 in four subjects. The pass requirement for total marks across 5 subjects is 375. The student uses Goal Seek to calculate the exact marks needed in Subject 5.

1. Cell B6 has the total formula: =SUM(B1:B5). Cell B5 is currently 0 or blank.
2. Go to Tools > Goal Seek...
3. In Formula cell, enter B6.
4. In Target value, type 375.
5. In Variable cell, click or enter B5.
6. Click OK. Calc runs iterative calculations and displays: "Goal Seek successful. Insert result (80) into the variable cell?". Click Yes.

9. Solver Tool (Advanced Goal Seek)

While Goal Seek can solve equations for only one variable without constraints, the Solver tool is designed for complex optimisation problems involving multiple changing variables and defined mathematical constraints (e.g., maximum budget limits, non-negative quantities, whole integer units).

• Target Cell: Can be set to a maximum, minimum, or specific value.
• By changing cells: A contiguous or non-contiguous range of multiple input cells.
• Limiting Conditions (Constraints): Rules such as Cell <= Value, Cell >= Value, or Cell = Integer.
• Access via Tools > Solver... in LibreOffice Calc.

10. Setting Up and Managing Multiple Sheets

Workbooks can contain multiple worksheets to keep data organised by month, class, subject, or branch.

• Insert Sheet: Click the + icon next to the sheet tabs at the bottom, or select Sheet > Insert Sheet.... You can specify whether to insert before or after the current sheet, number of sheets, and sheet names.
• Rename Sheet: Right-click the sheet tab > choose Rename Sheet..., or double-click the tab. Names cannot contain characters like \ / ? * : [ ].
• Delete Sheet: Right-click the sheet tab > choose Delete Sheet....
• Move or Copy Sheet: Right-click tab > select Move or Copy Sheet... > select destination position and check Copy if duplicating.

11. Referencing Other Sheets (Mouse & Keyboard)

Formulas can pull data from other sheets in the same workbook. The reference syntax in LibreOffice Calc uses a period (.), while MS Excel uses an exclamation mark (!):

• Calc Syntax: =SheetName.CellAddress (e.g., =Term1.B4 + Term2.B4)
• Excel Syntax: =SheetName!CellAddress (e.g., =Term1!B4 + Term2!B4)
• If the sheet name contains spaces, enclose it in single quotes: ='Term 1'.B4.

Referencing with the Mouse:

1. In the target cell, type =.
2. Click on the desired sheet tab at the bottom of the window.
3. Click on the specific cell you wish to reference.
4. Type an operator (e.g., +) and repeat, or press Enter to complete the formula.

12. Referencing External Workbooks

You can also reference cells located in completely separate spreadsheet files. This is called an external link.

In LibreOffice Calc, the syntax references the external file path: ='file:///path/to/workbook.ods'#$Sheet1.A1.

When an external workbook is updated, Calc prompts the user upon opening: "Update all links?". Selecting yes fetches refreshed figures. Manage links via Edit > Links to External Files....

13. Working with Hyperlinks (Relative vs Absolute)

Hyperlinks allow jumping to a website, another file, or a specific cell bookmark within the current workbook. Insert via Insert > Hyperlink... (or shortcut Ctrl + K).

14. Linking to External Data & Registered Sources

Calc allows users to dynamically import tables from web pages (HTML tables) or databases:

• Link to External Data: Go to Sheet > Link to External Data... > paste the URL or file path > select the HTML table name > set an automatic refresh interval (e.g., update every 60 seconds for live stock prices or scores).
• Registered Data Sources: Connect Calc to external databases (e.g., LibreOffice Base .odb, MySQL, ODBC). Press F4 to open the Data Sources browser and drag database tables directly into Calc sheets.

15. Sharing a Spreadsheet for Multi-User Editing

Sharing enables multiple users on a computer network to open, edit, and save changes to the same workbook concurrently.

1. Open the spreadsheet document you want to share.
2. Go to Tools > Share Spreadsheet....
3. In the dialog box, check the box: Share this spreadsheet with other users.
4. Click OK. Save the document when prompted.
5. The title bar will display (shared) next to the file name.

Note: In shared mode, certain editing functions such as deleting sheets, conditional formatting, and insert charts are temporarily disabled to prevent file corruption.

16. Recording and Tracking Changes

When multiple collaborators review a document, it is essential to track who made which modification. Calc provides a comprehensive Record Changes feature.

• To start tracking: Go to Edit > Track Changes > Record (shortcut: Ctrl + Shift + C).
• Modified cells are highlighted with a colored border and a small colored triangle in the top-left corner.
• Hovering the mouse pointer over the cell displays a tooltip with the author's name, date, time, old value, and new value.
• Deleted rows or columns are indicated by a colored bar across row or column headers.

17. Reviewing, Accepting, and Rejecting Changes

The document owner can review every recorded change before finalizing the sheet:

1. Select Edit > Track Changes > Manage... (or Accept or Reject...).
2. A dialog displays a list of all tracked modifications with Author, Date, Time, Cell, and Action.
3. Click on an individual entry to highlight the affected cell.
4. Click Accept to keep the edit, or Reject to restore the original value.
5. Use Accept All or Reject All to process all pending edits simultaneously.
6. Use the Filter tab to view edits by a specific author, date range, or cell area.

18. Adding, Editing, and Viewing Comments

Collaborators can attach notes to cells without modifying cell values:

• Insert Comment: Right-click the cell > choose Insert Comment (or Ctrl + Alt + C).
• Type the comment in the popup callout box and click outside. Cells with comments show a small red indicator square.
• Edit Comment: Right-click cell > select Edit Comment.
• Delete Comment: Right-click cell > select Delete Comment.
• Show/Hide Comment: Right-click cell > select Show Comment to keep the note visible permanently on screen.

19. Comparing and Merging Worksheets

If two colleagues edited independent copies of the same workbook without shared mode or network access, you can compare or merge their changes into the original file:

• Compare Document: Open original sheet > go to Edit > Track Changes > Compare Document... > select the modified copy. Calc turns on change tracking and displays differences in the Accept/Reject dialog.
• Merge Document: Open original workbook > select Edit > Track Changes > Merge Document... > select the modified file. Edits from the second file are merged in as tracked revisions. Both files must originate from the exact same ancestor copy.

20. Introduction to Macros

A Macro is a recorded or coded sequence of actions, commands, and keystrokes saved under a single identifier. Whenever the macro is run, the application repeats those exact steps automatically.

• Why use Macros? Eliminates repetitive formatting, automates standard weekly report creation, and reduces human error in repetitive calculations.
• Language: In LibreOffice Calc, macros are written in LibreOffice Basic (or Python/JavaScript). In MS Excel, macros use VBA (Visual Basic for Applications).

21. Recording Macros with Macro Recorder

The easiest way to build a macro without writing program code is using the Macro Recorder:

1. Enable the recorder if disabled: Tools > Options > LibreOffice > Advanced > check "Enable macro recording".
2. Click Tools > Macros > Record Macro. A small floating toolbar with a "Stop Recording" button appears.
3. Perform the sequence of actions you want to automate (e.g., set header font to Arial 12pt Bold, fill background with light blue, add bottom border).
4. Click Stop Recording on the floating toolbar.
5. In the LibreOffice Basic Macros dialog, expand My Macros > Standard > Module1.
6. Type a name for your macro (e.g., FormatHeader) in the Macro name box and click Save.

22. Running and Assigning Macros (Shortcuts & Toolbars)

Once recorded, you can execute your macro through multiple convenient methods:

• Run via Menu: Select Tools > Macros > Run Macro... > browse to your macro > click Run.
• Assign to a Shortcut Key: Go to Tools > Customize... > Keyboard tab > select an available shortcut (e.g., Ctrl + Shift + H) > find your macro under Category > click Modify > click OK.
• Assign to a Toolbar Button: Go to Tools > Customize... > Toolbars tab > click Add > choose your macro > assign an icon.
• Assign to a Push Button: Enable Form Controls toolbar > draw a Button > open Control Properties > under Events > Execute action, browse and link your macro.

23. Macros as Functions and Passing Arguments

Beyond automating keystrokes, macros can be written as User-Defined Functions (UDFs) that take arguments and return values just like built-in functions (such as SUM or SQRT).

Example LibreOffice Basic Function to calculate Simple Interest:

REM User-Defined Function in LibreOffice Basic
Function CalcSimpleInterest(Principal As Double, Rate As Double, Time As Double) As Double
    CalcSimpleInterest = (Principal * Rate * Time) / 100
End Function

Once saved in the Standard module, you can type in any cell: =CALCSIMPLEINTEREST(10000, 7.5, 2) and Calc will return 1500.

24. Important Terms & Definitions