After you click Add, the original reference appears in both All References and in the Reference box. Click the Add button to move the first reference to the All References box. The address of the first data set is left in the Reference box. Use the Close icon on the right side of the Consolidate: Reference dialog to return to the Consolidate dialog.Ħ. Click the icon, navigate to the first worksheet, and select the data, headings, and labels.
There is a Reference Edit icon on the right side of the Reference box. You can change this to Count, Average, Max, Min, Product, Count Numbers, Standard Deviation, or Variance.ĥ. The Function dropdown in the Consolidate dialog defaults to Sum. From the Data tab, select Consolidate to open the dialog.Ĥ.
Make sure there’s nothing to the right or below the active cell.ģ. Select the top-left-corner cell where you want the consolidated data to appear. Insert a blank worksheet in your workbook.Ģ. To consolidate these three worksheets, follow these steps:ġ. In this example, the worksheets have differing numbers of months: four months in Report1, five months in Report2, and three months in Report3.
Some items might be missing from any worksheet, meaning that each account could appear on a different row. In the figure below, three worksheets in a workbook show a chart of accounts in column A. While this is a limiting factor, there are a lot of data sets that follow this shape.Ĭonsolidate easily handles differing numbers of rows or columns. Found on the Data tab, it has a peculiar requirement: It only works if your data has exactly one column of labels and one row of headings.
0 kommentar(er)
