INTRODUCTION

Until now, you have learned about the many ways you can copy and paste items in your workbooks. This takes you a step further by showing you how to copy and link your workbooks to other Office documents—for example, Word documents. When you paste data, the data becomes part of the new location and is no longer representative of any changes made to the original location. When you link data, the link can be updated so that any changes to the original location will display in the new location.

TIP

Switching applications

You can switch between open applications just as easily as you switch between workbooks. Simply click on the taskbar button for the application you want to switch to.

TIP

Using the Paste Link option

If you neglect to choose the Paste Link option on the Paste Special dialog box, the linked data won't be updated when you change a cell's contents.

TIP

Pasting as hyperlinks

Besides pasting cells as a link to another document, you can paste cells as a hyperlink. This will allow you to click on the pasted cells in the document and immediately be switched to the worksheet that contains the cells. To paste data as a hyperlink, click in the document where you want the hyperlink to appear, open the Edit menu, and choose Paste Hyperlink.

INTRODUCTION

If your job requires you to use your computer to perform repetitive tasks, chances are you can create macros to automate those tasks. For example, suppose you frequently create quarterly workbooks. In that case, you could create a macro that opens up a new blank workbook for you and adds the appropriate column heading information, as outlined in this task. You can create macros that accomplish just about any task in Excel—without knowing anything about programming. You simply use Excel's macro-recording feature to record the actions of a particular task; then, when you run the macro, Excel performs these actions for you.

TIP

Macro security

Sometimes macros can be corrupt, or people intentionally write macros that can wreak havoc on your computer files. Before you run a macro, make sure you know where it's from and that it is a trusted source. To be extra careful, choose Tools, Macro, Security to open the Security dialog box. Make sure the security level is set to High.

TIP

Everything records

Keep in mind that when the macro is recording, everything you do is recorded. For example, if you page down through a worksheet, that will happen when you run the macro later.

TIP

Adding macros to your toolbars

Macros are also items that you can add to your toolbars to make it easy to launch them. To do so, click the More Buttons down arrow on the right-most side of the Standard toolbar, and click the Add or Remove Buttons command. Then select the Commands tab on the Customize dialog box and choose Macros from the Categories list. Finally, choose the macro for which you want to create a button in the Commands list.

INTRODUCTION

You might find that the data you've entered into your worksheet would be useful in another Office application. For example, you might want to upload the regional sales data you've entered in to Excel to an Access database (which has more extensive reporting tools), or use it in a Word mail merge. Thanks to Excel's exporting capabilities, you can. In this task, you'll learn how to save your Excel worksheet data in a tab delimited format that other applications can use, and to see what that data looks like in another application (here, Word). (Delimiters are the items that separate one field of data from the next, and can be tabs, semicolons, commas, spaces, or other types.)

TIP

Exporting workbooks

To export an entire workbook, you must save each worksheet in the workbook individually if you want them to be tab delimited.

TIP

Exporting formatting

When you create a tab-delimited file for use in other applications, the data in the cells is the important information you are saving to export. The formatting in the cells (bold, blue, italic, and so on) is not saved because it is not necessary to the data.

TIP

Incompatible features

Some examples of incompatible features are data formatting (color, font, and so on) and numeric styles (currency and the like). If the numeric styles can't be saved as a number format, they may be saved in quotes as "text data" instead of numeric data.

TIP

Calculations lost

If there were any calculations performed in a cell to derive a number, only the actual number will be saved—not the formula, function, or cell references. So, if cell F2 ($8,800.00) is actually =SUM(D2*E2), only the $8,800.00 is saved.

INTRODUCTION

Another way to transfer data from one application to another is to import it. For example, you could import data from a Word document into an Excel spreadsheet; before you do, however, that Word data must be in a form that Excel can accept (for example, in a tab-delimited text file). In this task, you'll use the file you exported from Excel to Word in the preceding task to learn how to import data into Excel.

TIP

Alternate file locations

If necessary, click the down arrow next to the Look In field in the Select Data Source dialog box and select the folder that contains the file from the list that appears. To move up a folder level, click the Up One Level button on the dialog box's toolbar. If you double-click a subfolder, its contents appear in the list of files and folders.

TIP

Delimiters and qualifiers

Delimiters are the items that separate one field of data from the next, and can be tabs, semicolons, commas, spaces, or other types. Qualifiers are the items that qualify data as text, and can be double, single, or no quotes.

TIP

External Data toolbar

The External Data toolbar opens automatically when you import data in this fashion. You can use the buttons on the toolbar, or close the toolbar and work with the new data in your worksheet.

