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.
Learn more with Excel CPE
Excel offers a wide variety of functions, formulas, and automation that can save you hours of work while improving accuracy and outcomes. To help you make the most out of this software, we offer a wide variety of CPE courses to support your learning!
Check out Becker's wide range of CPE courses that teach you to make the most of this powerful tool:
- Excel: Technical Analysis Trading Strategies
- Excel: Enterprise Risk Management
- Excel: Magic with Excel
- Excel: Solve Hard Problems in Corporate Finance
- Excel Metrics: Best Practices
- Python for Excel Users: A Gentle Introduction
These and many more Excel-focused, CPE credit-earning courses are included in Becker's Prime CPE subscription. Sign up now for 12 months of access to over 1700 on-demand, webcast, and podcast CPE courses!
Unlock unlimited CPE with a Prime Subscription
Becker makes it easy to meet your CPE requirements, gain new skills, and stay aware of critical updates and changes in the industry!
With Prime, you can access over 1,700 courses for a full year and earn unlimited CPE credits.
Related Articles
Share
