Create a consolidated report

The data consolidation makes it easier to consolidate data scattered in many ranges and to create a consolidated account or report.

Steps

To consolidate data that are scattered in multiple sheets

We will consolidate data scattered in two sheets (Paris and London) and display the number of units sold by product and total sales in the consolidated sheet (Total).

  1. Select Total sheet on which the results will be displayed. Select the A2:C6 area, and click Data > Consolidate.

  2. In the Consolidate dialog box, select Sum from Function list.
  3. In Reference, click Select Area  button. Select the A2:C6 area in the Paris sheet which contains the source data area, and click Finish Selecting Area  button.
  4. In the Consolidate dialog box, ensure that the proper reference area is entered as Reference. To add the reference area to All Reference Area list, click Add.
  5. In Reference, click Select Area  button. Select the A2:C6 area in the London sheet which contains the source data area, and click Finish Selecting Area  button.
  6. Ensure that the proper reference area is entered as Reference in the Consolidate dialog box. To add the reference area to the All Reference Area list, click Add.
  7. From Usage Label list, select the check boxes for First Row and Left Column and click OK.
  8. The consolidated data will be displayed in the selected area in the result sheet, Total.

Was this article helpful?
0 out of 0 found this helpful