Oct
31

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 by email, phone, or on the forum, we'll do our best to assist you.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard