Session 6: Working with Hyperlinks, Other Applications and Workgroups

1. Select A5, the cell containing the Background hyperlink. 2. Click: Insert->Links->Link 3. Click the ScreenTip button. 4. Add the screen tip text to the box: "Short description of Monaco's history" 5. Click OK, and OK again. 6. When you hover the mouse cursor over the hyperlink, the tip is displayed.

Lesson 6-1: Hyperlink to worksheets and ranges (file: Monaco-1) : *Add a screen tip the the Background hyperlink*

1. Select cell A5 on the Main Menu worksheet. 2. Click: *Insert->Links->Link.* - The Insert Hyperlink dialog appears. 3. Select Place in this Document from the left-hand navigation bar. 4. Select *Background* from the Or select a place in this document tree view 5. Click the OK button. - The text in cell A5 has been changed into a hyperlink:

Lesson 6-1: Hyperlink to worksheets and ranges (file: Monaco-1) : *Create a worksheet hyperlink from cell A5 in the Main Menu worksheet to the Background worksheet.*

1. Select cell *A12* on the *Main Menu* worksheet. 2. Click: *Insert->Links->Link.* - The Insert Hyperlink dialog appears. 3. Select *Place* in this Document from the left-hand navigation bar. 4. Select *Climate* from the Or select a place in this document tree view (Defined Names section). 5. Click the OK button. 6. Do the same for *Population* and *Life Expectancy.* 7. Test your hyperlinks.

Lesson 6-1: Hyperlink to worksheets and ranges (file: Monaco-1): *Make cells A12, A13 and A14 on the Main Menu worksheet into hyperlinks pointing to the range names that you created.*

1. Select cells *A32:A33* on the Geography worksheet. 2. Click: *Formulas->Defined Names->Create from Selection*. 3. Click the *OK* button 4. Using the same steps, create range names called *Population* and *Life expectancy* at birth for cells A3:A5 and A50:A54 on the People worksheet.

Lesson 6-1: Hyperlink to worksheets and ranges (file: Monaco-1): Create a range name called Climate for cells on the Geography worksheet, and range names called Life expectancy at birth and Population on the People worksheet.

- Demonstrates how to create hyperlinks to worksheets, and specific data, within the same workbook. - Demonstrates how to create screen tips to preview information.

Lesson 6-1: Hyperlink to worksheets and ranges: What is the purpose of this lesson?

1. Select cell A8. 2. Click: Insert->Links->Link. 3. Click: Existing File or Web Page in the left-hand navigation bar. 4. Select Current Folder and then the Monaco Economy-1 file in the central window 5. Click the OK button. 6. Click the Imports hyperlink in cell A8. - The Monaco Economy-1 workbook opens. Note that the hyperlink took you to cell A1 but the Imports data is actually in cells A74:A77.

Lesson 6-2: Hyperlink to other workbooks and the Internet (Monaco 2) : *Create a hyperlink from cell A8 in the Main Menu worksheet to the Monaco Economy-1 workbook.*

1. Select cell A17 on the Main Menu worksheet. 2. Click: Insert->Links->Link. 3. Click Existing File or Web Page in the Link to navigation bar. 4. Type *www.visitmonaco.com* into the Address box - Notice that Excel adds http:// in front of the address automatically. 5. Click the OK button. - Cell A17 is converted into a hyperlink pointing to the Monaco Government Tourist Office website

Lesson 6-2: Hyperlink to other workbooks and the Internet (Monaco 2) : *Make cell A17 on the Main Menu worksheet into a Web Page hyperlink pointing to the Monaco Tourist Office website at: www.visitmonaco.com*

1. Click: View->Window->Switch Windows->Monaco-2. You are returned to the Monaco-2 workbook. 2. If not already selected, move the cursor to cell A8 using the keyboard arrow keys (if you simply click the cell you will execute the hyperlink). 3. Click: Insert->Links->Link - The hyperlink address is visible in the Address box: *Monaco%20Economy-1.xlsx* - Notice that a hyperlink must have a %20 code in place of any spaces 4. Edit the hyperlink to reference the range A74:A77 on the Economy worksheet by changing the Address to the following: Monaco%20Economy-1.xlsx#Economy!A74:A77

Lesson 6-2: Hyperlink to other workbooks and the Internet (Monaco Economy-1): *Expand the hyperlink to include cells A74:A77*

1. Click the drop-down arrow on the right of the Quick Access Toolbar. 2. Click More Commands... from the drop-down menu. The Customize the Quick Access Toolbar dialog appears. 3. Click Commands Not in the Ribbon from the Choose commands from drop-down list. - A list appears showing all of the commands that are not normally available in Excel. 4. Select the Back command from the commands list 5. Click the Add>> button to add the Back command to the Quick Access Toolbar. 6. Click the OK button. - A back button is now displayed on the Quick Access Toolbar 7. Add a Forward button and a Document Location box to the Quick Access Toolbar 8. Test the new navigation controls.

