OpenOffice Calc Merge Tutorial — Combine Multiple Sheets & Files into One Workbook

Single File from Many: Join and Merge Multiple OpenOffice Calc Sheets and Spreadsheets

Merging multiple sheets and files into a single OpenOffice Calc workbook makes data easier to analyze, share, and back up. This guide shows simple, reliable methods—manual, semi-automated, and scripted—so you can choose the approach that fits your volume of data and comfort level.

When to merge

• You need one consolidated dataset for analysis, pivot tables, or charts.
• Multiple collaborators produced separate sheets with the same structure.
• You want a single file for archiving or sharing.

Preparation (always do these first)

1. Backup original files.
2. Standardize column headers and order across all sheets if you plan to stack rows.
3. Remove or mark duplicate IDs if duplicates are a concern.
4. Decide merge method: append rows (stack), join by key, or consolidate numeric data (sum, average).

Method 1 — Manual copy/paste (best for small numbers of sheets)

1. Open a new Calc workbook.
2. Open each source workbook in separate windows/tabs.
3. For each sheet to bring in:
   • Right-click the sheet tab → choose Move/Copy Sheet.
   • In the dialog, select the target workbook and position; check Copy to leave the source intact.
4. Repeat until all sheets are in the single workbook. Notes: Preserves formats and formulas that reference local sheets; faster than copy/paste for many sheets.

Method 2 — Append rows into one master sheet (same columns)

1. Open a new sheet called “Master”.
2. Copy headers from one source sheet into Master row 1.
3. From each source sheet (excluding header rows), select the data range and paste below the last row in Master.
4. After pasting all, use Data → Sort to order rows; use Data → Filter or Remove Duplicates extensions if needed. Tip: Use Paste Special → Values to avoid bringing unwanted formatting or links.

Method 3 — Use Data → Consolidate (numeric aggregation)

1. In the target sheet, choose Data → Consolidate.
2. Select the function (Sum, Average, Count, etc.).
3. Click Add and select ranges from each source sheet or file.
4. Check “Top row” and/or “Left column” if you want consolidation by labels.
5. Click OK; Calc will create consolidated values—useful for combining summarized tables.

Method 4 — Drag-and-drop sheet copying between open workbooks

1. Open both workbooks in the same Calc instance.
2. Click and drag a sheet tab from the source to the target workbook tab bar.
3. Release to move; hold Ctrl (or Option) while dragging to copy instead of move. Quick for a handful of sheets.

Method 5 — Automated merging with a macro (best for many files)

Use a basic LibreOffice/OpenOffice Basic macro to loop through files in a folder and copy sheets into one workbook.

Sample macro (paste into Tools → Macros → Organize Macros → OpenOffice Basic):

basic
Copy CodeCopied
Sub ImportSheetsFromFolder Dim folderPath As String
  folderPath = ”/home/username/CalcFiles/” ‘ <-- change to your folder (use / or </span>)
  Dim fileName As String
  Dim desktop As Object, dispatcher As Object   desktop = CreateUnoService(“com.sun.star.frame.Desktop”)
  fileName = Dir(folderPath & ”*.ods”)
  Dim targetDoc As Object   targetDoc = desktop.loadComponentFromURL(“private:factory/scalc”, ”_blank”, 0, Array())
  Do While fileName <> ””
    Dim fileUrl As String
    fileUrl = ConvertToUrl(folderPath & fileName)
    Dim srcDoc As Object     srcDoc = desktop.loadComponentFromURL(fileUrl, ”_blank”, 0, Array())
    Dim i As Integer
    For i = 0 To srcDoc.Sheets.getCount() - 1
      srcDoc.Sheets.getByIndex(i).copyTo(targetDoc.Sheets, targetDoc.Sheets.getCount())
    Next i     srcDoc.close(True)
    fileName = Dir()
  Loop
  targetDoc.Sheets.getByIndex(0).Name = “Merged-1”
  MsgBox “Import complete.”
End Sub

Notes:

• Change folderPath to your directory.
• Macro copies all sheets from all .ods files in that folder.
• Test on copies first; macro actions can’t be undone.

Method 6 — Use CSV intermediate files (for cross-application compatibility)

1. Save each sheet as CSV (File → Save As → CSV) if structure is simple.
2. Open target Calc, use Insert → Sheet From File or File → Open and copy into Master. Benefit: Removes formulas and formatting—safe for raw data merges.

Post-merge cleanup

• Use Data → Filter and conditional formatting to spot inconsistencies.
• Use Find & Replace to normalize formats (dates, currency).
• Recalculate formulas (Tools → Cell Contents → Recalculate).
• Save the consolidated workbook with a clear name and version.

Troubleshooting common issues

• Broken formulas referencing external sheets: convert to values or update references.
• Different date formats: use Value/DATE functions or Text to Columns to normalize.
• Duplicate rows: Data → More Filters → Standard Filter can help, or use a macro.

Recommendations

• For occasional merges of a few files, use Move/Copy Sheet or drag-and-drop.
• For repeated merges from many files, use a macro or export/import via CSV and automate with a script.
• Keep a clean “Master” template with standardized headers to speed future merges.

If you want, I can:

• Provide a ready-to-run macro customized for your folder paths,
• Walk through a step-by-step example with sample data,
• Or produce a short macro that appends only sheets with a specific name.