Business/Office Professionals eTips

View your chart and its underlying data simultaneously (Excel 2000/2002/2003/2007/2010)

Sometimes you want to have your cake and eat it too! When you chart data in Excel, you don't need to abandon your raw data. Here's how to quickly display a data table below your chart.

To display a data table with your chart:

1.    Launch Excel and open any workbook with an existing chart.

2.    Right-click on the chart area and choose Chart Options from the resulting shortcut menu.

3.    Click on the Data Table tab.

4.    Select the Show Data Table check box.

5.    Deselect the Show Legend Keys check box if you'd prefer to hide your series' information; otherwise, leave the check box selected.

6.    Click OK to display the data table below your chart

Flag an outgoing message so the recipient receives a reminder (Outlook 2000/2002/2003/2007)

Let's say you want to help an email recipient remember to review an email that you're sending. You can flag that message and cause the recipient to receive a reminder about reviewing that email message. To do so:

1.    Launch a new Message form, and then choose Actions | Follow Up | Add Reminder (Actions | Follow Up in 2002; Actions | Flag For Follow Up in 2000). If you have Microsoft Word set as your email editor, click the Message Flag button on the form's toolbar instead.

2.    Make a selection from both the Flag To and Due By dropdown lists in the Flag For Follow Up dialog box. Then, click OK.

Apply alternate row shading that updates automatically (Excel 2000/2002/2003/2007)

Worksheets often span hundreds or thousands of rows, which can wreak havoc on your eyes. Alternate row shading makes it easier to read large data tables, but if you manually shade rows, any changes you make to the data later will cause problems with your shading.

Here's a quick way to apply row shading to every other row in your data using conditional formatting. Just follow these steps:

1.    Select the data range to which you want to apply alternate row shading.

2.    Choose Format | Conditional Formatting from the menu bar to open the Conditional Formatting window. (In 2007, click on the Home tab and then click on Conditional Formatting in the Styles area, and choose New Rule to open the New Formatting Rule window.)

3.    Select Formula Is from the dropdown list. (In 2007, select Use A Formula To Determine Which Cells To Format.)

4.    In the corresponding text box, enter: =MOD(ROW(),2)=1

5.    Click the Format button to open the Format Cells dialog box and select the Patterns tab.

6.    Select the color you want to use for your shading in the color palette and click OK.

7.    Click OK in the Conditional Formatting window (New Formatting Rule window in 2007) to apply the alternate row shading.

Automatically add flags to incoming messages for guaranteed reminders (Outlook)

Yes, there's a way to automatically add flags and reminders to incoming Outlook items: by creating a rule.

To begin a rule to add a flag to specific incoming messages:

1.    Click on the Mail icon in the Navigation Pane (click on the Inbox folder in the Folder List in 2000 and 2002).

2.    Choose Tools | Rules And Alerts from the Outlook menu bar (Tools | Rules Wizard from 2000 and 2002.)

3.    Click the New Rule button in the Rules Wizard dialog box (New in 2000 and 2002).

4.    In 2002 and 2003 only, select the Start From A Blank Rule option button in the Rules Wizard dialog box.

5.    Select Check Messages When They Arrive from the top list box. Then, click Next.

To select the conditions of the rule:

1.    Select the From People Or Distribution List in the Conditions list box.

2.    Click on the People Or Distribution List link in the Rule Description dialog box.

3.    Enter the recipient in the Type Name Or Select From List text box in the Rule Address dialog box (or select from the list). Click the From button, and then click OK.

4.    Optionally, select the Marked As Importance check box from the same list box. Click on the Importance link in the Rule Description dialog box, and then select the desired choice from the Specify The Importance To Look For list box, and then click OK.

5.    Click Next. Select the Flag Message For Action In A Number Of Days check box or, in 2003, you can select the Flag Message With A Colored Flag check box.

If you select the Flag Message For Action In A Number Of Days check box:

1.    Click on the Action In A Number Of Days link in the Rule Description dialog box.

2.    Select a flag type from the Flag dropdown list. Then, select a number of days from the By dropdown list.

3.    Click OK to close the Flag Message dialog box. Click the Next button twice.

4.    Enter a name for the rule in the Please Specify A Name For This Rule text box. Then, click Finish to close the Rules Wizard dialog box.

If you select the Flag Message With A Colored Flag check box (Outlook 2003 only):

