By consolidating the
For example, the ranges shown in Figure A are on different worksheets in the same workbook.As you can see, the data appears in the same order in ranges of the same size; however, the ranges begin in different cells: A1 in the 1994 Sales sheet, B2 in the 1995 Sales sheet, and C3 in the 1996 Sales sheet.
However, linking formulas may consume more memory than you want—especially when you're dealing with several large ranges of data.Another way to summarize and manipulate data is by creating an Excel pivot table.Using the Pivot Table Wizard makes this task fairly easy, but a pivot table might be overkill if all you want is a simple summary of data from multiple worksheets. This command—located on the Data menu—is quite versatile.Fortunately, there's another alternative: the Consolidate... You typically use it to perform summary calculations on values in corresponding cells in various source ranges.That is, you use it to count the values in multiple ranges or to find the sum, product, minimum, maximum, variance, standard deviation, or average of the values in a group of ranges.In this article, we'll show you the simplest way to use the Consolidate... In a future article, we'll demonstrate a more complex use of the Consolidate... Consolidating by position Creating links to the source data Specifying multiple-cell destination ranges Conclusion You can consolidate by position if all the source ranges (the ranges containing the data you want to consolidate) are the same size and the data is arranged in the same order.
The ranges can exist in the same worksheet, in different sheets in the same workbook, or in different workbooks.
Before you begin, it's a good idea to open the workbook(s) containing the ranges you want to consolidate.
Inside Microsoft Office 95 A Publication of The Cobb Group Published March 1997 Use this handy command on multiple ranges to calculate sums, averages, products, minimum and maximum values, and other statistical summaries.
Excel offers a number of ways to summarize information in different worksheets and workbooks.
For example, you can use the addition formula to find the average of those values.
Summarizing information by using formulas is handy because you can update the summary calculation simply by recalculating the worksheet.