If you are a business analyst, you receive worksheets that calculatethe same data each month, such as product sales for different areasand/or parts of the company. Once you receive these worksheets, you needto combine (consolidate) them by collecting data from various workbooks,creating a summary table in one workbook to perform analysis on thedata.
Another example is consolidating budgets from different departmentsinto the entire company budget. We can use PivotTables to achieve this,but the Consolidate feature allows usto automatically ensure that all the changes made to all of theworksheets show up in a consolidated worksheet. Let’s work through thefollowing steps to set up a summary sheet:
- Open the workbook that contains the data you wish to consolidate.For this example, we will usethe SafestSolutionsSales.xlsx workbook. This workbookcontains four sheets—NORTH, SOUTH,EAST, and CONSOLIDATE—the data is constructed inall four sheets with identical formatting applied, including the samedata labels (column headings). This is crucial for the consolidation tocorrectly compute. Note that all the data does not need to be in thesame workbook, as you can browse for separate workbooks whenconsolidating.
- Click on a cell in the worksheet you want the consolidation to beplaced in. For this example, we will usethe CONSOLIDATE worksheet. It is important to note that thedata in all the worksheets is organized in a consistent manner. Data canbe arranged in a different order but it would need to contain the samelabels (column headings) in each worksheet. Notice that the sales reps’names in the NORTH and SOUTH worksheets aredifferent, as well as a number of sales reps and the values.
- Click on the CONSOLIDATE worksheet. For this example, wewill place the consolidation in cell A4.
- Click on the Data tab, then locatethe Data Tools group. Select the
- The Consolidate dialog box popsup. It is always a good idea to name the range you wish to consolidatein each of the workbooks, especially if you add new data to your sourceworkbooks, as the data is included automatically.
- Choose how you would like to aggregate the data by choosing an itemfrom the drop-down list in the
Consolidate dialog box. We will use SUM for this example as we want to find outthe total sales for all the sales reps.
- Click on the Referenceplaceholder, then navigate to the first worksheet (NORTH) tocollect the range, A6:I10, then click on the
ADD icon. Place the mouse pointer back onthe Reference placeholder, thennavigate to the second worksheet (SOUTH) to collect therange, A6:I11, then click on the ADD icon. Although the range isautomatically assumed by Excel, there could be more data on otherworksheets to collect. Be sure to check this before adding and moving onto the next range. Do the same again for the third worksheet, calledEAST:
- The final step is to choose whether you would like to use the labelsin the top row and/or the left column to consolidate the data andwhether to create links to the source data. Creating links to the sourcedata allows data to be updated automatically (that is, refreshed to theconsolidation sheet when any editing takes place in the source data).Select all three options for this example.
- Click on the OK button to view theresults on the CONSOLIDATE worksheet:
- You will see expand icons to the left of the consolidated data. Thisallows you to see more detail about a particular sales rep or month.Format the consolidated data as you wish and add any furthercalculations, such as the sum of all sales reps by month and individualreps’ sales to date. As you have created links to the source data, youwill be able to edit the data in the source worksheets and see thechange immediately in the consolidated data:
Now that you have learned how to create a consolidated workbook, wewill look at how to create a macro to perform multiple steps in aworkbook automatically.