1.    Click on the A Colored Flag link in the Rule Description dialog box. (In 2002, click on the Action In A Number Of Days link.)

2.    Select a colored flag from the Flag Message Using This Color dropdown list.

3.    Click OK to close the Select Flag Color dialog box. Click the Next button twice.

4.    Enter a name for the rule in the Step 1: Specify A Name For This Rule text box. Then, click OK to close the Rules Wizard dialog box.

Easily add part of an open document to an Outlook item (2000/2002/2003/2007)

Let's say you have an open Word document and you'd like to include part of it in an email message. Would you copy the text from the Word document, launch a new Message form in Outlook, click in the message area of the form, and then paste the text? Well, here's a faster way:

1.    Reduce the size of the Word document window by clicking the Restore Down button in the top-right corner of the window.

2.    Highlight the information in Word that you'd like to add to your email message.

3.    Drag the highlighted text to the Mail icon on the Navigation Pane in 2003/2007 or drag it to the Inbox folder in the Folders List in any version.

A new Message form automatically launches with the text inserted into the message body area of the form and the cursor ready to go in the To field.

Use this same technique to add information to a Contact, a Task, a Note, a Meeting, or an Appointment form. Just drag the text to the respective icon in the Navigation Pane in 2003/2007 or to the appropriate folder in the Folder List in any version.

Mishandling your USB drive could cost you your presentation (PowerPoint)

The USB flash drive is a wonderful invention that lets you store a big hunk of data on a little trinket the size of a key or pen. These drives are often called data thumbs, thumb drives, pen drives, or USB sticks. As you've found, properly saving files to them or to any other USB file-storage device, such as a camera or digital music player, can get tricky. In our experience, there are no actual save issues with these devices and PPT files; rather, the danger most often lies in the save process itself.

A USB device is plugged into a free USB port on the PC. The PC must recognize the device as a new drive on the computer before it's usable. When you plug in a USB device, Windows presents the message that a new drive has been found, usually called Removable Disk. You can find the new drive by clicking My Computer and looking for the Removable Disk or a new drive letter. The best way to save files to a USB device is to right-click on the files or folders you want to save, choose Send To from the menu, and then select the Removable Disk drive letter.

To ensure that your data is saved, you must allow the computer to finish communicating with the new drive before removing it. The most common cause of data loss when saving to USB devices is improper drive removal. When you're done copying files, such as a PowerPoint presentation, to a USB storage device, it's important that you click on the USB icon that appears in the system tray and select Safely Remove. 

The computer then saves the data and removes the drive. When it's done, you'll see a confirmation message in a Safe To Remove Storage Device pop-up window. Only now should you physically remove the USB device from the PC; if you remove it before the PC says it's safe, you run a risk of losing files.

Don't get stuck with plain text—pick the email reply format of your choice (Outlook)

When you receive a plain-text email, you probably just hit reply and send a plain-text reply. But, you don't have to stick to simple formatting if you usually use rich text or HTML. Simply select a different format option from the Message Format dropdown list on the main menu of the email. (In 2000, select Format | Rich Text or Format | HTML.) Keep in mind, though, that your recipient may not be able to see all the extra formatting if he can't view rich text or HTML mail.

Change the ruler's measurement units for comfortable form and report design (Access 2000/2002/2003/2007)

The ruler is a great way to ensure that your reports print accurately or your form controls are right where you want them. However, you may not know exactly what measurement units the ruler in Access indicates. If you're more comfortable with the metric system (centimeters instead of inches), you can adjust your ruler permanently. Or, if you have Access 2003, you can simply input measurements in your preferred unit.

To change the ruler's units of measurement:

1.    Open your Windows Control Panel, which you can usually find on the Start menu.

2.    Double-click on the Regional And Language Options icon. If you're using Windows XP's Category view, just click on Date, Time, Language, And Regional Options and then click on Regional And Language Options.

3.    In the Regional And Language Options dialog box, select the Regional Options tab, if necessary.

4.    Click the Customize button to display the Customize Regional Options dialog box.

5.    Select the Numbers tab, if necessary.

6.    Choose either U.S. or Metric from the Measurement System dropdown list, and click OK to apply it. Of course, since the ruler is based on the Windows regional settings, this means that the change from one measurement system to another affects your entire operating system.

In Access 2003, if you want to enter measurements in a different unit but you don't want to make the change permanent, you can do so. For instance, follow these quick steps to set a report's left margin to six centimeters:

