Ms excel consolidating worksheet text data
If the ranges are in different workbooks, you must use book, sheet, and cell references.If the source ranges are named, you can simply specify the ranges' names.Once the All References list box contains the addresses of all the source ranges, you should make sure that the Top Row and Left Column check boxes aren't selected.(You use those boxes when you want to consolidate by category rather than by position.) The Create Links To Source Data check box should also be deselected.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.
(By default, this box will contain the addresses of the ranges you specified the last time you used the Consolidate... To remove those addresses, select them and then click the Delete button.) The form of the references depends on the locations of the 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.Enabling this option tells Excel to copy all the data from the source ranges into the destination range in addition to entering the summary statistics.(We'll discuss that check box more in a moment.) At this point, click OK.
For example, you can use the addition formula to find the average of those values.