Consolidate Tool In Excel For Mac

| 14-05-2021
[RAND-1-100] Comments

Click Data>Consolidate (in the Data Tools group). In the Function box, click the summary function that you want Excel to use to consolidate the data. The default function is SUM. How to Combine Two or More Excel Spreadsheets By Andrew Braun – Posted on Feb 19, 2018 Feb 19, 2018 in Software Hacks Microsoft Excel is a great application for you to keep your data organized and working for you, but all those numbers can sometimes get unwieldy when scattered over too many spreadsheets.

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.

Microsoft Weekly Newsletter

Be your company's Microsoft insider with the help of these Windows and Office tutorials and our experts' analyses of Microsoft's enterprise products. Delivered Mondays and Wednesdays

Consolidate Tool In Excel For Macro

Sign up today Sign up today

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:

Lickable
  1. Click anywhere inside the data range and press [Ctrl]+[Shift]+8.
  2. 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).
  3. Press Enter.

Now you're ready to use the Consolidate feature to subtotal the sold values by date, as follows:

Target

Mining Target

  1. 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.
  2. Click the Data tab and then click Consolidate in the Data Tools group. In Excel 2003, choose Consolidate from the Tools menu.
  3. 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.
  4. Enter CByDate in the Reference control. If any other references are in the All References list, delete them.
  5. Check both options, Top Row and Left Column, in the Use Labels In section.
  6. 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.

3m Sponge Brand

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.

Excel 2016 allows you to consolidate data from different worksheets into a single worksheet. Using the program’s Consolidate command button on the Data tab of the Ribbon, you can easily combine data from multiple spreadsheets.

Mining Target Crossword

For example, you can use the Consolidate command to total all budget spreadsheets prepared by each department in the company or to create summary totals for income statements for a period of several years. If you used a template to create each worksheet you’re consolidating, or an identical layout, Excel can quickly consolidate the values by virtue of their common position in their respective worksheets. However, even when the data entries are laid out differently in each spreadsheet, Excel can still consolidate them provided that you’ve used the same labels to describe the data entries in their respective worksheets.

Most of the time, you want to total the data that you’re consolidating from the various worksheets. By default, Excel uses the SUM function to total all the cells in the worksheets that share the same cell references (when you consolidate by position) or that use the same labels (when you consolidate by category). You can, however, have Excel use any of other following statistical functions when doing a consolidation: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, or VARP.

Macro

To begin consolidating the sheets in the same workbook, you select a new worksheet to hold the consolidated data. (If need be, insert a new sheet in the workbook by clicking the Insert Worksheet button.) To begin consolidating sheets in different workbooks, open a new workbook. If the sheets in the various workbooks are generated from a template, open the new workbook for the consolidated data from that template.

Before you begin the consolidation process on the new worksheet, you choose the cell or cell range in this worksheet where the consolidated data is to appear. (This range is called the destination area.) If you select a single cell, Excel expands the destination area to columns to the right and rows below as needed to accommodate the consolidated data. If you select a single row, the program expands the destination area down subsequent rows of the worksheet, if required to accommodate the data. If you select a single column, Excel expands the destination area across columns to the right, if required to accommodate the data. If, however, you select a multi-cell range as the destination area, the program does not expand the destination area and restricts the consolidated data just to the cell selection.

If you want Excel to use a particular range in the worksheet for all consolidations you perform in a worksheet, assign the range name Consolidate_Area to this cell range. Excel then consolidates data into this range whenever you use the Consolidate command.

Consolidate Rows In Excel

When consolidating data, you can select data in sheets in workbooks that you’ve opened in Excel or in sheets in unopened workbooks stored on disk. The cells that you specify for consolidation are referred to as the source area, and the worksheets that contain the source areas are known as the source worksheets.

If the source worksheets are open in Excel, you can specify the references of the source areas by pointing to the cell references (even when the Consolidate dialog box is open, Excel will allow you to activate different worksheets and scroll through them as you select the cell references for the source area). If the source worksheets are not open in Excel, you must type in the cell references as external references, following the same guidelines you use when typing a linking formula with an external reference (except that you don’t type =). For example, to specify the data in range B4:R21 on Sheet1 in a workbook named CG Music – 2014 Sales.xlsx as a source area, you enter the following external reference:

Note that if you want to consolidate the same data range in all the worksheets that use a similar filename (for example, CG Music – 2012 Sales, CG Music – 2013 Sales, CG Music – 2014 Sales, and so on), you can use the asterisk (*) or the question mark (?) as wildcard characters to stand for missing characters as in

In this example, Excel consolidates the range A2:R21 in Sheet1 of all versions of the workbooks that use “CG – Music – 20” in the main file when this name is followed by another two characters (be they 12, 13, 14, 15, and so on).

When you consolidate data, Excel uses only the cells in the source areas that contain values. If the cells contain formulas, Excel uses their calculated values, but if the cells contain text, Excel ignores them and treats them as though they were blank (except in the case of category labels when you’re consolidating your data by category).