Excel’s Consolidate feature’s claim to fame is merging and summarizing values from multiple workbooks. It’s a great tool for combining data when several users work with different instances of the same file. But don’t cross this feature off your list because it sounds like something you don’t need. You can also use it to quickly summarize data in a single sheet - without sorting the data.
Consolidate is simple to use but requires a bit of initial setup. When the data range accommodates the following rules, it’s probably the quickest way to summarize data:
- The values you’re summarizing by must be to the left of the values you’re summarizing.
- The data range must have headings for each column.
- You must assign a range name to the columns you’re summarizing.
Let’s use the simple data range shown below to illustrate the Consolidate feature. Specifically, let’s find the subtotal for each date. In this case, the data meets the first two conditions listed above.
The only setup task left is to name the data range using your favorite technique, or as follows:
- Click anywhere inside the data range and press [Ctrl]+[Shift]+8.
- With the data range selected, enter CByDate (consolidate by date) in the Name Box - that’s the small control above the top-left corner of the sheet and to the left of the Formula Bar (circled above).
- Press Enter.
Now you’re ready to use the Consolidate feature to subtotal the sold values by date, as follows:
- Position the cursor in the top-left corner of the subtotal range (where Consolidate will display the subtotals). I’ll select A1 in another sheet (named ByDate), but you can select another area of the same sheet.
- Click the Data tab and then click Consolidate in the Data Tools group. In Excel 2003, choose Consolidate from the Tools menu.
- In the resulting dialog, you can choose several functions; click the Function dropdown to see what’s available, but don’t change the default. We want to sum the sold values.
- Enter CByDate in the Reference control. If any other references are in the All References list, delete them.
- Check both options, Top Row and Left Column, in the Use Labels In section.
- Click OK, and Excel will display a summarized version of your data - one row for each date and a total of sold values for that date. Excel probably won’t assume the date format and will display serial values. You can easily format those cells. In addition, you might want to delete the empty columns.
Using this same technique, you can summarize by the Region and Item columns. When subtotaling by region, name B13:D13 CByRegion. Repeat the steps above, using CByRegion as the reference in step 4. To subtotal by item, name the range C13:D13 CByItem, and use it as the Reference. In both cases, be sure to delete previous references in the All References list before clicking OK to execute the feature.
You could also use Excel’s Subtotals feature, but Consolidate is superior in two ways:
- The feature accommodates the grouping values, whether sorted or not. The values in the Date column are sorted, but neither the Region nor Item values are. However, you didn’t have to alter the technique in any way. If you use Subtotals, you’ll have to sort the data first.
- The resulting summary range is easier to use and peruse. Subtotals can be a bit messy, and you can’t just lift the subtotaling rows out of the data and use them separately from the data (although you can filter them).
Consolidate isn’t better than Subtotals; whether you use Subtotals or Consolidate will depend on your needs. You’ll probably use them both from time to time. Knowing which is the most productive for a given situation will help you work more efficiently.