One-step hyperlink removal from any Microsoft Office document

TechRepublic’s Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!When you type an Internet address, an E-mail address or URL, Word automatically formats the string as a hyperlink. Most of the time, that’s what you’ll want, but not always. The truth is, removing a hyperlink is pretty easy: Right-click the hyperlink and choose Remove Hyperlink.

If you prefer a mouse-less method, you’re in luck. You probably already know about [Ctrl]+z. It’ll remove the hyperlink format and leave the text. As soon as Word applies the hyperlink format, press [Ctrl]+z to remove only the hyperlink format. Now, what could be simpler? Surprisingly, there is a simpler way. After Word applies the hyperlink format, press [Backspace]. Admittedly, you’re saving only one keystroke and I think for most of us, [Ctrl]+z is easier to remember, but the [Backspace] alternative is worth knowing (remembering it’s a different matter).

You can disable this feature easily enough, if you decide that’s really what you want. In fact, you might disable it temporarily, enter a some non-formatted values and then re-enable the feature when you’re done. That makes more sense than disabling it permanently. To disable the feature in Word 2003, do the following:

1. From the Tools menu, choose AutoCorrect Options.
2. Click the AutoFormat As You Type tab.
3. Uncheck the Internet and Network Paths With Hyperlinks option in the Replace As You Type section.
4. Click OK.

In Word 2007, do the following:

1. Click the Office button and then click the Word Options button.
2. Choose Proofing in the left pane.
3. In the AutoCorrect Options section, click the AutoCorrect Option buttons.
4. Click the AutoFormat As You Type tab.
5. Uncheck the Internet and Network Paths With Hyperlinks option in the Replace As You Type section.
6. Click OK twice.

In Word 2010, do the following:

1. Click the File tab and then choose Options under Help.
2. Choose Proofing in the left pane.
3. In the AutoCorrect Options section, click the AutoCorrect Option buttons.
4. Click the AutoFormat As You Type tab.
5. Uncheck the Internet and Network Paths With Hyperlinks option in the Replace As You Type section.
6. Click OK twice.

TechRepublic’s Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!

Office challenge: Can you foil Word’s AutoCorrect setting that capitalizes the first word in a sentence

By default, AutoCorrect capitalizes the first character in a sentence. Most of the time, that behavior is helpful. On the other hand, it can be a nuisance. For instance, imagine typing cc: several times a day only to have AutoCorrect correct it to Cc:. You can press [Ctrl]+Z to undo the AutoCorrect correction, but do that often enough, and even that becomes annoying. You can disable the feature, but do you really want to do that?

Can you inhibit AutoCorrect for some entries, without disabling the feature?

Last week we asked…

How do you enter an m-dash into a PowerPoint slide?

Many people use the [Ctrl]+[Alt]+- keystroke to enter an m-dash (or em-dash), but PowerPoint doesn’t recognize that keystroke combination. Mark Thibault responded quickly with an [Alt] key alternative: hold down [Alt] and enter 0151 on the numeric keypad. Jeremy Oh chimed in soon after with the same suggestion. It’s easy and it works.

There’s a second way to enter an em-dash. From the Insert menu, choose Symbol. In the resulting dialog box, choose (normal text) in the Font control. The em- and en-dashes are about three-quarters of the way down. Select the dash and click Insert. This method isn’t as easy as the [Alt] shortcut, but it´s good to know if you´re working with a laptop (that doesn´t have a numeric keypad).

Thanks to Mark Kaelin for the link to Wikipedia.com!

A Special Offer From Our Sponsor

One-step hyperlink removal

When you type an Internet address, an E-mail address or URL, Word automatically formats the string as a hyperlink. Most of the time, that’s what you’ll want, but not always. The truth is, removing a hyperlink is pretty easy: Right-click the hyperlink and choose Remove Hyperlink.

If you prefer a mouse-less method, you’re in luck. You probably already know about [Ctrl]+z. It’ll remove the hyperlink format and leave the text. As soon as Word applies the hyperlink format, press [Ctrl]+z to remove only the hyperlink format. Now, what could be simpler? Surprisingly, there is a simpler way. After Word applies the hyperlink format, press [Backspace]. Admittedly, you’re saving only one keystroke and I think for most of us, [Ctrl]+z is easier to remember, but the [Backspace] alternative is worth knowing (remembering it’s a different matter).

You can disable this feature easily enough, if you decide that’s really what you want. In fact, you might disable it temporarily, enter a some non-formatted values and then re-enable the feature when you’re done. That makes more sense than disabling it permanently. To disable the feature in Word 2003, do the following:

