EXCELData ConsolidationExcel 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 consolidated data. 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.
If 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. |
