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