This description assumes you have data ranges (blocks) in a workbook that have column and row labels in the first column and first row of each range. You want to consolidate the data in those ranges. It is best practice to consolidate into a range on a new worksheet, rather than into a range on a sheet that has other data. But it is not required to consolidate to a new worksheet.
- Select the upper-left corner of the range where you want the consolidation to appear.
- Choose Data → Consolidate.
- Select the consolidation function you want to use (e.g. Sum), in the Function drop-down list.
- In the Reference box, enter your reference and click Add.
- You can type the reference as a named reference, then click Add. This is the best practice.
- Alternatively, click in the Reference box, then (with the dialog open), select the sheet and the range for the reference. While you are selecting your reference the dialog will collapse, then will expand when you are finished selecting your reference. Click Add to add the reference.
- Repeat the step above for each additional range that you want to include in the consolidation.
- Select the checkboxes, Top row and Left column.
- Optionally, you can choose to Create links to source data. See the discussion below for more information on this option.
- Click OK.
The consolidated data then appears.
If you have not chosen to create links to source data, any changes to the source data will not appear automatically. Instead, you simply re-run the consolidation by clicking in the upper-left corner of the consolidation range and choosing Data ? Consolidate again. Excel will remember the settings of the consolidation, so you won't need to repeat the rest of the steps. Just click OK.
If you have chosen to create links to source data, Excel does this by creating extra rows in the consolidation range that are linked from source ranges, then grouping those extra rows to hide them. This strikes us as being a big kludgy, but it achieves the effect of creating a 'linked' consolidation so that changes to source data automatically appear in the consolidation. However, if you add rows or columns to source ranges, those may not appear (and probably won't)... another reason why we suggest:
The recommended practice is not to choose Create links to source data. Instead, just re-run the consolidation to update the data.
It is also a best practice to use named ranges as the source for the consolidation--preferably named ranges that are dynamic--so that all inserted columns and rows are included automatically in the consolidation.
