Guides

Related Tutorials

Excel > Excel 2007 Intermediate > Working with Multiple Workbooks

Consolidating Data

The Consolidation tool in Excel is used to summarise data from multiple worksheets into a single worksheet. For example, the expenses from different offices can be totaled on another worksheet.

Data can be consolidated in two ways:

  • Consolidate by position: Used when the data is arranged in the same order and location on all worksheets.
  • Consolidate by category The row and column label is used to match the data.

Consolidating by position or category

Before consolidating, ensure that the data is arranged in labelled rows and columns without blank rows or columns on seperate worksheets.

  1. Select the cell in the upper left corner of where you want the consolidated data to appear
  2. Click the Consolidate button in the Data Tools group of the Data tab

    3. The Consolidate dialogue box appears

    Consolidate data

  3. Click the Function list arrow and select the function you want to use to summarise the data
  4. Click in the Reference field, click the worksheet tab, and then select the first range of data to consolidate

    5. If the range of data is located on a different workbook, click the Browse button and locate the file

  5. Click the Add button
  6. Repeat steps 4 and 5 to select all the ranges you want to consolidate
  7. To copy the labels to the consolidated worksheet, click the Top row or Left column options

    8. This refers to where the labels are located in the source ranges

    Consolidation references

  8. Click the Create links to source data checkbox if you want the consolidation to automatically update, whenever the source ranges change.
  9. Click Ok

The values from the source ranges are summarised using the chosen function on the consolidation sheet

Consolidating by formula

Using a formula to consolidate data is a more powerful method as you do not need to worry about any prescribed layout for the consolidating data, or ensuring they have the same labels.

  1. Select the cell where you want to put the consolidated data
  2. Type the formula to reference and summarise the source data

    3. The formula below sums the total cell on three different cells from three different worksheets.

    =SUM(London!B18,Bristol!B17,Manchester!B16)

    The consolidation will automatically update when the source data changes.

Follow us on

Facebook  Twitter  You Tube