Combine Data from Multiple Worksheets in Excel

Simple method to combine data from multiple worksheets into one worksheet.  This method has Excel do all the work for you so that you don't have to insert any formulas or functions yourself.

Sections:

Steps to Combine and Consolidate Data in Excel

How to Consolidate Data while Keeping Links to the Original Data

Notes

Steps to Combine and Consolidate Data in Excel

We will combine data from two source-data worksheets into one consolidated worksheet.

1. Make sure that your data worksheets have their data setup in a similar way to each other and that there is a header on top of the data or to the left of the data.  Here is my data.
   
2. Go to the worksheet where you want the consolidated data to be and select the upper-left most cell where you want the consolidated data to start.
   
3. Go to the Data tab and click the Consolidate button.
   
4. A small window will appear and the first thing you need to do is to figure out what you want to do with the data that you are combining or consolidating.
   Since I am combining sales data from different stores, I want to add the data together.
   In the drop-down menu under where it says Function I will select Sum.
   As you can see, there are many options, so choose the one that applies to your situation.
   
5. Click in the Reference input box and then navigate to the worksheet that contains the data to consolidate.  From there, select the data to consolidate, INCLUDING the headers that identify each piece of data, for me this is the far left column of the data.
   
6. Once you select the data, click the Add button in the small window and it should now look like this:
   
7. Repeat Steps 5 and 6 for as many data sets as you want to consolidate (sometimes Excel gets weird when selecting a new range so just delete any current reference in the Reference input box before starting Step 5 again). After adding the last set of data, our Consolidate window looks like this:
   
8. Look to the Use labels in section.  This is where you tell Excel what it will use to identify the separate pieces of data to consolidate.  This allows Excel to match up the data from each worksheet and put it together and this is why we also selected the "Part" column in our example for the range references in Step 5.
   Since our data is identified with labels in the left column (the part numbers), we put a check next to Left column:
   
9. Hit OK and that's it!
   

We now have all data from the Data worksheets consolidated onto the Consolidated Data worksheet.

Note: You do NOT have to have your data sorted in the same order like I did in this example.  You also do NOT have to have the same data points on each tab; notice that asc-10 does not exist in Data 1 but it does in Data 2.

How to Consolidate Data while Keeping Links to the Original Data

This allows you to consolidate data onto a single worksheet while allowing the consolidated data to update when you change something on one of the source data worksheets.

1. Follow all steps from the previous example above up to Step 8.
2. On Step 8, put a check mark next to Left column and ALSO next to Create links to source data
3. Once you hit OK it will look like this:
   

When the data is changed on the Data 1 or Data 2 worksheet, the Consolidated Data sheet will now be updated as well.

If you click the plus signs to the left of the row numbers you can see the links to the source data:

Notes

Consolidating data may seem difficult or scary, if the data is important, but trust me when I say it is a basic task and is very easy to do. Test out the consolidate feature on simple data at first until you feel comfortable using it and then move up to important data.  Once you do this a few times, you will be able to consolidate thousands of rows of data in under a minute and without typing a single formula or function.

Download the worksheet used in this tutorial so you can see how it works and try it out yourself.