INTRODUCTION

To use your Excel workbook as a Web page, you must first save it in the correct file format—HTML. Excel enables you to save your workbooks in HTML format. In addition, you can reopen the Excel workbooks you save in HTML format in Excel, and then use Excel's various features to edit your Web page.

TIP

Saving workbooks versus worksheets

Instead of saving the default entire workbook as a Web page (the default), you can select the Selection: Sheet option button to save only the active worksheet as a Web page.

TIP

Custom views

Custom views are one element that will not save in an HTML file. If you try to save an Excel workbook containing custom views as a Web page, you may receive an Excel message about this; click Yes.

INTRODUCTION

Excel enables you to convert the workbooks and worksheets you create into Web pages (discussed in the previous task). Before you do, however, it's a good idea to preview the workbook or worksheet you want to save as a Web page so you can see how it will look.

TIP

Browser buttons and links

Notice that the Web browser acts just like it is displaying an active Web page. The workbook filename is in the title bar, and the Explorer bar buttons are active.

TIP

Online comments

If you have comments in a document that you publish to a Web page, you can move the mouse pointer over the comment indicator (the red bracket in the upper-right corner of the cell) to display the comment in a ScreenTip.

INTRODUCTION

Suppose you're creating a report for your customers to read, and want them to email you as soon as they finish it to let you know their thoughts. In that case, you can add an email link to the document, which they can use to start an email message addressed to you. Email links appear in a different color from the regular text and are underlined, making them easy to identify. In addition to making text or cell data into an email link, you can click any other object and create a link. For example, you could make a piece of clip art, a chart, or a comment a link.

TIP

mailto:

Notice that as soon as you type the @ symbol in the Address field in step 3, mailto: is placed at the beginning of the email address. This indicates that the link is an email link, not a Web address (URL).

INTRODUCTION

A URL is to a Web page what a street address is to a house—a unique way of identifying that page's location. Excel enables you to type URLs directly into your worksheets; when you do, Excel automatically establishes a link to the Web page that the link identifies. Anyone viewing your worksheet can then use this link to immediately view the page it references (see the task "Linking to the Web in a Document" later in this part to learn how). In addition to typing a URL to create a hyperlink, you can also type email addresses directly into your worksheets to create email links.

TIP

Removing hyperlinks

If you are typing a hyperlink into a worksheet as an example and don't want it to be an active link, move the mouse pointer over the link, right-click it, and select Remove Hyperlink from the shortcut menu.

INTRODUCTION

As you learned in the preceding task, a URL is a unique way of identifying a Web page's location. Excel lets you insert URL links directly into your worksheets in much the same way you insert email address links; after you insert a URL link, anyone reading the worksheet can use the URL link to view the page it references (see the task "Linking to the Web in a Document" later in this part to learn how).

TIP

Browsing the Web

If you cannot remember the URL you want to add to your worksheet, you can use the Insert Hyperlink button on the Standard toolbar to open the Insert Hyperlink dialog box. There, you can browse Web pages or use recent links to locate and add the correct address.

INTRODUCTION

There may be times when creating an elaborate worksheet that you will want to add a link that takes you or the reader to some other pertinent file. For example, you might add a monthly report presentation link to your sales worksheet so that anyone reading the sales worksheet can use that link to immediately view the monthly report presentation.

TIP

Locating recently used files

If the folder list in the Insert Hyperlink dialog box doesn't show the document you want to link to, try clicking the Recent Files option to locate the document. If it has been a while since you last used the file you're looking for, click the down arrow next to the Look in field to find the folder in which the document is stored.

INTRODUCTION

URL hyperlinks in your Excel worksheets can be used by the people viewing those worksheets to immediately start their Web browsers and visit the Web page to which the URL link refers (assuming they are connected to the Internet already).

TIP

Returning to your worksheet

If you have finished browsing the Web, you can click the Web browser's Close (x) button to close the browser and return to the original Excel worksheet, or click the browser's Back button until you return to Excel. In addition, you can click the workbook's taskbar button to make it the active window.

INTRODUCTION

Chances are, at some point you'll mistype a URL, email address, or document file location while creating a hyperlink and need to edit it. Alternatively, the URL, email address, or document file location associated with a hyperlink may change. Either way, you'll need to edit the hyperlink for it to function properly.

TIP

Clicking and holding

If you want to make an edit to a link directly within the cell that contains the link, you must first click and hold the mouse pointer in the cell. (A ScreenTip pops up with this information in case you forget.)

INTRODUCTION

