In the last video, we saw how we could work with data in different workbooks by linking those workbooks. Linking, however, can cause problems of its own, and Uma has a situation now. Where she wants to take the regional expenses from three different regions. And sum them in a single workbook, that she can send to head office. She wants to avoid the complication of working with links. A tool that can be really useful to her is the consolidate tool. It can be found on the data tab of the ribbon, in the Data Tools group, and you'll see the consolidate tool. Now before we get started, as we did last time, I suggest you minimize your ribbon, and then let's get out other three workbooks open. So Ctrl+O, click Browse, and the three workbooks we needed are the expenses for Melbourne, Perth, and Sydney. So use Ctrl to select them, and then click Open. Once again, all four workbooks have opened, but it's easier if we can see them. So I'm going to come to my view tab this time, and click Arrange All, and then OK. And there are my four workbooks, ready to go. Now when we want to do a consolidate, the first and possibly most important step, is to make sure you're clicked in the cell where you want the consolidation to begin. A common mistake is to accidentally click in one of these workbooks, and you risk overwriting your data. Now we're going to find our consolidate tool, so I'm going to come up to Data, come to Data Tools and there's our consolidate, tricky to find, but still there. This is the consolidate dialog, the first thing you're allowed to do is choose which function you want to use. So you don't have to sum the data, you can get a count or an average, or any list function, really. We're going to stick with sum, and now we need to tell Excel where to get this data from. And that's what we put in this reference box, so click in the reference box. We'll start with Perth, so I'm going to come to the Perth workbook, and I'm going to select everything from C7 all the way down to F28. Now, the next important thing, make sure you click the Add button after adding each reference, otherwise, you will end up overwriting them. So, that was Perth, let's do Melbourne, it's the same thing. Come to your Melbourne workbook, select everything from C7 down to F28, and click Add. And then finally, we will do Sydney as well, so everything from C7 down to F28. And click Add, we have now told Excel to add up the values from those three workbooks and place them into this one. So now we just press OK, and there is all of the data from those three workbooks, added together into our new workbook. This is, however, a snapshot, if I click on one of the cells, you'll see no formula, simply the value at the time that the consolidation was run. What this means is if I come back to Perth and change my miles driven for Q1 to 1741, this does not update. That said, once you've set up the consolidation, it actually remembers it, and you can simply rerun it anytime you need to. So I'm going to click back into C7, come to my Data, come to Data Tools, click Consolidate. Check if everything's okay, OK, and now it's picked up the changes. So once you've set up the consolidate, you can run it as many times as you need to, and you don't need to have to worry of links. That said, if you want links, you can add them, so I'm just going to make this a little bigger so you can see what's happening. And I'm going to duplicate this worksheet, because Uma doesn't actually want links, so this is just for demonstration. I'm going to hold my Ctrl key down, and drag the worksheet tab to get a copy, and then I'm just going to delete the data out. Now we're going to run exactly the same consolidation again, so Data > Data Tools > Consolidate. But this time, I'm going to have a look at this little check box at the bottom, called Create Links to Data Source, and I'm going to put a tick in that and say OK. Now at first glance, it looks like an identical consolidation, but have a look to the left, and you'll notice gaps in the row numbers, and these funny little pluses. What has happened is, Excel has actually created an outline, and if I click on one of these pluses to expand it, you'll see it's actually pulled through each individual parking toll from each of the workbooks, and then summed them up. So while the links is quite powerful, it is also a little bit messy. And you should be warned up front, you cannot undo a consolidation of links. So now if I needed to get rid of this outline, it's going to be a bit of a manual job. Quick heads up, select all the cells affected, come to your Data tab, come to Outline, come to Ungroup, and then Clear Outline. That will get rid of the outline for you, but then you'll still have to manually go and remove those extra rows that have been added. So just think carefully about whether this is what you want before you go about adding it. You will have noticed that the workbooks had to be identical in structure, and that is because we have used Consolidate by Position. In the next video, we're going to look at an even more powerful consolidate option, called Consolidate by Reference. Which allows our workbooks to be slightly different.