Excel online training course

excel lesson 19, Consolidate

Consolidate information

We can connect information from different worksheets with each other to make a sum, difference, or another calculation in a independant worksheet.
For example when we have a workbook with four worksheets which have the quarterly turnover figures for each state, we can make a fifth worksheet which contains the total of all quarterly turnover figures.
We can connect information from a maximum of 255 sources into one worksheet.

When we want to connect different worksheets with each other, we first select the cell in the new worksheet where we want to make the connection.
Then we need to select the different ranges in the different worksheets.
Click on "Data" and "Consolidate" in the menubar.

The "Consolidate" window will open.
In this window you first choose the function you would like to perform (in this case the sum).
Then we select the information from our different worksheets which we want to connect.
We do this by clicking on the button on the right of the "Reference" field and selecting the range in the worksheet.

When you selected a range you click on the "Add" button.
For every range in every worksheet you would like to connect click on the button to right right of the "Reference" field to select it.
If you would like to have labels in your new worksheet you need to select the "Top Row" and/or "Left column" box.
If you would like the new worksheet to be updated when changes are being made in the other worksheets you need to check the "Create links to source data" box.

When Excel connects the different worksheets to each other it will add different rows and columns when you check the "Create links to source data" field.
The inserted columns or rows contain a reference to the original information.
These inserted columns or rows and connected and hidden so the new worksheet will look like an ordinairy worksheet.
You can also click on the "-" and "+" character to hide or show the details.

Type of connection

We can connect Worksheets or Workbooks in two way in Excel, to category or to position.
When we connect worksheets through category, column and row headers will become the categories where Excel will base itself on to process the information in the new worksheet.
For example, when we connect different worksheets with the same titles:
1st quarter, 2nd quarter, 3rd quarter and 4th quarter.
This will give Excel the possibility to use the titles and thus connect according to categories.
Even if they are not in the same order in the different worksheets.
When we connect according to category, its also neccesairy to select the column and header titles in the range of each worksheet.

We can also connect worksheets according to position.
In that case it doesn't matter to Excel what the titles are.
The only thing Excel will care about is where the information is located.
All information in all worksheets need to be in the exact same position.
When you are using a position connection like this, Excel will connect the information according to the cell addresses in the worksheet.