1. From the Tools menu, choose AutoCorrect Options.
2. Click the AutoFormat As You Type tab.
3. Uncheck the Internet and Network Paths With Hyperlinks option in the Replace As You Type section.
4. Click OK.

In Word 2007, do the following:

1. Click the Office button and then click the Word Options button.
2. Choose Proofing in the left pane.
3. In the AutoCorrect Options section, click the AutoCorrect Option buttons.
4. Click the AutoFormat As You Type tab.
5. Uncheck the Internet and Network Paths With Hyperlinks option in the Replace As You Type section.
6. Click OK twice.

In Word 2010, do the following:

1. Click the File tab and then choose Options under Help.
2. Choose Proofing in the left pane.
3. In the AutoCorrect Options section, click the AutoCorrect Option buttons.
4. Click the AutoFormat As You Type tab.
5. Uncheck the Internet and Network Paths With Hyperlinks option in the Replace As You Type section.
6. Click OK twice.

Reclaim space in Microsoft Excel by changing text alignment

My Microsoft Excel users make many of the same requests, and one thing I hear often is I want to see my whole spreadsheet in one screen. Scrolling to the right to view just a column or two annoys some people. I happen to be one of these people, and I extend that irritation to web sites that force me to scroll just a tad to see additional information or click a button to continue. So, I empathize with this particular request.

Now, you can’t help a user who has dozens of columns. You can create ranges and show them how to use F5 to quickly access a specific area without scrolling—your users will love you for it. Unfortunately, you’re never going to get that much data on screen in a readable format.

For those with smaller spreadsheets, there are several ways to reclaim space, but they all require specialized tweaking and that takes time and an expertise your users might not have. In addition, if users are dealing with limiting conventions, you might have to alter the sheet yourself. On the other hand, if you’re looking for a quick fix that users can apply themselves, show them how to angle their header labels.

First, ask the user if the heading labels are considerably longer than the actual data. Often, we waste screen space on displaying these long headings. There are at least two traditional, but time-consuming methods for reclaiming some of that wasted space:

• You can reduce the size of the heading labels, but you have to visit each one and often, you really can’t change the headings enough to warrant that route.
• You can wrap the heading labels, but that only works if the headings are comprised of multiple words and even then, this solution only goes so far.

Both methods are hit or miss—you can’t guarantee the results. Users could spend a lot of time experimenting and still not accomplish their goal.

An easier solution that usually satisfies with little effort is to angle the header text. This method works when the header labels are significantly longer than the actual text. You reduce the column widths to accommodate the data and angle the header labels, accordingly.

At 100% zoom, the following spreadsheet extends off-screen. Also notice that the headings are all single words so they shouldn’t wrap. Keeping the labels intact would require even wider columns. Your first thought might be to use the Auto-Fit column width, but the resulting column widths will be inconsistent, which is a visual distraction in and of itself. (Of course, in this simple example, the obvious solution is to abbreviate the months, but for the sake of the example, please play along—that won’t always be the case.)

The first step is to reduce the column size. A column width of 4 is adequate and it certainly gets all the data on screen. (Make sure that the column width is large enough to accommodate any possible value.) As you can see, the header labels look even worse than they did before, but that’s quickly fixed.

Once you’ve reduced the column width, you’re ready to angle the headings as follows:

1. Select the header values (A1:M1).
2. Right-click the selection and choose Format Cells.
3. Click the Alignment tab. By default, the Degree orientation is 0%.
   
4. Change the Degree setting to 65. Between 45 and 85 usually yields the best results. The highest setting is 90. Lower settings require a larger column width.
5. Click OK.

The result is still a bit of a mess, but easily fixed. Just drag the row header cell down until the row height setting is large enough to display the text.

There’s one more benefit—you can increase the zoom and still see everything on one screen. At 100%, you couldn’t see December. Now, not only can you see all the data, you can still see it all at 150%. (That matters to my old eyes!)

Angling header text is an easy way to reclaim a bit of screen space and it doesn’t hurt any that it looks cool. In addition, this is something you can train users to do for themselves.

TechRepublic’s Microsoft Office Suite newsletter, delivered every Wednesday, is designed to help your users get the most from Word, Excel, and Access. Automatically sign up today!

Reclaim space by changing text alignment

My Excel users make many of the same requests, and one thing I hear often is I want to see my whole spreadsheet in one screen. Scrolling to the right to view just a column or two annoys some people. I happen to be one of these people, and I extend that irritation to web sites that force me to scroll just a tad to see additional information or click a button to continue. So, I empathize with this particular request.

