Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Combine Data from Multiple Excel Worksheets into One?
There are situations when the data that you require is scattered across numerous sheets in Microsoft Excel, or even multiple files. How wonderful it would be if you could collect all this data in a single document?
When you are in a hurry, you can save time by simply copying and pasting the necessary cells onto the same sheet. However, the amount of time and effort required to complete this task may be quite substantial, depending on the data that you are analyzing. Instead, you could think about some of the more intelligent approaches to complete the same task.
In this tutorial, we will show you some ways that you can use to merge the data scattered across different sheets and files in Excel into one. Using these methods, you could very well skip some of the repetitive tasks that is required.
Using the Consolidate Command
You can use the "Consolidate" command to assist you pull data together onto one page if you get information that is spread across numerous sheets or workbooks and you want to summarize it.
For the purpose of consolidation according to position to work, the data on each source sheet must be organized in the form of a list, and there can be no blank rows or columns in the list. To consolidate, follow the below given steps.
Step 1 − Open up each source sheet, and check that your data is formatted in the same way throughout all of the sheets. Here we have taken two sheets for the purpose of consolidation. We have employee name and their salary in Sheet1 and employee name and bonus in Sheet2. See the below given image for reference.
Let's assume Sheet1 contains the following data −
And Sheet2 contains the following data −
Step 2 − To combine the data from Sheet1 and Sheet2 into Sheet3, select cell "A1" of Sheet3.
Step 3 − Click the "Data" tab of the Sheet3, and then click on Consolidate option present under the Data Tools group. See the following image for reference.
Step 4 − In the "Consolidate" dialog box, chose any function as per your requirement, here in this case we will select Sum function to combine Employee salary column of Sheet1 with Employee bonus column of Sheet2.
Step 5 − Write the source sheet name and range of cells which contains the data to be combined into the destination sheet (In this case Sheet3) in the Reference box as shown in the image.
Step 6 − Then add the reference to All reference list by clicking the Add button. Similarly, you can add references for Sheet2.
Step 7 − Check the "Left column" box and then click OK. −
After that, we will get the combined data of salary column and bonus column in this sheet (Sheet3) as shown in below image.
Conclusion
In this tutorial, you learnt how to combine the data from multiple worksheets into one sheet using the "consolidate" command and the different steps to combine the data into one worksheet.
368 Views
