How to Merge Excel Sheets and Consolidate Data

merge excel sheets -magoosh

Data accounting is an important part of a job in every arena, be it maintaining employee details or the data about a certain system. In earlier days, all these accounting jobs were done manually in what we know as registers. However, as the era of computers dawned upon the human civilization, this manual work soon was transformed into a more sophisticated work. As Microsoft introduced the human race to their Excel application, the job of keeping a periodic data records became easier and lucid.

Microsoft Excel worksheets are nothing but a collection of cells in the form of rows and columns where one can keep up the records of accomplishment of various functions, thereby maintaining a data sheet. Worksheets can be many in number within a particular workbook. Initially, there are three worksheets but you can add more and that is completely based on whether you need them or not.

What if you need to merge all the data of all the worksheets into a single one?

This method is known as data consolidation. Data consolidation is the procedure by which various data from many sources are integrated into a single site. You can merge Excel sheets and consolidate all the data in such a manner that multiple source data will be correctly and orderly placed in a single destination.

Now, what are the steps that you need to follow in order to merge the sheets?

We have discussed here the simplest of all methods and hence the process will become quite easier for you.

Steps to merge excel sheets

  1. The first step that you will need to follow is to format all the excel worksheets in accordance with certain rules. Firstly, the layouts of all the ranges will be same and nothing should be written on the worksheet where you will put all the data together.
  2. Secondly, you have to make sure that no blank rows or columns are present in the ranges of the worksheets that are to be consolidated.
  3. Now, you have to select the cell on the upper left-hand side of the master worksheet, that is, the sheet where you want your data to appear.
  4. After you have ensured that enough space is there in the destination worksheet, you have to follow the steps in order:
    Data tab > data tools > consolidate
  5. Now, move to the function box and enter the work you want the Excel compiler to perform. However, in case you forgot to select, then by default, Excel will select the function ‘SUM’.
  6. Then is the step where you will need to the select the data from the worksheets you want. Move to the dialog box of the consolidation and click on the ADD button. You have to repeat this process for all the ranges you have selected for the integration process.
  7. Once you press the OK button, Excel will generate a conglomerated data from multiple worksheets into a single one, therefore fulfilling the purpose of merging the worksheet.

Happy working with Excel!

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!