Lesson 6-3: Hyperlink to an email address and enhance the browsing experience (file- Monaco-3): *Add a Back button to the Quick Access Toolbar*

1. Type your e-mail address into cell A20 on the Main Menu worksheet. 2. Press the <Enter> key. When you press the <Enter> key, the e-mail address changes into a hyperlink. 2. Click: Insert->Links->Link. 3. Type Feedback from Monaco Excel workbook into the Subject box. - When you click the hyperlink, you are taken to your default e-mail application with the destination e-mail address and subject lines already completed

Lesson 6-3: Hyperlink to an email address and enhance the browsing experience (file- Monaco-3): *Add an e-mail hyperlink to cell A20 on the Main Menu worksheet.*

1. Use Word to open The World's Best Selling Cars-1 (Word document) from your sample files folder. 2. Click just to the right of the sentence "Here is a list of the world's most successful cars to date:" 3. Press the <Enter> key to move to the next line 4. Click: Insert->Text->Object->Object... - The Object dialog appears. 5. Select Microsoft Excel Worksheet from the Object type list. 6. Click the OK button. - An empty Excel worksheet is embedded into the Word document. 7. Enter the following data into the worksheet: (See Lesson) 8. Return to word by clicking anywhere in the Word document's text (outside the Excel object).

Lesson 6-4: Embed an Excel worksheet object into a Word document: *Embed an Excel worksheet at the end of the word document* (The World's Best Selling Cars 1 Word Documment)

1. Use Word to open The World's Best Selling Cars-2 (a Word document) from your sample files folder (if it isn't already open). 2. Remove the AutoFilter from the table. - Click: Data->Sort & Filter->Filter. The filter buttons vanish. 3. Sort the table from bestselling to least selling. - Click anywhere in column C - Click: Data->Sort & Filter->Sort Largest to Smallest. 4. Format the values in column C so that they display one decimal place. - Select the values in column C. - Click Home->Number->Increase decimal

Lesson 6-5: Embed an Excel chart object into a Word document *Convert the range into a table, remove the autofiller from the table, sort the table from best selling to least selling, format the values in column C so they display only one decimal place* (Reference Only)

1. Click anywhere in the document's text to return to Word. 2. Click slightly to the right of the table. 3. Press the <Enter> key to move to the next line. 4. Type the text: ... and here is the same data as a chart. 5. Press the <Enter> key to move to the next line.

Lesson 6-5: Embed an Excel chart object into a Word document: *Add the text ... and here is the same data as a chart to the end of the document. (International Customers-1 Word Document) (Reference Only)

1. Double-click the lower Excel object to go back into Excel. 2. Select cells A1:C6. 3. Click: Insert->Charts->Pie->2-D Pie->Pie. The worksheet now contains a pie chart. 4. Right-click just inside the top-left corner of the pie chart. 5. Click Move Chart... from the shortcut menu. 6. Click the New Sheet option button. 7. Click the OK button. A portion of the pie chart is now visible in the Excel object 8. Re-size the Excel chart object to an attractive size.

Lesson 6-5: Embed an Excel chart object into a Word document: *Convert the duplicated Excel object into a chart.* (Reference Only)

1. Click the Excel object once to select it. Be careful to only click once. If you double-click you will go back into Excel. 2. Copy the Excel object. 3. Click on the line after the text: ... and here is the same data as a chart. 4. Paste. - An identical Excel object now appears in both places.

Lesson 6-5: Embed an Excel chart object into a Word document: *Copy the Excel object to the end of the document.* (Reference Only)

1. From Word, open International Customers-1 from your sample files folder. 3. Cursor below the last sentence. 4. Click: Insert->Text->Object->Object. - The Object dialog is displayed. 4. Click the Create from File tab. 5. Check the Link to file check box - This means that any changes to the Excel worksheet will immediately appear in the linked Excel object. 6. Click the Browse... button. 7. Navigate to the International Customer Rates-1 workbook. 8. Click the Insert button 9. Click the OK button. - The exchange rates are now displayed inside the Word document.

Lesson 6-6: Link an Excel worksheet to a Word document: *Insert the International Customer Rates-1 Excel Workbook at the end of the document as a linked object.*

1. Double-click the Excel object. Excel opens with the International Customer Rates-1 workbook. 2. Re-size the Excel and Word windows so that you can see both documents. 3. Change one of the exchange rates in the International Customer Rates-1 workbook. 4. Click once in the Word document window to make it the active window. 5. Right-click the Excel linked object in the International Customers-1 Word document. 6. Click Update Link from the shortcut menu Notice that the values update to match those in the workbook. 7. Values are normally only updated when you open the Word document. The Update Link command tells Word to refresh the data it is displaying

Lesson 6-6: Link an Excel worksheet to a Word document: *Test the link by changing values in the Excel workbook.*