Viewers: in countries Watching now:
Whether you're a novice or an expert wanting to refresh your skillset with Microsoft Excel, this course covers all the basics you need to start entering your data and building organized workbooks. Author Dennis Taylor teaches you how to enter and organize data, perform calculations with simple functions, work with multiple worksheets, format the appearance of your data, and build charts and PivotTables. Other lessons cover the powerful IF, VLOOKUP, and COUNTIF family of functions; the Goal Seek, Solver, and other data analysis tools; and how to automate many of these tasks with macros.
If you work with files that have formulas that get data from other workbooks, those are called links. And you need to know where those links are and how to find them. We need to have two files open in this movie, 09-05-EmployeeTable as well as, I'm pressing Ctrl+Tab, 09-05-RegionalSales. Right now, there are no linkage formulas. How do we know that? First of all, in Regional Sales, if we go to the DATA tab, the term Edit Links is grayed out and we cannot select it.
Therefore, there are no formulas in this workbook that get data from other workbooks. Let's press Ctrl+Tab and go to the other workbook. Are there any formulas here or on the DATA tab? Edit Links here is grayed out as well. So let's create a linkage formula between the two. In cell J8 here, we want the Couches' total. Equal or press Ctrl+Tab, go to the other workbook and we're going to get from each of the four sheets here, the Grand Total for Couches. It's in cell B8 so we'll simply click there, click Plus then do the same thing with Midwest, click that cell and a Plus and South, same cell and Pacific, same cell and Enter.
We've got our total here. Now, DATA Tab>Edit Links and as we click this option here, recognize Edit Links tells us that this workbook, the one we're currently using, is getting data from 09-05-RegionalSales below this we see the option Startup Prompt. In the future, when we open this file, let's imagine we're about to close it, when we open this file in the future, it would be worth considering a Startup Prompt. The Startup Prompt box gives us three options.
The last option says, 'Don't display the alert and update the links.' I think most people would not want this. This simply means if we open this file again, nothing special is going to pop-up on the screen. We won't even know necessarily that there are links and they will be updated. And the one above it is hardly much better. It doesn't display the alert and it does not update the links. The first choice is the best one and it's worded a bit strangely. It says, 'Let users choose to display the alert or not.' That doesn't mean that every time we open this file in the future, that there's going to be a prompt asking us if we want to see the prompt anymore.
It's simply going to ask us if we want to update. I think this will be the preferred choice for most people. Let's click OK and close this. Let's imagine what happens in the future if we were to open this file and remember the total here is 11,660. So we're going to close this file and save it. Click the Save button in the Quick Access Toolbar is certainly one way and we can close this with Ctrl+W. Let's imagine we're working with the other file now and I'm going to make a change here to Couches. I'm going to make that to be 2000.
If the other file were open, that total of 11,660 will have gone up substantially, but it's not open right now. So we're going to save 09-05-RegionalSales. I'll click the Save button up there and then press Ctrl+W to close it. Now I want to open the other file and I can go to the File tab in the Ribbon and down below under Recently Open Files, there's that Employee Table so I'll open this again. For the first time, we are seeing this prompt and remember that 11,660 has not yet changed.
Our prompt says, 'This workbook contains links to one or more external sources that could be unsafe.' Do we want to update? Yes, we do. Watch the 11,660 change. It's changed to 12,510 even though the other file isn't open. The formula is still in place and of course on the Data tab, Edit Links is there reminding us the source of that formula. Now, what happens if you're a different user or it's you a few months later, you open this file perhaps you saw the prompt and you said update, but you forget where the formula is.
Maybe you're looking at this sheet or that sheet or maybe you've got more sheets in the meantime and you want to know where linkage formulas are in this workbook. Let's go back to where this particular formula is. I'm going to Double-Click here. What is it that's different about these kinds of formulas? Well, there are two things are different. It's probably unlikely that you're ever using a left bracket or a right bracket in other parts of a workbook. Now you certainly can and that option is open and if you work with tables you might even have formulas with brackets in them, not parentheses necessarily, but brackets.
What else is unique here? We are seeing file names with .xlsx. Now possibly it could be another Excel file, but let's say that .xls is probably unique or .xl. So it's highly unlikely that you would see that combination of characters anywhere else. Let's imagine that you have opened this file, you either forgot this or maybe you're a completely different user and you've seen by way of the Data tab, that there are links to other workbooks, you've seen that but you don't know where the links are.
So what might you do? You might be on any worksheet here, Right-Click and select all the sheets and then go to the Home tab, and the extreme right button, Find and Select, Find and let's look for all occurrences of .xl. Find all of them. In this example, there's only one so we see that there and we see the address. In different situations, you might see a pretty large list here. All cells that have the .xl combination are appearing here in this list.
So that helps us track these down. Now, links are one way. In this workbook, we've got a linkage formula, one or more, getting data from another workbook. Suppose we open the other workbook. I'll go back to file and open Regional Sales. Does this workbook have any formulas that get data from other workbooks? Data tab, Edit Links is gray, we cannot select it. There's no indication in this workbook that there are formulas elsewhere using this data so the linkage concept is based on the idea that in those workbooks where there are linkage formulas we can find them, but there's no way, when looking at a source workbook, that we can track the link in the opposite direction.
It gets a little tricky at times when you're trying to explain this to others but in our example here, we've got two workbooks, the other workbook as I press Ctrl+Tab and go back to it, has one or more, in this case only one, linkage formulas that are getting data from another workbook. In any workbook that gets data from other workbooks by way of formulas, we can those track formulas on the Data tab, the Edit Links command.
There are currently no FAQs about Excel 2013 Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.