Summarizing Data with Excel's Consolidate...Command

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

='1994 Sales'!B2+'1995 Sales'!C3+'1996 Sales'!D4

to sum the values in cell B2 of Sheet1, cell C3 of Sheet2, and cell D4 of Sheet3. Or you can use the Average function

=Average('1994 Sales'!B2,'1995 Sales'!C3,'1996 Sales'!D4)

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. 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 PivotTable 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.

Fortunately, there's another alternative: the Consolidate... command. This command—located on the Data menu—is quite versatile. 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... command—consolidating by position. In a future article, we'll demonstrate a more complex use of the Consolidate... command—consolidating by category.

On This Page

Consolidating by position
Creating links to the source data
Specifying multiple-cell destination ranges
Conclusion

Consolidating by position

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. For example, the ranges shown in Figure A are on different worksheets in the same workbook.

Figure A: We'll consolidate data from these three named worksheets to a fourth sheet in this 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.

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 destination range). Since the source ranges are the same size, you don't have to specifically select the entire destination range. Next, you choose the Consolidate... command from the Data menu to open the dialog box shown in Figure B. In the Function list box, choose the summary function you want to use. Then, specify the source ranges and click OK.

Figure B: Excel will display this dialog box when you issue the Consolidate... command.

The Function list box offers a number of options. In most cases, you'll choose Sum. However, you can choose from a variety of other statistics (Count, Average, Max, Min, Product, and so forth) if you want. Once you've selected the statistic you want Excel to calculate, you should specify the source ranges.

