I recently ran an Advanced Excel course where I was asked “what is the quickest way to pull data from multiple worksheets into a summary worksheet?” Excel offers several solutions for consolidating and summarizing data from different worksheets and workbooks. In this post I want to share with you the Consolidate feature. This feature combines values from multiple ranges into one range.
In the example below we want to summarize the Used Car Sales from the Northern worksheet (2), the Central worksheet (3) and the Southern worksheet (4) on the Summary by Product worksheet (1).
You will notice the range for Southern worksheet (4) includes an extra row. This is because the Southern figures include data for four different vehicle types. The Consolidate feature easily includes this extra row into the summary data table.
- Placing the cell selector on the top left corner of the range that will hold the summarized data, in this case cell A6, from the Data tab select Consolidate.
- From the Function box select the appropriate function. In this example we will use the Sum function.
- Click into the Reference box and then either type or click and drag over the first range to be included in the summary.
- Click Add to add the range to the All references list.
- Repeat steps 3 and 4 until all ranges have been included. At this point you can elect to include column and row labels from the Use labels in options. In the example below the row labels were included in the range therefore the Left column option has been selected.
- Click OK. The data will now be summarized. In the example below a Total row has been added in row 10 to give an overall summary figure. Excel has used the row labels to identify matching data and created a summary for each.
In the following video tutorial I demonstrate this process.
Following on from this post I’ve created a video tutorial to demonstrate how you can use this feature to summarize data from multiple sources using the Pivot Table command. You can check it out in my post Excel – How to summarize data from multiple worksheets (Part 2 of 2).