If you refer to a particular URL, email address, or document file location in a report but don't want people to link to it, or if you decide you no longer want a particular hyperlink in your worksheet, you can remove it. When you remove a hyperlink, the link text or object remains, but clicking it has no effect.

TIP

Using the Undo command

If you remove a hyperlink but decide you want to put it back in your worksheet, click the Undo button on the Standard toolbar; the hyperlink will be restored.

INTRODUCTION

Excel has an E-mail button on its Standard toolbar that will let you immediately send a workbook as an email attachment, or a worksheet as the body of an email message. You will find this feature handy if you want to get quick feedback on a worksheet you are working on with a colleague.

TIP

Subject filename

Notice that the filename of the workbook or worksheet is added by default to the Subject line in the email message. You can change it by clicking in the Subject area and typing a different subject.

INTRODUCTION

If you like to dictate letters and memos, you'll be pleased to learn that Excel supports speech recognition. Before you can use this feature, however, you must set it up. You can do so in any Office application or by double-clicking the Speech icon in the Windows XP Control Panel. (In this task, you'll set it up from within Excel.) Before you begin, you'll need to connect a microphone and some speakers to your PC (refer to the instructions that came with those components for help).

TIP

Adjusting the microphone's sensitivity

Double-click the Volume icon in the system tray (on the far-right end of the taskbar, to the left of the time) to open a window that enables you to increase or decrease the microphone's sensitivity.

TIP

Mistakes and stops

If Excel stops highlighting text while you are reading, you probably missed a word, read too quickly, or pronounced something incorrectly. Simply take a couple breaths and begin again where the highlighting left off. If you don't recognize a word in the text, or aren't sure how to pronounce it, you can skip it by clicking the Skip Word button. To stop for a moment (for example, to answer the phone), click the Pause button. (A Resume button will appear for you to click when you are ready to continue.)

TIP

Getting more training

Click the More Training button in the screen in step 11 to read additional passages aloud, which will make the speech-recognition feature more accurate.

TIP

Setting up multiple speech profiles

Other people who use your computer can set up their own speech-recognition profiles. To do so, they must choose Tools, Speech to activate the Language bar, click the Tools button in the Language bar, and select Options from the list appears to open the Speech Properties dialog box. There, they click New button to initiate a new speech profile. Once multiple speech profiles are set up, you can select your own by choosing Tools, Current User, and the appropriate user name.

INTRODUCTION

After your speech profile is set up, you can do additional training at any time from any Office application to make Office's Speech Recognition feature more accurate. In addition, the more you use Speech Recognition to dictate information, the more accurate it becomes.

TIP

Displaying the Language bar

If the Language bar isn't visible, open the Tools menu and choose Speech; if the Tools button on the Language bar isn't visible, click the Options button in the Language bar and select Speech Tools.

TIP

Adding words

To add a word to the Speech Recognition dictionary, select Tools, Add/Delete Word(s) from the Language bar. Type the word, click the Record pronunciation button, and say the word to add it to the dictionary.

INTRODUCTION

The whole point of using Excel's Speech Recognition feature is so you can speak into your microphone, rather than typing, to input data into Excel. You do so using the Dictation command. Simply say the numbers or words you want to enter into a cell. To accept your data, say "enter," or say "backspace" as many times as needed to delete characters.

TIP

Adding punctuation and nonprinting characters

To add punctuation to your document, simply say the name of the mark you want to add. For example, to add a comma, say the word "comma." To move to a new line, say "new line"; to begin a new paragraph, say "new paragraph" or "Enter." You can also issue the following self-explanatory commands: "Tab," "space," "up," "down," "left," and "right."

INTRODUCTION

You can use the Voice Command feature in Speech Recognition to select buttons from toolbars, commands from menus, options from dialog boxes, and even links on the task pane. This task will demonstrate using selection commands for a menu and dialog box; read the tips in this task for how to select toolbar buttons and links on a task pane.

TIP

Talking to the task pane

You can choose commands on a task pane by saying the full name of the desired link. For example, in the New Document task pane in Excel, you could say "From existing workbook" to create a new workbook from an existing workbook.

TIP

Talking to the toolbar

To access buttons on a toolbar, you must first know the exact names of each button. Simply move the mouse pointer over each of the toolbar buttons to become familiar with them. When you are ready to access a toolbar button, click the Voice Command button (or say "voice command") and say the name of the button. For example, to begin typing underlined text, say "underline," then "dictation," and then say the words you want to appear underlined in your document. If you make any errors, you can always switch back to the "Voice Command" feature and say, "undo."