Part IV: Managing and Analyzing Data
The following is a description of the controls in this dialog box:
Function drop-down list Specify the type of consolidation. Sum is the most commonly
used consolidation function, but you can also select from 10 other options.
Reference box Specify a range from a source file that you want to consolidate. You can
enter the range reference manually or use any standard pointing technique (if the work-
book is open). Named ranges are also acceptable. After you enter the range in this box,
click Add to add it to the All References list. If you consolidate by position, don’t include
labels in the range; if you consolidate by category, do include labels in the range.
All References list box This contains the list of references that you’ve added with the
Add button.
Use Labels In check boxes Use this to instruct Excel to perform the consolidation by
examining the labels in the top row, the left column, or both positions. Use these options
when you consolidate by category.
Create Links to Source Data check box When you select this option, Excel adds summary
formulas for each label and creates an outline. If you don’t select this option, the consoli-
dation doesn’t use formulas, and an outline isn’t created.
Browse button Click to display a dialog box that enables you to select a workbook to
open. It inserts the filename in the Reference box, but you have to supply the range refer-
ence. You’ll find that your job is much easier if all the workbooks to be consolidated are
open.
Add button Click to add the reference in the Reference box to the All References list.
Make sure that you click this button after you specify each range.
Delete button Click to delete the selected reference from the All References list.
Viewing a workbook consolidation example
The simple example in this section demonstrates the power of the data consolidation
feature. Figure 28.7 shows three single-sheet workbooks that will be consolidated. These
worksheets report three months of product sales. Notice, however, that all don’t report on
the same products. In addition, the products aren’t listed in the same order. In other words,
these worksheets aren’t laid out identically. Creating consolidation formulas manually
would be a tedious task.
These workbooks are available on this book’s website at http://www.wiley.com/go/excel2019bible.
The files are named region1.xlsx, region2.xlsx, and region3.xlsx.
To consolidate this information, start with a new workbook. You don’t need to open the
source workbooks, but consolidation is easier if they’re open. Follow these steps to consoli-
date the workbooks:
- Choose Data ➪ Data Tools ➪ Consolidate. The Consolidate dialog box appears.
- Select the type of consolidation summary you want to use from the Function
drop-down list. Use Sum for this example.
