 My name is Patrick Loner, and I'll be your instructor on this course. We're gonna start with a little bit about my background. I've been in the IT industry actually for about 18 years, working as a network administrator and a Microsoft certified trainer. And of course, a long way, I've done my fair share of applications training as well, and I've also used the Office programs personally, every version since I think Office 97 was the first version that I was involved with. I heavily use, of course, Outlook, Excel, PowerPoint, Word, all of those very well-versed in the differences, the older versions to the new, the compatibility issues from an IT perspective, but also just using them from an application perspective. So I'm excited to be able to go through these courses and share this information with you and get you ready to use the latest version of Microsoft Office. In this first topic, we're gonna be looking at using links and external references. As I believe we've already discussed, when you start to get into complex worksheets, you can introduce errors and have a mess on your hands. We have to refer to data in its original location, and that's often better than making copies of data. This is because when you create multiple copies and then you have to update information, you have to remember to go and update all of the copies. Well, Excel enables you to create formulas and functions that link data from multiple worksheets and workbooks, and that helps you to create more of a summary, and it's gonna still allow you to maintain the view of your original data. So we begin with linked cells, and this is just the ability to connect one cell to the data that's entered in another cell. It's not a cell reference. A cell reference is just calling the value in another cell, and I'm using it, usually in a formula or function. A linked cell behaves as if it actually contains the data in the original cell. So if you update the value in the original cell, then the linked cell value gets updated automatically, and as the linked cell behaves as if it actually has that data, you can do any calculation on the linked cell that you would have done on the original cell. That takes full advantage of all of the calculation capabilities in Excel, all of your data analysis functionality, and does so without having to worry about introducing errors into the raw data, because as the linked cell updates automatically, so will all of the calculations. Now there are two different kinds of links. So you have the internal link, which is a link to other worksheets in the same workbook, and then you have links to other workbooks, which are called external links. By default, Excel is gonna create graphically selected external links as absolute references and graphically selected internal links as relative references. It's important to note here the syntax. I mean, technically you create links to other cells the same way you would enter a formula into the cell. In fact, you really can think of the link as a specialized type of formula. So with that understanding, the syntax should be a little bit easier. To create an internal link, type an equal sign. You type an equal sign into the cell as if you were typing a formula, like we see there on the top left, equals B2 plus B3 plus B4, okay? And that is the B5 cell, and that's just the value that we are putting in there. If you're linking a cell to another worksheet, then you have to include the worksheet name, which is followed by an exclamation point, and so we see that in the next internal link down, equals worksheet B2. So the value of the B2 cell from that and whatever the name of it is. If you wanna create an external link, you will also have to include a pointer to the workbook that contains the cell that you're linking to. And so there in the square brackets, you put the name of the workbook. So equal sign, square brackets, name of the workbook, and then reference to the worksheet and the reference. And as we said, that's gonna be an absolute reference, whereas it's a relative reference on the internal link. Once a link has been created, you can use the edit links dialog box to manage external links in your workbooks. This displays a list of all the workbooks that the current workbook has a link to. You can get here by going to the data tab, the connections command group, and then clicking edit links. And so that's your dialog box that is going to pop up. And by the way, that edit links command will just be grayed out if there are no external links in the workbook. The different options that you have here, the update values button is going to update the values in link cells if changes have been made to the source data. This is really only required if you've turned off automatic updates. Automatic updates is the default. And in most cases should be left that way. The change source button will open up a separate dialog box, allows you to change which workbook. The current workbook is linked to. The open source button will open a particular workbook, whichever one you have selected there. Break link removes the links to the workbook you have selected. Check status shows the status of all the links. And then the startup prompt is going to button bottom left is going to open that dialog box, which determines or help, which you can use to determine whether or not Excel gives you a warning when you open this workbook. Now the same way that you can link cells or similar to other worksheets and external workbooks, you can also use references to cells or ranges in formulas and functions. And so that allows you to display a calculation or function result in a different location from where the actual data is stored, which is pretty cool because I can then just create much smaller tables, much more concise information, but it's pulling its data from some other location. The syntax for including references to cells or ranges in other worksheets is the same as it is for creating links. Whenever you need to include a reference in a formula or as an argument in a function, you just put the worksheet reference. And if necessary, you put the workbook reference along with the cell or range reference, okay? So there are some and then we have the actual worksheet that it's on and the reference to the range. So it's saying to get that information from D2 to D21 cell range on the April sheet. In the other case, we have a quarters Excel workbook, so we reference the workbook, then we reference the worksheet, then we reference the range. Only difference is you reference the range as absolute references if it's coming from another workbook. I'll see you in a minute.