< Back
Excel: Combine different data sets using Consolidate.
Post
You can use the Consolidate feature in excel to combine different data sets that you have into a single set.
Follow these steps to consolidate several worksheets/data sets into a singe list:
Follow these steps to consolidate several worksheets/data sets into a singe list:
- Ensure that the data that you need to consolidate has labels on the left column or on the top row. There must be no blank rows or columns anywhere in the list.
The data can be in the same sheet or on different worksheets.
Ensure that each range has the same layout.
- In the master worksheet, click and select the upper-left cell of the area where you want the consolidated data to appear.
Note: To avoid overwriting existing data in the master worksheet, ensure that you leave enough cells to the right and below this cell for the consolidated data. - Here is an example of two data sets in the same sheet.
- Click Data>Consolidate (in the Data Tools group).
- In the Function box, click the summary function that you want Excel to use to consolidate the data. The default function is SUM.
- Next,click in the Reference box and select the data in the worksheet.Then click on Add.
- Select the next range that you need to add to consolidate and click on Add. Continue adding all the ranges that you need to consolidate.
If a worksheet containing data that you need to consolidate is in another workbook, it is best to keep that worksheet open so you can select the data directly . Alternately, click Browse to locate that workbook. After locating and clicking OK, Excel will enter the file path in the Reference box . Now add an exclamation point to that path. You would need to manually enter the range to select the data.
- If you want Excel to update your consolidation table automatically when the source data changes, simply check the Create links to source data box. If this box remains unchecked, you can update the consolidation manually.
Notes:You cannot create links when source and destination areas are on the same sheet. - Now check the boxes where you have your data labels. You can use the Left column or Top row as labels. You can also choose both as labels.
- Click OK, and Excel will generate the consolidation for you.
- Any labels that don't match up with labels in the other source areas result in separate rows or columns in the consolidation.
Ensure that any categories that you don't want to consolidate have unique labels that appear in only one source range. - If you need to change the range of data or replace a range, click the range in the Consolidate popup and update it using the steps above. This will create a new range reference, so you???ll need to delete the previous one before you consolidate again. Simply choose the old reference and press the Delete key.