Then we type in our new range name

Consolidating workbooks in excel

These are the actual figures for each product. This master worksheet might also contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise. Consolidating data by category is similar to creating a PivotTable.

If you want Excel to update your consolidation table automatically when the source data changes, simply check the Create links to source data box. In this, we got a single table that shows the sum for all tables being consolidated. This new worksheet is completely blank and without formatting. Excel will do this for you.

If you want Excel to

Use this method to consolidate data from a series of worksheets that have different layouts but have the same data labels. The next step to merge multiple Excel files is a bit different than before.

In this we got a

Now that we have named ranges for our source files, we can combine Excel files much more efficiently. This is the simple method for consolidating tables of data. Each of four regions, North, South, East, and West have sales figures for all the products. One important thing to note at this point.