POWER USER

Stop! Here's how not to go surfing when you work in Excel

John McCormick

Do you ever wish you could bypass Microsoft Excel 2000's presumption that whenever you type www and a period, you want to hot-link to a uniform resource locator?

The fast way around this is never to key in Web addresses that start with www, but that's not always practical.

Search on the phrase 'URL hyperlink' in the Microsoft Windows Help answer wizard, and you will find at least seven entries, all beginning 'Create a hyperlink,' but nothing for 'Change default.' Enter 'URL' in the Help index, and you see a vast amount of information about what to do if hyperlinks don't work correctly, but nothing about how to keep Excel from automatically creating them in the first place.

These Web hyperlinks can be extremely annoying because almost any editing on the cells will trigger an attempt by Microsoft's Internet Explorer to go to the Web page. It's hard to get any work done when you're continually interrupted this way.

Circumnavigation

Although I can't tell you how to stop the madness by changing the default, I do know of several ways around the forced hyperlink conversion on an individual or a block basis.

Mark a cell as text-only by adding an apostrophe before you enter the URL'a holdover trick from the glory days of Lotus 1-2-3.

If you imported the text or have opened someone else's worksheet, or if you are just unable to touch-type the apostrophe, you can remove a hyperlink by right-clicking on an individual cell and clicking the Remove Hyperlink menu item.

This works for single cells but can't be applied to more than one at a time because the menu option isn't available when blocks are selected.

Another solution is to press Ctrl-Z immediately after you enter the URL and move to another cell. This is the Edit, Undo feature that changes from Undo Typing to Undo Hyperlink when Excel has just created a hyperlink.

If, like me, you do little fancy formatting, you might have discovered that you can block text containing a lot of URLs and remove all formatting, which will also clear those unwanted hyperlinks. Do it by selecting a block, then going to the pull-down menus to choose Edit, Clear and Formats.

The preceding actions will make the cell contents show up without underlining or font color, but you haven't really removed the hyperlink, merely changed the way it's displayed. The hyperlink remains hidden.

If you place the cursor on the cell, you will see the complete URL, which activates Explorer if you click on the address or hit the Enter key.

If you use a lot of hyperlinks in Excel worksheets, there's an interesting option well-hidden under the File pull-down menu. Excel's default is to make all file hyperlinks relative unless you specify otherwise.

If you want a different default path, go to File, select Properties, click on the Summary tab and go to the 'Hyperlink base' line where you can specify a default path for hyperlinks. After that, you need only enter the filename.

Here's how it works. Enter, for example, d:\backup in the Hyperlink base box. Now go to an empty cell, press Ctrl-K or just drill down through the Insert menu, then enter the name of a file, for example, filename.txt, in d:\backup. Although the full path isn't displayed in the cell, pointing to the cell will show that Excel has indeed linked it to d:\backup\filename.txt.

The easy way out

Want to exit all Excel worksheets quickly from the keyboard? Select File and look at the options near the top of the menu. Press Esc, then immediately press Shift-F. When you take another look, you'll see that Close has changed to Close All.

If you choose Close All, and you haven't saved one or more of the worksheets since the last changes, you'll see an option window that lets you cancel or save the latest version.

By the way, this doesn't close Excel itself, just the open worksheets.

Try the same trick in Word. You'll see two different options, Close All and Save All.

Here's a tip directly from Microsoft Corp. The company touts it as 'the one Excel 2000 keyboard shortcut everyone should know.' Ctrl-Shift-* will automatically select the largest collection of data cells that is surrounded by blank cells.

It could be useful for editing.

John McCormick, a free-lance writer and computer consultant, has been working with computers since the early 1960s. E-mail him at poweruser@mail.usa.com.

inside gcn

  • artificial intelligence (ktsdesign/Shutterstock.com)

    Machine learning with limited data

Reader Comments

Please post your comments here. Comments are moderated, so they may not appear immediately after submitting. We will not post comments that we consider abusive or off-topic.

Please type the letters/numbers you see above

More from 1105 Public Sector Media Group