Streamline your data analysis by efficiently combining multiple Excel worksheets into one consolidated summary. Learn step-by-step how to utilize Excel's Consolidate function to effortlessly merge and update information across several divisions.
Key Insights
- The Consolidate function in Excel allows combining data from multiple worksheets—such as divisional sales data from Australian, European, North American, and South American operations—into one summarized sheet.
- Using the "Create links to source data" option ensures automatic updates in the summary worksheet whenever changes are made to the source sheets, making the consolidation dynamic; for example, if hardware pricing is increased by $100 in one division, the summary instantly reflects this change.
- When consolidating tables with consistent ranges, Excel saves time by automatically selecting equivalent ranges across different worksheets, eliminating manual reselection and streamlining the consolidation process.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Consolidate function. This function allows you to consolidate information across multiple sheets. We're not just consolidating one value, but we can actually consolidate multiple tables.
In our example here, we have our summary sheet, and then we have four tabs that contain different divisions. Consolidate 1 contains the Australian division. Consolidate 2 is the European division.
Consolidate 3, North American, and Consolidate 4 is the South American division. I would like to add up all the values from these four different worksheets and come up with a summary right here in the consolidation tab. In order to do that, I'll start by clicking on the data tab.
If I'm not already there, go to the data tools group, and then I'll choose consolidate. This dialog box displays, and you'll take a look at all the options and see which are appropriate for what you want to do. Well, like I said earlier, I would like to sum all the values, so I'm going to choose sum.
I also have other types of calculations that I can perform, but I'll stick with sum. When it comes to reference, I need to select and add those ranges into the all references box. You can imagine that you're going grocery shopping, and when you go into a supermarket, there's a little basket that you can pick up to add your items before checkout.
You'll add your items to that basket right before checkout, and then you'll check those items out, and then that will complete the process. We're going to do sort of the same thing. So I'll click on reference, and I'll go and pick up the first division.
I'll go to consolidate one. I want to pick up the Australian division. I'm going to select starting from the headers, and I'm going to go down to the totals, and then what I'm going to do is choose add.
Then I want to head over to consolidate two. The nice thing about this is if the range you selected before is the same size as the range on the next worksheet, then you don't have to do anything. If it isn't, then you'll have to re-select and make sure you're selecting the same type of range, top and bottom.
Now that I've done that, I'll click add. I'll go to consolidate three. There's that fortunate situation I was talking about.
Everything is selected, and all I have to do is click add, and then finally I'll choose consolidate four, and I need to extend this to include the totals because that's what I've been doing all along. I'll click add. Now there are a couple of options here.
I can choose left column if I have labels in the left column. This means that these values won't be considered when it comes to summing up the values, so I'll click that check box. I already have this option checked, create links to source data.
That's going to be useful if you need to update any of the information in the other sheets. It'll automatically update the summary when you make an update to any values in the four sheets that we've selected. Now I can click okay, and now I have a summary for quarters one, two, three, and four.
If I wanted to drill down, I could click on the plus sign and see the individual values that make up the total for accessories. Unfortunately, I won't be able to see where the information is coming from. You just get the name of the sheet that you're currently on, but you do not see the name of the sheet where it came from.
But let's say we did want to check the ability of this consolidated table, the ability for it to update our values. So I'll go over to consolidate. Before we go, take a look at hardware.
You see currently it's at 900. I'm going to go to consolidate one, and I'm going to increase the price for hardware by $100. So I'll go from 200 to 300.
Now according to this, according to the option that we selected, that value should update our consolidation summary. So I'm going to click back on consolidation, and you'll see we've gone from 900 to 1,000. So that is the consolidate function.
Very useful if you need to consolidate multiple sheets into one and have one summary worksheet.