You specify individual source ranges in the Reference text box and then click the Add button to place the reference in the All References list box. (By default, this box will contain the addresses of the ranges you specified the last time you used the Consolidate... command. 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. If the ranges are in the same worksheet, you can simply specify the cell addresses. If the ranges are in the same workbook but in different worksheets, you must use sheet and cell references. 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.

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. When you do, Excel will add that range's address to the All References text box.

Once you've specified the first source range, you should specify the others in the same way—by highlighting them and clicking Add. Don't bother including a source range's category labels when you specify the range—Excel won't include the labels in the destination. If you want to include the labels in the destination range, you'll have to copy them or enter them manually.

When you move to a different source worksheet, Excel will, by default, "suggest" the same range that you highlighted in the previous worksheet. Therefore, if the data in each source range occupies the same cells, you don't have to highlight each range—you can simply click Add after activating the appropriate worksheet.

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. 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. When you do, Excel will summarize your data using the function you specified. The resulting destination will be the same size and shape as the source ranges. For example, the upper-left cell of the destination range will contain the statistic for the values in the upper-left cell of the source ranges, the cell in the second row and first column of the destination range will contain the statistic for the values in the second row and first column of the source ranges, and so forth.

To demonstrate this technique, let's use it to consolidate the values in the worksheets we showed you in Figure A. Specifically, we'll consolidate into cells B2:F6 in Sheet4 the data in cells B2:F6 in the 1994 Sales worksheet with the values in cells C3:G7 of the 1995 Sales worksheet and cells D4:H8 in the 1996 Sales worksheet. To begin, select Sheet4's tab and click on cell B2. Then, pull down the Data menu and choose the Consolidate... command. Select Sum from the Function dropdown list and ensure that the three options at the bottom of the dialog box (Top Row, Left Column, and Create Links To Source Data) are unchecked.

Next, click the Reference text box, click the 1994 Sales tab, highlight cells B2:F6, and click Add. Then, click the 1995 Sales tab, highlight cells C3:G7, and click Add again. After that, click the 1996 Sales tab, highlight cells D4:H8, and click Add a third time. At this point, the Consolidate dialog box should look like the one shown in Figure C.

Figure C: If you click OK while the Consolidate dialog box looks like this, Excel will produce the results shown in Figure D.

When you click the OK button, Excel will produce the results shown in Figure D.

Figure D: Cells B2:F6 of this worksheet contain the sums of the values in the corresponding cells in the worksheets shown in Figure A.

As you can see, Excel entered the values in cells B2:F6 of Sheet4. For example, the value in cell B2 (573+900+837=2,310) is the sum of the values in cells 1994 Sales!B2, 1995 Sales!C3, and 1996 Sales!D4—the upper-left cell in each of the three source ranges we specified.

Creating links to the source data

In the previous example, Excel entered values—not formulas that return those values—in the cells of the destination range. For that reason, the results of the Consolidate... command aren't dynamic. That is, the calculated statistics won't change if you change one or more of the values in any of the source ranges. To make Excel update the result of a standard consolidation, you have to reissue the Consolidate... command.

However, you can make Excel enter the actual summary functions in the destination range instead of the results of the functions. To do this, you click the Create Links To Source Data check box after selecting a function and specifying the source ranges. But checking this box doesn't produce quite the results you might expect. Instead of entering functions that refer directly to the cells of the source ranges, Excel enters formulas that refer to each cell of every source range individually, then enters functions that refer to those formulas and, using outlining techniques, hides the rows containing the referenced cells.

To demonstrate this effect, let's duplicate the consolidation we just performed, but this time, we'll check the Create Links To Source Data box. To do this, first click cell A1 of Sheet5. Then, reissue the Consolidate... command and specify cells 1994 Sales!B2:F6, 1995 Sales!C3:G7, and 1996 Sales!D4:H8 as the source ranges. (Because you're working in a different worksheet, you need to specify those ranges again—Excel won't automatically suggest the ones you used in Sheet4.) Then, click the Create Links To Source Data check box. Finally, click OK.

Figure E shows the result.

Figure E: When you choose the Create Links To Source Data option, Excel displays in outline view the results of the functions that refer to the source ranges' formulas.

As you can see, rows 1-3, 5-7, 9-11, 13-15, and 17-19 are hidden; cells A4:E4, A8:E8, A12:E12, A16:E16, and A20:E20 contain SUM( ) functions that refer to the cells in those hidden rows. For example, cells A4:E4 contain the functions

A4: =SUM(A1:A3)

B4: =SUM(B1:B3)

C4: =SUM(C1:C3)

D4: =SUM(D1:D3)

E4: =SUM(E1:E3)

These functions return the same values Excel entered in cells B2:F6 of the worksheet shown in Figure D. To understand why, reveal the hidden rows by clicking the five + buttons. Figure F shows the result of clicking the first + button.

Figure F: When you reveal the hidden rows in outline view, you see that the Create Links To Source Data option also entered the source ranges' formulas.

As you can see, cell A1 contains the formula

A1: ='1994 Sales'!$B$2

which returns the value from cell B2 of Sheet1—the upper-left cell of the first source range. Cells A2 and A3 contain the functions

A2: ='1995 Sales'!$C$3

and

A3: ='1996 Sales'!$D$4

Therefore, the function in cell A4 returns the sum of the values in those three cells—the upper-left cell of each of the three source ranges.

Specifying multiple-cell destination ranges

Although you'll typically specify only a single cell as the destination range, you can highlight a multiple-cell range. If the range is the same size as (or larger than) the source ranges, the result will be the same as it would be if you'd selected only a single cell. If you select a range smaller than the source ranges, however, Excel will perform an incomplete consolidation.

If you select a destination range that includes multiple columns in a single row, Excel will consolidate the entries from all the rows of the source ranges—but only as many columns as you selected. For example, Figure G shows the result of selecting cells A1:B1 of Sheet6, issuing the Consolidate... command, specifying the same source ranges we used before (as shown in Figure C), and clicking OK.

Figure G: If you select a single-row, multiple-column range, Excel will consolidate the entries from all the rows of the source ranges—but only as many columns as you selected.

As you can see, Excel entered the sum of the values in each of the five source rows but only the sums of the values in columns A and B. (These are the same values in the first two columns shown in Figure D.)

If you select a range that includes a single column in multiple rows, Excel will consolidate the entries from all the columns of the source ranges—but only as many rows as you selected. For example, Figure H shows the result of selecting cells A1:A2 of Sheet7, issuing the Consolidate... command, specifying the same source ranges as before, and clicking OK. As you can see, Excel entered the sum of the values in each of the five columns but only the sums of the values in rows 1 and 2.

Figure H: If you select a single-column, multiple-row range, Excel will consolidate the entries from all the columns of the source ranges—but only as many rows as you selected.

If your multiple-column, multiple-row destination range has fewer columns and rows than the source range, Excel will consolidate the values from only those portions of the source ranges that correspond to the destination range. For example, Figure I shows the result of selecting cells A1:B2 of Sheet8, issuing the Consolidate... command, specifying the source ranges, and clicking OK. As you can see, Excel filled only the cells you highlighted.

Figure I: If you select a multiple-column, multiple-row range that's smaller than the source ranges, Excel will consolidate the values from only a portion of those ranges.

Conclusion

The Consolidate... command is a useful alternative to linking formulas for consolidating values from similar ranges in different worksheets. In this article, we demonstrated the simplest way to use this command—consolidating by position. In a future issue, we'll show you how to use this command to consolidate data by category.

The article entitled "Summarizing Data with Excel's Consolidate...Command" was originally published in Inside Microsoft Office 95, March 1997. Copyright © 1997, The Cobb Group, 9420 Bunson Parkway, Louisville, KY 40220. All rights reserved. For subscription information, call the Cobb Group at 1-800-223-8720.

We at Microsoft Corporation hope that the information in this work is valuable to you. Your use of the information contained in this work, however, is at your sole risk. All information in this work is provided "as is," without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement , and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Microsoft Corporation. Microsoft Corporation shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.