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.

ms excel consolidating worksheets-56

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.

Consolidating by position involves a few easy steps: First, you click on the upper-left cell of the range in which you want Excel to place the summary information (we'll call this range the range). However, you can choose from a variety of other statistics (Count, Average, Max, Min, Product, and so forth) if you want.

Since the source ranges are the same size, you don't have to specifically select the entire destination range. command from the Data menu to open the dialog box shown in Figure B. Once you've selected the statistic you want Excel to calculate, you should specify the source ranges.

Although you can type the references, the easiest way to specify the source ranges is by selecting them.

To select the first source range, activate its workbook, click the tab for the appropriate sheet, highlight the range by dragging over it, and click the Add button.

She could then use pivot tables and data filtering to analyze the data The Consolidation Assistant has several features that allow you to conveniently view your data. Another sets the same cell on all selected sheets to the upper left corner.

And at the same time sets the zoom to the same setting on all the worksheets.

The customer first combined all the survey sheets using the Consolidation Assistant into one workbook, with each sheet renamed from "survey" to the manager names.

Next, he used the extract feature of the Consolidation Assistant to extract values from the same cell on each sheet and place on a new worksheet, which became his database worksheet.

The ranges can exist in the same worksheet, in different sheets in the same workbook, or in different workbooks.