how to consolidate data in excel from different worksheets

#HOW TO CONSOLIDATE DATA IN EXCEL FROM DIFFERENT WORKSHEETS HOW TO#
#HOW TO CONSOLIDATE DATA IN EXCEL FROM DIFFERENT WORKSHEETS UPDATE#

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

#HOW TO CONSOLIDATE DATA IN EXCEL FROM DIFFERENT WORKSHEETS UPDATE#

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.

#HOW TO CONSOLIDATE DATA IN EXCEL FROM DIFFERENT WORKSHEETS HOW TO#

How to Consolidate Data while Keeping Links to the Original Data 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. Note: You do NOT have to have your data sorted in the same order like I did in this example. We now have all data from the Data worksheets consolidated onto the Consolidated Data worksheet. Since our data is identified with labels in the left column (the part numbers), we put a check next to Left column: 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. This is where you tell Excel what it will use to identify the separate pieces of data to consolidate. After adding the last set of data, our Consolidate window looks like this:

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).

Once you select the data, click the Add button in the small window and it should now look like this:.

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.

Click in the Reference input box and then navigate to the worksheet that contains the data to consolidate.

In the drop-down menu under where it says Function I will select Sum.Īs you can see, there are many options, so choose the one that applies to your situation. Since I am combining sales data from different stores, I want to add the data together.

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.

Go to the Data tab and click the Consolidate button.

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.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.We will combine data from two source-data worksheets into one consolidated worksheet. Notes Steps to Combine and Consolidate Data in Excel Steps to Combine and Consolidate Data in Excel This method has Excel do all the work for you so that you don't have to insert any formulas or functions yourself. Simple method to combine data from multiple worksheets into one worksheet. The easiest way to combine and consolidate data in Excel.

How to consolidate data in excel from different worksheets