Consolodating sheets in excel
To consolidate these three worksheets, follow these steps: 1. Make sure there’s nothing to the right or below the active cell. From the Data tab, select Consolidate to open the dialog. The Function dropdown in the Consolidate dialog defaults to Sum.
You can change this to Count, Average, Max, Min, Product, Count Numbers, Standard Deviation, or Variance. There is a Reference Edit icon on the right side of the Reference box.
Click the icon, navigate to the first worksheet, and select the data, headings, and labels.
Use the Close icon on the right side of the Consolidate: Reference dialog to return to the Consolidate dialog. The address of the first data set is left in the Reference box.
wiki How's Content Management Team carefully monitors the work from our editorial staff to ensure that each article meets our high standards.
The wiki How Tech Team also followed the article's instructions and validated that they work.
In this example, the worksheets have differing numbers of months: four months in Report1, five months in Report2, and three months in Report3. Select the top-left-corner cell where you want the consolidated data to appear.Excel has a built-in tool for consolidating multiple worksheets or multiple workbooks. 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.If you need to total various worksheets that might have differing rows or columns, the Consolidate command uses the labels in the first row and first column to line up the data and combine it onto one worksheet. While this is a limiting factor, there are a lot of data sets that follow this shape.Note that I have opened the Consolidate dialog again, displaying it on top of 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. As you can see, cells F3: H3 and B4: E4 are blank instead of zero.
see screenshot: Note: For selecting same ranges in all checked worksheets, you click the Same range button. Click Next to select calculation from Function section, and check Top row and Left column options. Click Finish, then click Yes or No to save the scenario or not. You can click Add in Combine Worksheets--Step 2 of 3 dialog to add workbooks and ranges to directly consolidate without name range. Check Create links to source data option in Combine Worksheets--Step 3 of 3 dialog to auto update consolidated results as source data changing.