Video: 6 ways to merge Excel sheets with 1 add-in

Combining data from multiple sheets becomes effortless if you have Consolidate Worksheets Wizard for Excel. See all the ways this add-in lets you copy, consolidate, and merge data from your tables.

Merge Excel worksheets without copying and pasting: video transcript

There are different ways we combine our records in Excel: sometimes we integrate data from several tables, copy some sheets into one file, or sum numbers from a few reports.

Combine Excel sheets by columns

First, let's look at one common challenge we have when joining records from different tables. The columns may be the same, but the order is often different.

Consider column headers when merging excel sheets

Consolidate Worksheets Wizard has a special tool that considers column headers when it copies the values.

Here are your simple steps for when you need to do it:

  1. Go to Ablebits Data tab and click Combine Sheets.
  2. First it will ask you to pick all worksheets you want to join. You are free to limit the range or browse for any additional files that are not open in Excel. If you want to select the columns to bring over, keep the checkbox at the bottom selected, and click next.
    Merge excel worksheets into one master worksheet
  3. Once you're ready, choose how to paste your records.
    • You can paste them as they are
    • If you have formulas that may stop working in a new file, go for values
    • Or you can add links to the original spreadsheet
    • How to merge  excel sheets into one

    • If your tables have the same style and custom cell formats, preserving them will save you some time, just like unselecting this option if each table looks different.
    • One more handy option is adding a blank row between records from different sheets.
  4. Choose where you want to get the result and click this button to combine data from the same columns.

4 ways to copy data from multiple worksheets into one

If all you need to do is copy the records, it may become pretty time-consuming when you have dozens of spreadsheets and a certain pattern for copying.

Copy data from multiple worksheets into a single sheet

There are 4 typical ways we group information in Excel and they are all covered by 2-step wizards that come with the add-in:
4 ways to merge excel worksheets without copying and pasting

  • One of them will simply copy all selected records and bring them together.
  • One copies the worksheets you select into one file.
  • You can even join data from the sheets that have the same name. This option is crucial when you need to quickly organize information.
  • The first option groups data within each file.Let me show you one example. Say, you have yearly reports with monthly data in 12 separate sheets. So this tool lets you quickly copy all monthly data within a year into one sheet.
    How to combine excel sheets
    So you choose to place the ranges one under another, preserve formatting, and paste values. Click Copy and now you have one sheet for each year in your new Excel file.
    Merge Excel spreadsheets

consolidate data from multiple worksheets in a single worksheet

When consolidating numbers, you have a standard Excel tool at hand. But when you have to look at a lot of files, this add-in will streamline the process.

Consolidate data from multiple Excel sheets

To get a summary using sales reports from different stores, all you need to do is pick the sheets with your data, select the function for the numbers, and choose where to get your results.

You can download a free evaluation version of the add-in and see how much time it saves you.

If you have any questions about it, please contact us, we'll do our best to assist you.

You may also be interested in

Ukraine flag War in Ukraine. Here's what Ablebits is doing to make sure our team and projects are safe.