How to Combine and Merge Data from Multiple Worksheets

Sometimes when you have to deal with a lot of information, you may get a request to combine and merge data from multiple worksheets into one worksheet. That’s not a difficult task, but it can be time-consuming, especially if you’ve never done it before.

The best way to proceed is by using Power Query. If you’re wondering what Power Query is and where can you get it, don’t worry. Power Query is an inbuilt feature in Excel 2016 and later versions. In case you have an older version, you can download it as an add-in feature.

Before You Begin

If you’re using an older version of Excel, all you have to do is go to the Microsoft website. Search for Power Query in the search bar at the top right corner and then download it. Make sure that you download the version correspondent to your own version of Excel. When the download is completed, you’ll see Power Query in the upper toolbar.

In order to combine data using Power Query, your data has to be organized in Excel Tables. No matter how many worksheets you have, you should organize the data in each of them in aforementioned Excel Table. The structure of the tables should be consistent.

If you’re new to this, we suggest you give names to your Excel Tables. This isn’t obligatory but it will help you avoid confusion. Let’s say that you have to prepare a report for the first four months of the year. You could name your tables: January, February, March, and April.

How to Combine Data from Multiple Spreadsheets

When you’ve prepared your Excel Tables, you’re ready to begin. Just follow the step by step guide below to successfully combine data from various worksheets:

1. Click on the Data tab.
   
2. Just below the Data tab, click on New Query then choose From Other Sources in options. You will then be able to import data from various other sources.
   
3. When you’ve entered the From Other Sources section, click on Blank Query.
   
4. That will take you to the Power Query editor.
   
5. There, you need to type this formula in the formula bar: =Excel.CurrentWorkbook()
   
6. Now click on Enter and you’ll be able to see the names of all tables in the workbook.
   
7. There will be two columns, one named Content and the other named Name (with names of your tables). In the Content header cell, you’ll see a double-pointed arrow. Click on it.
   
8. Now select all the columns that you would like to combine. If you want to combine all columns, click on Select All Columns.
   
9. Make sure that the option “Use original column name as prefix” isn’t checked and click OK.
   

All of the data from different worksheets should now be combined into one table. In the last column, you’ll be able to see the names that you’ve given to your Excel Tables. That enables you to know from which Excel Table a certain piece of data has come. It’s a very useful feature, especially if you have to deal with a lot of data.

How to Merge Data from Multiple Spreadsheets

Merging data from multiple spreadsheets isn’t difficult, but before starting make sure that:

1. All worksheets have the same layout (identical headers and identical types of data).
2. There are no blank rows or columns.

You have to do this if you want the process to be successful. After that, simply follow our step by step guide:

1. Create a new worksheet.
   
2. Click on the Data tab and then click on Consolidate.
   
3. In the Function section, choose Sum.
   
4. In the Reference section choose your spreadsheet in order to be able to access your data.
   
5. When you find the data you need, click on Add.
   
6. Continue in the same way for each dataset that you would like to merge.
   
7. Click on OK.
   

Bonus Tip: If you’re going to change data in your worksheets in the future and would like those changes to be reflected in your new, merged worksheet as well, there’s a simple way to do it. Before you click OK, make sure that you check the box “Create links to source data” and the new worksheet will be automatically updated every time you change something.

Wrap Up

There are many amazing things you can do with your data in Excel. It just takes some time to get used to it and discover all of the features. The first time might be a bit confusing, but every subsequent time it will be easier and faster.

We hope that this article was useful. Do you know any other tips and tricks when it comes to combining and merging data in Excel? Let us know in the comments section below.