Articles
Consolidate in Excel: Combine Data From Multiple Worksheets
by Peter Anderson WriterWhenever 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 webroot.com/safe 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 
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 352 times.
