Consolidating Multiple Workbooks in Microsoft Excel

How to consolidate multiple workbooks in Microsoft Excel

Applies to Office 2007, 2010, 2013 and 2016

Consolidating data from multiple sheets or Workbooks need not be a daunting task to carry out. First step towards consolidating data is making sure the template you are using to store your data must have a consistent and uniform format (same rows and columns).

To summarize from separate worksheets, you can consolidate data from each into a master worksheet. The worksheets can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data, you are assembling data so that you can more easily update and aggregate as needed.

In this post, I will use data from 3 Divisions (Division I  , II and III Budgets), and a blank consolidation file (where all the 3 divisions will be consolidated to). Download Practice Files here for this post Consolidating Multiple Files in Excel

Each of the 3 divisions has a summary sheet show sales and expenses across 12 Months (From January – December).

There are two ways to consolidate data: by Category or by Position.

Consolidation by position: When the data in the source areas is arranged in the same order and uses the same labels. Use this method to consolidate data from a series of worksheets, such as departmental budget worksheets that have been created from the same template.

Consolidation by category: When the data in the source areas is not arranged in the same order but uses the same labels. Use this method to consolidate data from a series of worksheets that have different layouts but have the same data labels.

  • Consolidating data by category is similar to creating a PivotTable. With a PivotTable, however, you can easily reorganize the categories. If you want a more flexible consolidation by category, consider creating a PivotTable instead.

Consolidation steps

  1. If you haven’t already, then in each worksheet that contains the data that you want to consolidate, set up your data by doing the following:
    • Make sure that each range of data is in list format, so that each column has a label in the first row and contains similar data, and there are no blank rows or columns within the list.
    • Put each range on a separate worksheet, but don’t enter anything in the master worksheet where you plan to put the consolidation – Excel will fill this in for you.
    • Make sure that each range has the same layout.
  2. In the master worksheet, click the upper-left cell of the area where you want the consolidated data to appear.
  3. On the Data tab, in the Data Tools group, click Consolidate.
  4. In the Function box, click the summary function that you want Excel to use to consolidate the data. The default function is SUM.
  5. Select your data
    1. If the worksheet that contains the data that you want to consolidate is in another workbook, first click Browse to locate that workbook, and then click OK to close the Browse dialog box. Excel will enter the file path in the Reference box followed by an exclamation point, and you can continue to select your data
    2. Next, in the Reference box, click the Collapse Dialog button to select the data in the worksheet.
  6. Click the worksheet that contains the data you want to consolidate, select the data, and then click the Expand Dialog button on the right to return to the Consolidate.
  7. Now check the boxes with “Top Row” and “Left Column”
    1. In the Consolidate dialog box, click Add, and then repeat to add all of the ranges that you want.
    2. Automatic vs. Manual updates: If you want Excel to update your consolidation table automatically when the source data changes, select the Create links to source data check box. If unchecked, you can update the consolidation manually.
  8. Notes:
    • You cannot create links when source and destination areas are on the same sheet.
    • If you need to change ranges once you’ve added them, you can click each one in the Consolidate dialog and update them when they show up in the Reference box, then click Add. This will create a new range reference, so you’ll need to delete the previous one before you consolidate again. Just select the old reference and press Delete.
    1. Press OK, and Excel will generate the consolidation for you. It will be unformatted, so it’s up to you toDownload post as PDF How to consolidate multiple workbooks in Microsoft ExcelDownload Practice Files here for this post Consolidating Multiple Files in Excel

      Published by OpenCastLabs Consulting | OpenCastLabs Consulting is a leader in the field of data science, analysis, Advanced Excel Training and Microsoft Office Applications training and development.

      www.opencastlabs.co.ke | +254 20 2347622

       

       

     



Leave a Reply