Excel has a useful tool for
consolidating the data within several lists. The lists may be in different worksheets within the same workbook or in
different workbooks. Each list must
be constructed with the same row and column headings so that the data can be
compared and consolidated.
For an example, the spreadsheet
below shows the monthly rainfall in 1995:
The data for other years is laid out
in other sheets in a similar fashion with exactly the same row and column
headings. To consolidate the data,
we select the range A2:B14 in each sheet and Excel will summarise the
When you have set up all the data
sheets you can use a blank worksheet to perform the consolidation. Click on the Data drop-down menu and choose Consolidation,
a window appears as below.
Choose the Function
you want to use for the consolidation and then click in the Reference box
and click on the red icon at the right hand end of the box to highlight the
first range to be consolidated as shown above. When you have highlighted the range, click the Add button to add
it to the list at the bottom of the window. You should now be able to simply click on each sheet tab to change the
reference (as the cells are in the same position the correct range should
automatically be highlighted) and click the Add button to add each range
to the consolidation. Repeat this
process until you have selected all the data to be consolidated. Now put ticks in the boxes at the bottom of the window to use the row and
column headings as titles and put a tick in the ‘Create links to source
data’ box if you would like Excel to outline your data so you can see the
individual figures making up the overall consolidation. Now click OK and the consolidated data will appear in the
worksheet as shown below.
As links were created
to the data, an outline bar appears on the left hand side of the screen, if you
cannot see the outline bar press CTRL and 8 together to display
it. You can see the individual
figures relating to the summary for a month by clicking on the + sign beside
that month. Alternatively, you can
expand out all months by clicking the number 2 at the top of the outline or
collapse all months by clicking on the number 1.
you want to totally clear the worksheet containing the consolidated data you can
remove the Outline by clicking on the Data drop-down menu and choosing Group
and Outline then Clear Outline. You will then need to delete all the data from the worksheet, the easiest
way to do this is to click your right mouse button on the blank top left corner
heading cell (between A and 1) and choose Clear Contents from the menu
that appears as below.
You can consolidate
data from sheets in other workbooks using this technique, simply click on the
Browse button in the consolidation window to find the workbook containing your
data. The column containing the word Rainfall in the example above is
giving the name of our workbook, if each year's data was in a different workbook
we would see the name of that workbook in this column.
Click here to
return to the Excel index.