Excel Consolidation

Consolidating data in Excel is covered extensively in our NYC Excel classes. For those outside New York, find and compare Excel classes near you or Excel courses online.

Consolidation

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 is the North American division, 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.

Creating a Summary

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.

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. So 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 1. 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 I'm going to choose Add.

Then I want to head over to Consolidate 2. 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 reselect 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 3. There is 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 4, and I need to extend this to include the Totals, because that's what I've been doing all along. I'll click Add.

Extra Options

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 checkbox.

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 will automatically update the summary when you make an update to any values in the four sheets that we've selected.

Now I can click OK, and now I have a summary for Quarters 1,2, 3 and 4. 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.

Updating Values

But let's say we did want to check the ability of this consolidated table, the ability for it to update our values. Before we go, take a look at Hardware. You see currently it's at 900. I'm going to go to Consolidate 1, 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.

Recap

So that is the Consolidate function. It's very useful if you need to consolidate multiple sheets into one and have one summary worksheet.