Use formulas to link worksheets and workbooks Excel 2013

show more Using formulas to link worksheets and workbooks provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training show less
please wait ...

Using formulas to link worksheets and workbooks

Sometimes you need to write a formula that gets data from different sheets within the same workbook. At other times, you might need to write a formula that gets data from another workbook. In this movie, we need to have two files open, one of them, 09-04-RegionalSales, the other one, 09-04-EmployeeTable. Let's go to the RegionalSales file first. There's a sheet called RegionalTotals and we want to get Retail Totals there in cell B2. Each of the four sheets preceding this, East, Mid West, South and Pacific, all have a Retail Total in F4 and then below that a Wholesale Total and an Internet Total.

So let's start where we want to put the formula, that's Regional Totals, in cell B2 and when you write formulas that get data from different worksheets or even different workbooks, by all means, Click the various locations using the mouse or the trackpad rather than typing in file names and sheet names. We begin with equal sign here. We'll Click the East sheet and then Click the cell that has the total we want, that's F4. So the formula starts to build and you can see it in the Formula Bar. We'll put in a plus then go to the Midwest sheet.

Click that same location and then a plus, same thing with the South and so on here. And then Pacific and finally Click that F4 location and then Enter. And we've got our total and as I Double-Click here to see this more easily, we can see what's happened. Recognize how and it's certainly not intuitive that sheet names are followed by exclamation points. Now something else could happen too and it's not bad, but just be aware of it because it does make formulas look different sometimes. If the sheet name has a space in it, now none of these do, but I'm going to change the name of Pacific to Pacific Rim.

So I'll Double-Click the sheet tab, Click right after the C at the end there, add a space and put in Rim. So I've changed the name of that sheet. How does our formula look now? RegionalTotals, Double-Click. Because there is a space, we also see single quotes so Excel does accommodate that and it certainly is okay to have spaces, but in certain kinds of formulas, you can imagine how this clutters up the formula and makes it look a little bit more crowded. So I'm not saying it's wrong, but just do be aware of that. It's another strong recommendation as to why we should not be typing sheet names.

If you simply Click the sheet name as you're writing the formula, Excel pops it right into place properly. So I'll make another change here, I'll simply go back to the Pacific Rim. I'll Double-Click and Click out there and change the name back to Pacific, Enter. And then our formula, of course, has been readjusted. In this example too, since each of the sheets has Wholesale and Internet right under this, I can simply Double-Click or copy this downward and we've got our totals for Wholesale and our totals for Internet as well. So we've written formulas that gather data from different sheets of the same workbook.

Now let's imagine that in the other workbook, I'll press Ctrl+Tab to get there, there's a sheet tab called Furniture Sales and in cell J8, we want to get the Couches Total from the other file, the East sheet, the West sheet and so on. So we're going to see a similar formula, but it's going to be a little bit different and the way we get there is going to be a little bit different as well. Keep in mind, the other files, I press Ctrl+Tab, on the East sheet, Midwest, South and Pacific sheets, all have a total for, for example, Couches in cell B8, there it is right there, same thing with Midwest, South and Pacific.

So, as we jump back into the other file, Ctrl+Tab again, it's the fast way to get there and here too we will not be typing much. We certainly will begin with the equal sign and now what do we need? We need to get our East total and you can get to the other workbook by way of Ctrl+Tab or we can go to the View menu, choose Switch Windows, go to Regional Sales and then on the East tab, Click B8, put in a plus. Now, as you watch the formula being built in the Formula Bar, you see that the file name is referred to as well.

So after putting that and we then need to go to the Midwest sheet. Click on that same cell, a plus and then the South sheet, Click B8 again and a plus and Pacific sheet and Click B8 and we're all done. Let me scroll to the right here a bit so we can see this even better and I'll even zoom in too so that we can see what's happened here. The formula certainly is not something you would have typed as I Double-Click it. So what are we seeing here? We are seeing the name of the file in brackets and then the sheet name and then the location of the cell.

This happens four times here since we're gathering data from four separate sheets. So the formulas certainly look a lot more complicated and involved, but it really isn't. In this case, it's really gathering data from four cells and there's our total right there. Now this is going to look quite a bit different though if we close the other file. If you are writing formulas that get data from other workbooks, you definitely want to have the other file open at least as you're creating the formula, but what happens if we go to the other workbook? I'll press Ctrl+Tab again and close and save this workbook.

So I'll choose File>Close or possibly just press CTRL+W, either way. File>Close and do I want to save this? Yes, I do. We've saved it. Now look at this formula as I Double-Click, because the other file is closed, we need to know -- have a reference as to where it is. It's on the C Drive, within a folder called Users, within a folder with my name, Desktop>Exercise Files and of course, this will look different in your environment if you've saved it in a different location, but all this that I'm highlighting right here represents the content of one cell and then we see this being repeated for the three other cells.

So, the formulas look incredibly complex and involved, but they're really not when you think about it. So we've got a formula that's gathering data from different sheets in a completely different workbook and as we saw earlier, we can also write formulas that gather data from different sheets of the same workbook.

Using formulas to link worksheets and workbooks
Video duration: 6m 1s 6h 32m Appropriate for all


Using formulas to link worksheets and workbooks provides you with in-depth training on Business. Taught by Dennis Taylor as part of the Excel 2013 Essential Training

Business Education + Elearning
please wait ...