Now, you can’t help a user who has dozens of columns. You can create ranges and show them how to use F5 to quickly access a specific area without scrolling—your users will love you for it. Unfortunately, you’re never going to get that much data on screen in a readable format.

For those with smaller spreadsheets, there are several ways to reclaim space, but they all require specialized tweaking and that takes time and an expertise your users might not have. In addition, if users are dealing with limiting conventions, you might have to alter the sheet yourself. On the other hand, if you’re looking for a quick fix that users can apply themselves, show them how to angle their header labels.

First, ask the user if the heading labels are considerably longer than the actual data. Often, we waste screen space on displaying these long headings. There are at least two traditional, but time-consuming methods for reclaiming some of that wasted space:

• You can reduce the size of the heading labels, but you have to visit each one and often, you really can’t change the headings enough to warrant that route.
• You can wrap the heading labels, but that only works if the headings are comprised of multiple words and even then, this solution only goes so far.

Both methods are hit or miss—you can’t guarantee the results. Users could spend a lot of time experimenting and still not accomplish their goal.

An easier solution that usually satisfies with little effort is to angle the header text. This method works when the header labels are significantly longer than the actual text. You reduce the column widths to accommodate the data and angle the header labels, accordingly.

At 100% zoom, the following spreadsheet extends off-screen. Also notice that the headings are all single words so they shouldn’t wrap. Keeping the labels intact would require even wider columns. Your first thought might be to use the Auto-Fit column width, but the resulting column widths will be inconsistent, which is a visual distraction in and of itself. (Of course, in this simple example, the obvious solution is to abbreviate the months, but for the sake of the example, please play along—that won’t always be the case.)

The first step is to reduce the column size. A column width of 4 is adequate and it certainly gets all the data on screen. (Make sure that the column width is large enough to accommodate any possible value.) As you can see, the header labels look even worse than they did before, but that’s quickly fixed.

Once you’ve reduced the column width, you’re ready to angle the headings as follows:

1. Select the header values (A1:M1).
2. Right-click the selection and choose Format Cells.
3. Click the Alignment tab. By default, the Degree orientation is 0%.
   
4. Change the Degree setting to 65. Between 45 and 85 usually yields the best results. The highest setting is 90. Lower settings require a larger column width.
5. Click OK.

The result is still a bit of a mess, but easily fixed. Just drag the row header cell down until the row height setting is large enough to display the text.

There’s one more benefit—you can increase the zoom and still see everything on one screen. At 100%, you couldn’t see December. Now, not only can you see all the data, you can still see it all at 150%. (That matters to my old eyes!)

Angling header text is an easy way to reclaim a bit of screen space and it doesn’t hurt any that it looks cool. In addition, this is something you can train users to do for themselves.

Office challenge: How do you enter an m-dash in PowerPoint?

Most of you are probably familiar with the [Ctrl]+[Alt]+- keystroke combination that inserts an m-dash into a Word document. (That – sign is the minus sign on the numeric keypad; the minus sign on the regular keyboard won’t do.) Unfortunately, the combo doesn’t always work in PowerPoint—so how would you insert one of these dashes?

Last week we asked:

How would you provide reusable content for Outlook messages? That’s a big question and there are many ways to answer it! TexasJetter mentioned two built-in features: Add a signature line that’s included in all messages and using a custom form, complete with the shared content. L.m.zuelke uses signature lines for multiple blocks of content. Joaquim Amado Lopes suggested Quick Parts—a great way to have easy access to reusable content—and templates (one I didn’t think of when I posed the challenge). Several of you had a quick discussion about AutoCorrect, which is one of my favorite methods in all the Office applications. A few of you like to save reusable content in a Draft message, which is a creative solution for sure! It was a great challenge, as usual.

You covered a number of good solutions—if anyone has any trouble finding more information about reusable content, let me know and we’ll work through an example.

A dynamic SUMIF() function based on naturally occurring data

Excel’s SUMIF() function adds values that satisfy specific criteria. For instance, you might use this function to sum all the products sold by specific personnel. The sheet below shows this function returning the total of products sold by Bill, 120. The formula in E3

=SUMIF($A$2:$A$9,”Bill”,$B$2:$B$9)

sums the sold values in column B when the corresponding cell in column A equals the string “Bill.”

It’s likely that you’re familiar with this function and have even used it. The only drawback to using this function as I just did is the second argument, the criteria. As written, the criterion is static—the function will always look for the string Bill. If you only have a few criteria items to match, that’s not such a big deal. On the other hand, entering a large number of functions would be a tedious and error-prone task.

