If there’s a valid reason for them to be blank (for example, perhaps you post amortization entries only in December), you can use the Find and Replace dialog to change the blank cells to zero. If a row is missing from one data set, the result cells are left empty to alert you to a potential problem. As you can see, cells F3:H3 and B4:E4 are blank instead of zero. There are some unusual features in the results. You can see that, even though we left the final data set out of the All References box in step 8, Excel now displays that reference in the All References box. Note that I have opened the Consolidate dialog again, displaying it on top of the results. Check the Top Row and Left Column boxes in the lower-left corner of the Consolidate dialog. While clicking the Add button seems like the correct step, the Consolidate will work if the last data set is left in the Reference box.ĩ.
But at this point, it’s your choice if you press the Add button or not. Use the Reference Edit button, then select the third data set. For your last data set, the steps are slightly different. Click the Add button to add this second reference to the All References dialog.Ĩ. Click the Close icon to return to the Consolidate dialog. Select the data for your second worksheet. Click the Reference Edit icon on the right side of the Reference box. While this might seem confusing, Microsoft leaves the old reference in place in case your next data set simply needs to change from Sheet1 to Sheet2.ħ. 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.