Consolidating multiple workbooks

13-Sep-2019 01:37

It could also be in one or more different workbooks altogether.

We have sales numbers for the first three months of the year.

Every worksheet follows the same standard format, with row 1 being the header containing variable names and data being listed in columns; except for the very first worksheet, the header of which comprises of the first 2 rows, with variable names storend in row 1 and description in row 2.

It is as simple as adding a new worksheet and selecting a cell, say ‘B2’.

Like before, click on ‘Consolidate’ in the ‘Data Tools’ selection of the ‘Data’ tab. Now you need to select the range that includes the column headers and row labels.

The next order of business is to add our references for our consolidated table. Once there, we will go to our worksheet that contains the data for our North division.

So, you don’t need to select the data range for subsequent reference selections. At this point, it should look like the following figure: This is the simple method for consolidating tables of data.

Every worksheet follows the same standard format, with row 1 being the header containing variable names and data being listed in columns; except for the very first worksheet, the header of which comprises of the first 2 rows, with variable names storend in row 1 and description in row 2.

It is as simple as adding a new worksheet and selecting a cell, say ‘B2’.

Like before, click on ‘Consolidate’ in the ‘Data Tools’ selection of the ‘Data’ tab. Now you need to select the range that includes the column headers and row labels.

The next order of business is to add our references for our consolidated table. Once there, we will go to our worksheet that contains the data for our North division.

So, you don’t need to select the data range for subsequent reference selections. At this point, it should look like the following figure: This is the simple method for consolidating tables of data.

It automatically selects the same range so long as each subsequent worksheet has the same layout (i.e., the table begins at the same cell as ours at ‘B2’). In this, we got a single table that shows the sum for all tables being consolidated.