Welcome to another series of Dr. Wayne Winston’s best Excel tips! I’m Wayne Winston, Becker’s resident expert on all things Microsoft Excel. Let’s learn another helpful Excel skill that can make your everyday accounting tasks a little bit easier.
Two, four, six, eight, how do we consolidate? A business analyst often receives worksheets that tally the same information (such as monthly product sales) from different affiliates or regions. Using Excel’s Data Consolidate feature (found on the Data Tab in the Data Tools Group), you can easily combine or consolidate the information in different worksheets, or even different workbooks!
We illustrate this great feature in this Excel workbook. As shown in Figures 1 and 2, the “East” and “West” worksheets contain our product sales for January-March in the East and West regions of a country.
Figure 1: East sales
Figure 2: West sales
To use the Consolidate command, go to a new worksheet, and choose the upper left-hand corner of the range where you want the consolidated results. We chose cell A1 of the worksheet “Consolidate.” Then, we go to the Data tab and into the Data Tools group, and choose the Consolidate option. We then fill in the Consolidate dialog box with the “Sum” function dropdown option and with each range of source data from the East and West worksheets. Remember to click “Add” in between each range. Make sure to select the three options at the bottom, “top row,” “left column,” and “create links to source data.” This is shown below in Figure 3.
Figure 3: Consolidating East and West region sales
Choosing “Sum” ensures that the consolidation will return total sales for each product in each month (other functions such as Average and Standard Deviation are also available.) For example, as shown in Figure 4 below, we can see that the total February sales of Product A is 1,317. The “create links to source data” option ensures that if the source data is changed, our consolidation results automatically update. To verify this, increase cell D4 of the West worksheet by 1 to 101 and you will see that the 1,317 in the Consolidated worksheet automatically increases to 1,417!
Figure 4: Consolidated sales results
Finally, if you think you will add new rows to your source data, you might consider selecting more rows when choosing the source data. For example, for the East data, you might select the range A4:D500 to ensure that new East data would be included in the consolidation summary.
Hopefully, the Data Consolidate command will simplify your work with data residing in multiple worksheets and/ or workbooks. Keep visiting the Becker blog for more of my best Excel tips.
Read my previous tip on analyzing loans in Excel.
