In this movie, I will show you how to create formulas that include links to cells and other worksheets and workbooks, as well as creating hyperlinks.
- [Instructor] Dashboards summarize data from other worksheets and possibly even other workbooks. In this movie, I will show you how to create formulas that include links to cells in other worksheets and workbooks, as well as creating hyperlinks that allow users to go to specific destinations of Excel workbooks or on the web to see more details about the data summarized in the dashboard. In this movie, I will work with two separate Excel workbooks. The first, which I have open now, is called CreateLinks, and the second is DepartmentExpenses.
So I'll go back to CreateLinks and start working. On the left side of this worksheet, I have four headers, beneath which I would like to add some data, so I want to add in data for Room Service Revenue, Guest Count, Average Daily Rate, and Department Expenses. I'll start in cell B3 by creating a link to the cell that contains the total of Room Service Revenue. With cell B3 selected, I will type an equal sign, and now I need to go to the worksheet that contains the cell to which I want to link.
So, I will go down to the tab bar and click RoomService. So here I am, I have Room Service Revenue by Month, then I'll scroll down, and I want the Total row for this Excel table. I'll click that and you'll see that the reference, rather than giving the cell, links to Department Expenses and then the Totals row, and the Revenue column.
And when I press Enter, I get the value of Room Service Revenue of $977,443. I can do something similar for Guest Count and Average Daily Rate. So I will click cell B6 for Guest Count then go to the CityData worksheet. I'm still in the CreateLinks workbook. And I have Guest Count by City and just making sure that is what I wanted, I'm going to click the dashboard again, yep, Guest Count.
So I'll type equal and then go to CityData and again, I want to scroll down. There I have my total for number of guests, press enter, and there's the value. I can do the same thing for Average Daily Rate and this time I'm going to show you a shortcut so you can get to the bottom of a data set. So I'll click cell B9, which is just below the Average Daily Rate header, type an equal sign, then click the CityData sheet tab, and then scroll over.
You can see here that I have 101.92 in cell L164. But let's say that I'm back up at the top. If I click cell L4 and then I press Control + Down Arrow, it moves me down to the bottom cell in this column. So basically, any time that you want to go to the bottom of a data set in a particular column, click any cell within that column, and then press Control + Down Arrow and you'll go to the cell that you want.
So everything looks correct, my value should be 101.92, I'll press Enter, and there it is. Next, I'm going to work with the Department Expenses workbook. In this case, I want to put the value in cell B12, so I'll click that cell, type an equal sign, as always, and then press Alt + Tab to move to the Department Expenses Workbook. The value that I want is in column E, which is the Expenses column of the table.
So I'll click any cell in that column and then press Control + Down Arrow and I move down to cell, looks like E100, which is the Total cell, and you can see the reference includes the name of the workbook, the name of the worksheet, which is SourceData2, corresponds to here, and then Department Expenses is the table, Totals row, Expenses column. And when I press enter, the value of $5,347,182 appears.
If you create a link to another workbook, you can manage that link from within Excel. So let's say, for example, that for some reason I wasn't getting updated data for Department Expenses, which goes to another workbook. I can take a look at the link by going to the Data tab and then, in the Connections group, clicking Edit Links. This shows me all of the external links and I see that I have the source of DepartmentExpenses.xls, which is a worksheet, and the location of the file.
I have updates to set to Automatic, which is always correct, and over on the right, you see that you can update the values, you can change the source, you can open the source document, break the link, or check the status. I'll go ahead and click Check Status just so you can see what that looks like. Status here says the Source is open and that's correct, the file is open. If for some reason you're unable to make a connection to your data source and it's throwing off your workbook, either by throwing an error or by showing a value you don't expect, you can click the link in the list here, and then click Break Link.
And what it will do is display the last value and delete the link to the other workbook. So it won't update, but you won't get the error. In this case, I don't want to make any changes, so I'll click Close. The last type of link that I would like to talk about is what's called a hyperlink. A hyperlink is what's used on the web to create connections between websites and web pages. You can do the same thing in Excel. So I will click cell D1, which has Website already there, and then I'll go to the Insert tab on the ribbon and click Hyperlink.
Now, I can create my hyperlink, there are four different types. You can go to an Existing File or Web Page, a Place in This Document. You can also Create a New Document by clicking the link or click E-mail Address. And if you do E-mail Address, then it will fire up your email program to send the message. In this case, I want to send the user to a web page, so I will ensure that Existing File or Web Page is highlighted, it is, and then in the Address box, I will type the address, http://www.landonhotel.com, and then click Okay.
And there I have my hyperlink and it looks like a regular hyperlink in blue and underlined. If you want to click a hyperlink, then move your mouse pointer over it and then click once to follow. You can see that information in the tool tip that appears. So I'll click the website hyperlink and I have Landon Hotel and there's the site. So, as you can see, there a lot of ways to create links in your dashboards.
They're all very useful, I encourage you to explore them all.
- Managing data using Excel tables
- Managing PivotTables and PivotCharts
- Summarizing table and PivotTable data
- Defining conditional formats
- Creating sparklines
- Sketching your dashboard layout
- Creating and managing cell links
- Linking cell contents to a shape
- Linking to a PivotTable cell
- Extending your dashboard using hidden rows