1.    Choose File | Page Setup from the menu bar.

2.    Click on the Margins tab, if necessary.

3.    In the Margins (Inches) panel, type 6cm in the Left text box and press [Enter].

Since the operating system is set to the U.S. measurement system, when you press the [Enter] key, Access automatically converts the six centimeters into its equivalent inches. If you're using the metric system as your default, you can perform the same automatic conversion by typing the text in after your measurement; Access converts the measurement from inches to centimeters.

Eliminate error values in your PivotTable (Excel)

If your PivotTable's source data contains errors, you'll notice those errors pop up in your PivotTable too. Here's how you can replace error values with blank cells in your PivotTable:

1.    Click anywhere within your PivotTable to view the PivotTable toolbar (or choose View | Toolbars | PivotTable from the menu bar).

2.    Click the PivotTable button on the PivotTable toolbar and choose Table Options from the resulting menu. Excel displays the PivotTable Options dialog box.

3.    Select the For Error Values, Show check box and leave the corresponding text box blank

4.    Click OK to return to your PivotTable.

Make The Best Use Of Worksheet Hyperlinks (Excel)

When you think of worksheet hyperlinks, you probably think of website links. There's another great use for hyperlinks in your workbook that you might not use to its full potential: internal links. You can create a hyperlink that takes you to another location within the same workbook when you click on it.

For example, if your workbook contains an Overview worksheet, which refers to more detailed data in other worksheets within the same workbook, you might want to allow users to quickly jump to that data.

To create an internal hyperlink:

1.    Launch Excel and open the workbook to which you want to add an internal hyperlink.

2.    Select the cell you want to make your hyperlink.

3.    Choose Insert | Hyperlink from the menu bar or press [Ctrl][K] to open the Insert Hyperlink dialog box.

4.    Click the Place In This Document icon on the left.

5.    Specify the text you want the linked cell to display in the Text To Display text box.

6.    Enter a cell reference for your hyperlink destination in the Type The Cell Reference text box and choose a worksheet from the list box. Note that any named ranges in the workbook are also available as hyperlink destinations.

7.    Click OK to create the hyperlink.

Now when a user clicks on the cell that contains the specified text, the active cell jumps to your specified destination.

Assign Categories To Calendar Entries For Easy Reading (Outlook)

If your calendar is filled with not only your work tasks, but also your personal appointments and your boss's meetings, you may look at it and wonder what you're supposed to do next.

If you're using Microsoft Outlook, you can assign categories to your appointments and then choose to view only one category at a time.

To add a category to a calendar item in Microsoft Outlook:

1.    Click on the Calendar icon in the Navigation Pane (click on the Calendar folder in the Folder List in 2000 and 2002).

2.    Launch a new Meeting or Appointment form, or double-click on an existing event to open it.

3.    Click the Categories button at the bottom of the form.

4.    Select the appropriate check box from the Available Categories list box, or enter a custom category in the Item(s) Belong To These Categories list box, and then click the Add To List button.

5.    Click OK to close the Categories dialog box.

To display your calendar by a category:

1.    Click on the Calendar icon in the Navigation Pane (click on the Calendar folder in the Folder List in 2000 and 2002).

2.    Choose View | Arrange By | Current View | Customize Current View from the Outlook menu bar (View | Current View | Customize Current View in 2000 and 2002).

3.    In the Customize View dialog box (View Summary in 2000 and 2002), click the Filter button.

4.    Click on the More Choices tab in the Filter dialog box.

5.    Click the Categories button. Then, select the category that you'd like to print from the Available Categories list box.

Keep your Format Painter active — even after you re-format a range (Excel 2000/2002/2003/2004/2007)

The Format Painter button offers a great service. It copies the formatting from your selected cell or data range and pastes it onto the next data range you select. You don't have to format a new area — including font, borders, number formats, etc. — from scratch.

But if you need to use the Format Painter to add your copied formatting to more than one non-contiguous data range, you'll find yourself wasting time. You probably select the cell with the desired formatting, click the Format Painter button, select one data range for the new formatting, go back to the original cell ... rinse, wash, and repeat.

But here's an insider tip: You don't have to go through this time-consuming, back-and-forth process. Just double-click on the Format Painter button. Then every range that you select gets the new formatting — until you click the Format Painter button again to deactivate it. You'll see that the paintbrush cursor sticks around even after you apply the formatting the first time.