[SOUND] In the previous video, we saw how we could work across multiple sheets within a workbook. In collaborative environments however, it often is required that we work with data in multiple workbooks. And Excel allows us to do this through linking workbooks. We have exactly this example here, where Uma needs to collect the data from the Q1, Q2, and Q3 workbooks, and summarize those values into a total expenses. Now before we begin the video, please make sure you've opened up W1_LinkingWorkbooks.xlsx and that you've closed any other workbooks you may still have open. Now we're going to be working with four workbooks and we want to see them all on the screen at the same time. And you're going to find that the ribbon starts taking up a lot of space. So to save on space, we're going to minimize our ribbon by double-clicking on the Home tab. Now let's go get those other workbooks open. So I'm going to press Ctrl+O. [SOUND]. I'm going to come to browse. Navigate to where your workbooks are stored. And then can you please select W1_ExpensesQ1.xlsx, hold your shift key down select Q3 so they're all selected. And click Open. Now all four workbooks have been opened. But it'd be much easier if I could see them all on screen simultaneously. To do this we can use the arrange all function. This is available on the view tab. But there's a great little feature I'd like to show you called, Tell me what you want to do. It's quite new. And if you click in here and start typing what you want to do, Excel will actually give you a short list of things that meet your criteria. And, yeah, arrange all. So I'm going to click on that. I do want them to be tiled and I'm going to say, OK. And now all four of my workbooks laid out nice and easy for me to use. Now what I want to do here is get the total values from each of these workbooks into this workbook. I do not want to type them though, partly because I'm very likely to make an error and it's going to be very slow, but also because if the values should change in one of these workbooks I need this one to update automatically. So we're going to use exactly the same technique we used when working across worksheets, and you'll see it works almost exactly the same with one small exception. So start by clicking in C7 and type =. Now, I'm going to come to my Q1 workbook. Click once to select the workbook and then click on the field you want to pull through. Now, just have a quick look at this formula. It's got first the name of the workbook, then the name of the worksheet and then the cell. But it's done something different. It's applied an absolute cell reference and this happens automatically when working across workbooks. Now we're going to copy this formula in a moment, so we want to get rid of that. So, to do that just press your F4 button three times. Absolute cell reference gone. Now, to get to this next cell I'll going to press Tab. And let's repeat that process. So =, come to HR Expenses 2, click in that total field, F4 three times to get rid of our dollars, and tab. And the last one now. We're going to get our Q3 total, and of course turn off our dollars, and enter. Now what we've done there, is we've pulled through the values from the workbooks, but we've also linked our expenses workbook to our three quarterly workbooks. And what that means is if a value changes in one of them, it will update here as well. Let's test that theory. So let's say, for example, in Q1, Carlos actually did 400 Ks in January. So I'm going to come to his miles driven. I'm going to change that to 400, press enter and note that when I do the miles driven here automatically updates. And if I were to undo that change, we're back to where we started. And the good news is relative cell references still works, so if I come into here, select my top three calculations, and drag them down using my fill handle, the values are pulled through. One small problem, I've lost my formatting. So let's just come to here and say, fill without formatting. Great, so that worked beautifully. I'm just going to make this workbook maximized again so you can actually see what's going on. Right, let's just finish this off quickly. So I'm going to copy these bottom four calculations. Ctrl+C this time, Ctrl+V, and Ctrl+V. And you can see I've very quickly completed my total expenses for Sydney. Now linking is obviously very beneficial when you have workbooks that are stored in a similar location and aren't likely to move around too much. The problem is, once those workbooks start moving or being renamed, the links can get damaged. So the next thing I need to show you is where you can go to manage these links. So I'm going to come to my data tab. And just underneath the data tab you'll see edit links. This is normally grayed out, but here we have links so when we click on it, we can now see our three linked workbooks. If one of these links becomes damaged, we just click change source. And it will let us go and correct it to where the workbook actually is located or what it's called. I'm going to click cancel because it's fine. The other option we have is to break a link. I strongly recommend you don't do this unless you're absolutely certain you need to because once you've broken the link it is a permanent move. You can't simply undo that process. But let's see what happens when we break the link to our Q1 workbook. So I'm going to click Break Link. There's my warning. I'm going to go ahead anyway. And when I close the dialogue and come back in to my workbook you'll see when I go to any of the cells, the formula has been replaced by the last known value. So I've completely lost that link and the only way to get it back is to repeat the process we did earlier. Take a moment to consider if there are any situations in your workplace that could be solved by using linked workbooks. [SOUND]