Consolidate in Excel: Combine Data From Multiple Worksheets

by Peter Anderson Writer

Whenever you are working with a lot of information, you may need to combine all the data from multiple worksheets to a single worksheet. It is not a hard task, but it can take your time, especially when you have not done it earlier.

One of the best methods to process with the help of the Power Query. If you wish to use the power query, then don’t worry as the power query is an inbuilt feature of Microsoft Excel 2016 and also in the current version. If you have an old version, then you need to download and install it.

Before You Start


1.      If you have an old version of Microsoft Excel, then you need to visit the official website of Microsoft.

2.      Move to the Search engine at appearing on the upper right on your screen and type Power Query.

3.      When the Power Query appears on your screen Download it and remembers one thing Power Query correspondence to your Microsoft excel.

4.      When the Power Query downloads in your device, you can see in your upper toolbar in your device.


To combine your data into a single worksheet, you need to organize your data in an Excel table. It does not matter how many spreadsheets you are using, and you need to organize all your information in aforementioned in the Excel table.

If you are doing this for the first time, then we recommend you to give a name to an Excel table. It is not compulsory, but it can help you avoid confusion. For example, if you have prepared full months to report separately and want to merge in a single worksheet. You can rename as month wise like January February soon.

How to Combine Data From Multiple Spreadsheets


After completing your Excel table, you can start merging your data. Here are the steps on how you can merge your multiple worksheet data into a single worksheet:-

1.      Hit on the data tab.

2.      On the left side of your screen, you can see several options for getting your data. Tap on the Form is another source option.

3.      Now you can import your information from several other sources.

4.      After entering in the From another source option, tap on the Blank Query.

5.      This process brings you to the Power query editor.

6.      Here you need to enter a formula in the formula bar as =Excel.CurrentWorkbook()

7.      Now tap on the Enter, and you can see all the tables’ names in the workbook.

8.      Here you can see the two columns, one as name content and second as named Name (names of the table).

9.      In the content column cell, you can see the double-pointer arrow, tap on it.

10.  Now choose all the columns that you wish to combine. If you want to combine all the column, tap on select all columns.

11.  Remember that the option “Use original column name as prefix” not checked and don’t tap on OK.


After this process, your all data will be combined into a single worksheet, in the separate columns you can see the names that you have to make it in the Excel table.

How to Merge Data From Multiple Spreadsheets


Here are the steps on how to combine data from various spreadsheets:-

1.      Here you see all the worksheets have a similar layout.

2.      Here you can find any blank row and columns. You need to make do to process to be successful.


 Here are the steps:-

1.      Create a worksheet.

2.      Tap on the Data tab and then tap on Consolidate.

3.      In the function option, select Sum.

4.      In the reference, option selects your spreadsheet to access your data.

5.      After finding your data tap on Add.

6.      Continue on the same methods for every data that you like to merge in a single worksheet.

7.      Tap OK.


Harry Williams is an inventive person who has been doing intensive research in particular topics and writing blogs and articles on and many other related topics. He is a very knowledgeable person with lots of experience.


Source : Combine Data From Multiple Worksheets



Sponsor Ads

About Peter Anderson Innovator   Writer

19 connections, 1 recommendations, 85 honor points.
Joined APSense since, December 5th, 2019, From Houston, United States.

Created on Mar 5th 2020 09:10. Viewed 213 times.


No comment, be the first to comment.
Please sign in before you comment.