LibreOffice Calc Advanced

Cheatsheet Content

1. Introduction to Advanced Spreadsheets Electronic spreadsheet software (like LibreOffice Calc) is essential for calculations and data management. It helps in analyzing data, automating tasks (macros), linking, sharing, and reviewing data. 2. Consolidating Data Combines information from multiple sheets into one to summarize and identify trends. 2.1. Pre-consolidation checks: Data types must match. Labels from all sheets must match. First column should be the primary consolidation column. 2.2. Steps to Consolidate: Open spreadsheet, create new sheet for consolidated data. Go to Data > Consolidate . Choose a function (e.g., "Sum"). Select source data ranges from sheets and Add them. Specify target range in "Copy results to". (Optional) In Options : Check "Row labels" and/or "Column labels" to consolidate by matching labels. Check "Link to source data" to automatically update target sheet with source changes. Click OK . Consolidated sheet shows original data details by clicking '+' sign. 3. Groups and Subtotals Used to outline and group rows/columns, applying summary functions automatically. 3.1. Group and Outline: Select data, then Data > Group and Outline > Group . Choose "Rows" or "Columns" to group. Collapse/expand groups using '-' / '+' signs. Remove outline: Data > Group and Outline > Remove Outline . 3.2. Subtotal Tool: Ensure columns have labels. Go to Data > Subtotals . In "1st Group": Choose a column in "Group by" for data grouping. Select columns under "Calculate subtotals for". Choose "Use function" (e.g., Sum, Average) for each selected column. (Optional) Use "2nd Group" and "3rd Group" tabs for further levels. Click OK . 4. What-if Scenarios A set of values used in calculations to explore and compare alternatives based on changing conditions. 4.1. Steps to Create a Scenario: Select cells containing values to be changed (use Ctrl+click for multiple). Go to Tools > Scenarios . Enter a name for the new scenario. Click OK . Scenarios can be accessed via the Navigator (toolbar icon) or Tools > Scenarios . 5. What-if Analysis Tool (Multiple Operations) A planning tool to see how a single formula's output changes with a list of alternative input values, displaying results in an array. 5.1. Steps for One Formula, One Variable: Enter data and a formula calculating a result from other cells. Create an array of input values. Select the cell range of the input array and the output array (where results will appear). Go to Data > Multiple Operations . Enter the cell address of the formula in the "Formulas" box. Enter the cell address of the variable (input cell) in the "Column input cell" box (or "Row input cell" if inputs are in a row). Click OK to generate outputs. 6. Goal Seek Finds the input value needed to achieve a specific target output from a formula. 6.1. Steps to Use Goal Seek: Enter values in the worksheet. Write the formula in the cell where the calculation is used. Place cursor in the formula cell. Go to Tools > Goal Seek . "Formula cell" will be pre-filled. Place cursor in "Variable cell" box and click on the cell containing the value to be changed. Enter the desired result in the "Target value" box. Click OK . If the variable cell is empty, it may prompt to add a 0 value. Click Yes, then repeat from step 2. Click Yes to apply the calculated value. 7. Macros A single instruction executing a set of instructions (sequence of commands or keystrokes) to automate repeated tasks. 7.1. Enabling Macro Recording: Go to Tools > Options > LibreOffice > Advanced . Check "Enable macro recording" under "Optional Features". 7.2. Recording a Macro: Go to Tools > Macros > Record Macro . Perform desired actions. Click "Stop Recording" button. In "Basic Macros" dialog: Select object to save macro to. (Optional) Change macro name. Click Save . 7.3. Actions NOT Recorded by Macro: Opening windows. Actions in other windows than where recording started. Window switching. Actions not related to spreadsheet contents (e.g., Options dialog, macro organizer). Mouse selections (only keyboard cursor movements are recorded). 7.4. Running a Macro: Go to Tools > Macros > Run Macro . In "Macro Selector" dialog: Select the library and module. Select the macro name. Click Run . 7.5. Naming Rules (Macro, Module, Library): Must begin with a letter. No spaces. No special characters except underscore (_). 7.6. Editing a Macro: Go to Tools > Macros > Edit Macros . Select the macro. Code is in BASIC (starts with Sub MacroName , ends with End Sub ). Warning: Edit only if familiar with BASIC. 7.7. Macro as a Function: Define a macro that accepts arguments, performs calculations, and returns a result. Go to Tools > Macros > Organize Macros > LibreOffice Basic . Select the spreadsheet file. Click New to create a module. In the IDE, write the function: Function FunctionName(arg1, arg2) ' Calculations FunctionName = result End Function Save the module. Use the function directly in a cell: =FunctionName(cell1, cell2) 8. Linking Spreadsheet Data Connecting data across multiple sheets or documents to ensure consistency and efficiency. 8.1. Setting up Multiple Sheets: Add a new sheet: Click '+' sign at bottom-left or right-click sheet tab > Insert Sheet . Rename sheets for clarity. 8.2. Referencing Other Sheets (Same Document): To refer to cell C4 in a sheet named "Term 1": ='Term 1'.C4 Steps for formula (e.g., average of C4 from Term1 and Term2 sheets): In target cell, type =SUM( or = , then select function. Click on "Term1" sheet, select cell C4. Type , (comma). Click on "Term2" sheet, select cell C4. Close function parenthesis (e.g., )/2 for average). Press Enter. Changes in source sheets reflect in linked cells. 8.3. Referencing Other Documents (Different Files): To refer to cell C4 in "Result" sheet of "X-A.ods" located at C:\Users\ADMIN\Documents: ='file:///C:/Users/ADMIN/Documents/X-A.ods'#$Result.C4 Steps for formula (e.g., MAX of C4 from two external files): Open all relevant spreadsheets. In target cell, type =MAX( . Click on the external sheet, select the cell. Type , (comma). Click on the second external sheet, select the cell. Close parenthesis and press Enter. 8.4. Inserting Sheet from Another File: Go to Sheet > Insert Sheet > From File . Click Browse , select the source file. Select the desired sheet from the list. Check "Link" checkbox to maintain a live link. Click OK . Update links: Tools > Options > LibreOffice Calc > General > Updating . 9. Hyperlinks Allows jumping to different locations (same sheet, different spreadsheet, or website). 9.1. Absolute vs. Relative Hyperlinks: Absolute: Stores full path (e.g., C:\Users\ADMIN\file.ods ). Breaks if file moves. Relative: Stores path relative to current document (e.g., Subfolder\file.ods ). Stays functional if folder structure is moved. 9.2. Creating Hyperlinks: Go to Insert > Hyperlink . In "Hyperlink" dialog: Select "Document" on the left. Click Browse next to "Path" to select target file. Click Target to choose specific sheet/range within the document. Enter display text in "Text" box. Click Apply , then Close . To open: Ctrl+click the hyperlink. 9.3. Editing/Removing Hyperlinks: Right-click on hyperlink > Edit Hyperlink... to modify. Right-click on hyperlink > Remove Hyperlink to delete. 10. Linking to External Data 10.1. From Web Pages (HTML): Select target cell. Go to Sheet > Link to External Data . Enter URL of webpage. (Optional) Select language for import. Choose desired table from "Available Tables/Ranges" (e.g., "HTML_10" for a specific table or "HTML_all" for entire document). Click OK . 10.2. From Registered Data Sources (e.g., LibreOffice Base .odb): Registering informs LibreOffice about data source type and location. Go to Tools > Options > LibreOffice Base > Databases . Click New . Enter location of database file (or Browse ). Type a name for the registered database. Click OK . 11. Share and Review a Spreadsheet Enables collaboration on a single spreadsheet by multiple users. 11.1. Sharing Spreadsheet: Save the spreadsheet. Go to Tools > Share Spreadsheet . Check "Share this spreadsheet with other users". Click OK . Confirm to save and activate shared mode. "(shared)" appears in the title bar. 11.2. Opening and Saving a Shared Spreadsheet: When opening, a message indicates shared mode (some features unavailable). Unavailable features: Undo, Redo, Repeat, Paste, Links to External files, ImageMap, Object. When saving: If no conflicts, updates with other users' changes. If conflicts, "Resolve Conflict" dialog appears. If another user is resolving conflicts, file may be locked for saving. 11.3. Recording Changes: Tracks modifications made by users. Disable Shared Mode first: Go to Tools > Share Spreadsheet , uncheck "Share this spreadsheet...", click OK . Go to Edit > Track Changes > Record to enable. Changes made will show a red border; hovering shows description (author, date, old/new value). 11.4. Add, Edit, and Format Comments: Adding Comments (Track Changes): Edit > Track Changes > Comment . Enter text. Adding Comments (Notes/Suggestions): Select cell, Insert > Comment . Type text. A colored dot appears. Viewing/Editing/Deleting Comments: Right-click cell with comment. Options: "Show Comment", "Edit Comment", "Delete Comment". Formatting Comments: Right-click cell with comment > Format Comment . Adjust font, color, background, etc. 11.5. Reviewing Changes (View, Accept, or Reject): Go to Edit > Track Changes > Show to filter which changes to display. Go to Edit > Track Changes > Manage . In "Manage Changes" dialog, select a change and click Accept , Accept All , Reject , or Reject All . Click Close when done. 11.6. Merging and Comparing Documents: Merging: Combines changes from two versions of the same file. Open base file (e.g., Test.ods). Go to Edit > Track Changes > Merge Document . Select the other file (e.g., Test1.ods) to merge. "Manage Changes" dialog appears; accept/reject changes. Comparing: Shows differences between two files, allowing acceptance/rejection. Open one file (e.g., Test.ods). Go to Edit > Track Changes > Compare Document . Select the other file (e.g., Test1.ods) to compare with. "Manage Changes" dialog appears; accept/reject changes.