Whether you need just a few or several functions, there might be an easier way–the criteria is probably in your data! By referencing the data, you can make a dynamic function  and copy it as you would a regular SUM() function. In this case, the solution is to use the criteria as sheet labels.

Instead of “hard-coding” the criteria, refer to the new label cell in column D2, which contains the string Bill. The function now refers to cell D2 instead of a literal string

=SUMIF($A$2:$A$9,$D2,$B$2:$B$9)

The final step is to copy the formula to copy the function. Creating dynamic formulas and function is basic to Excel, but you might not consider using the naturally occurring data as I’ve done in the SUMIF() function.

Use WingDings to display special check box controls in an Access report

The following Access report shows the default display for a Yes/No field. In this case, an unchecked box means the product is still available; a checked box means the product has been discontinued. Most of the time, the default is adequate and you won’t want to change it. Occasionally, you’ll want a bit more flexibility.

The easiest way to display a check box other than the default is to use the WingDings font, which offers the following check box controls:

To create one of these check boxes, hold down the [Alt] key and press the appropriate numbers on the numeric keypad. Don’t use the numbers along the top of your keypad; you must use the numeric keypad for this technique to work. Then, apply the WingDings font to the resulting character.

When applying the WingDings fault to an Access report (2003, 2007, and 2010), choose two of the above characters: one to represent Yes (True) values and one to represent No (False) values. Now, let’s apply all that to an Access report:

1. Use any Access report you like, as long as it displays a Yes/No field. The example report is based on the Products table in Northwind, the sample database that comes with Access. This table contains a Yes/No field named Discontinued.
2. With the report in Design view, delete the Discontinued bound control. Just select the check box and press the Delete key.
3. Replace the default check box control with a label less text box.
4. Set the following controls for the new text box:
   Control Source = Discontinued (or the name of your Yes/No field).
   Font Name = WingDings
   Width = .25 (You might want to reset this later, but this is a good place to start.)
5. Select the Format property field and enter the appropriate characters to display your Yes and No check boxes using the form false;\true. For this example, hold down [Alt] and press 0253 on the numeric keypad. Then, enter a semicolon (;) and a backslash (\). Next, hold down the [Alt] key again and press 0254 on the numeric keypad.
6. Change the Font Size to 12
7. View the report in Print Preview.

As you can see, the No (or false) values now display an X instead of being empty. The Yes (true) check boxes still display checks, but the style is different. If you want to display just checks for Yes values and nothing for No values, leave the first Format component blank (step 5). In addition, experiment with other format attributes such as color.

Quickly produce different shades of the same color in PowerPoint

Sometimes you put a lot of effort in to producing just the right color by mixing red, green, and blue components. But, what do you do when you need different shades of the perfect color? You might put still more time and effort into producing just the right shades the same way, but there’s an easier way.

Once you have just the right color, use the transparency setting in objects to lighten or darken it. For instance, the background of the slide below is a custom mix: Red, 195; Green, 235; and Blue, 195. Next, I added a text a placeholder and added a few talking points. Unfortunately, the placeholder’s default background clashes with the slide’s background.

You could make the text placeholder transparent—sometimes that works well. Change this default setting as follows:

1. Right-click the placeholder and choose Format Placeholder from the resulting context menu. In PowerPoint 2007 and 2010, choose Format Shape from the context menu.
2. On the Colors and Lines tab, choose No Fill from the Color dropdown in the Fill section. In PowerPoint 2007 and 2010, choose Fill in the left pane and click No Fill in the settings section.
3. Click OK.

In this case, it is not particularly effective. The text items get lost in the saturated background.

Fortunately, it’s a simple matter to lighten the placeholder’s color just a bit:

1. Right-click the placeholder and choose Format Placeholder from the resulting context menu. In PowerPoint 2007 and 2007, choose Format Shape.
2. On the Colors and Lines tab, choose white from the Color dropdown in the Fill section. In PowerPoint 2007 and 2010, click Fill in the left pane (the default) and choose white from the Color dropdown.
3. Change the Transparency setting to 35%.
4. Click OK.

The placeholder now shows a lighter shade, with just a few clicks. No mixing and very little guesswork. You might have to experiment with the Transparency percentage a bit, but it won’t take much work to get it right.

Apply the same technique to create darker shades. Instead of white, choose gray, and use a higher Transparency setting. The text placeholder shown below uses gray and a Transparency setting of 88%. In this case, the difference between shades is more subtle because the background is already saturated quite a bit.

« Previous Page — Next Page »