Consolidate Data Across Worksheets in LibreOffice Calc

Consolidating data across multiple worksheets in LibreOffice Calc allows you to combine, summarize, and analyze information from different sheets into a single master sheet. This guide provides a straightforward, step-by-step walkthrough of the two most effective methods: using Calc’s built-in Consolidate tool to aggregate ranges with functions like SUM or AVERAGE, and using multi-sheet 3D formulas for quick calculations.

Method 1: Using the Consolidate Tool

The built-in Consolidate tool is best for combining structured tables, with options to link live data or match data based on row and column headers.

  1. Prepare Your Data: Ensure the data across your source sheets shares a consistent structure or clear row/column labels.
  2. Select the Destination: Open a new or existing target worksheet and click the top-left cell where you want the consolidated summary to appear.
  3. Open the Tool: In the top menu, go to Data > Consolidate….
  4. Choose a Function: In the Function dropdown, select the operation you want to perform (such as Sum, Average, Count, or Max).
  5. Add Source Ranges:
    • Click the shrink button next to the Source data ranges field.
    • Navigate to your first sheet, select the data range, and click the expand button.
    • Click Add to register the range into the consolidation list.
    • Repeat this process for every worksheet you want to include.
  6. Configure Options:
    • Expand the Options section at the bottom of the dialog.
    • Check Row labels and Column labels if you want Calc to match data based on headers rather than strict cell coordinates.
    • Check Link to source data if you want the summary sheet to update automatically when the source sheets change.
  7. Complete the Process: Click OK to generate your consolidated data table.

Method 2: Using 3D Cell References (Formula Method)

If your worksheets share an identical layout and you only need a quick mathematical total across identical cells, use a 3D reference formula.

  1. Click the cell on your summary sheet where you want the result.
  2. Enter a formula using the following syntax: =SUM(Sheet1.B2:Sheet4.B2) (Replace Sheet1 with your first sheet name, Sheet4 with your last sheet name, and B2 with the target cell).
  3. Press Enter.
  4. Drag the fill handle (the small square at the bottom-right corner of the cell) across adjacent rows and columns to apply the formula to the rest of your dataset.