Online and Web
Often, the purpose of using Excel is to publish content online. Fortunately, Excel provides a few tools to make the transition from an Excel file to the Internet easy. In addition, you may want to pull information from online sources and place it within your worksheet. These tips explain what you can do with Internet-related content in Excel.
Tips, Tricks, and Answers
The following articles are available for the 'Online and Web' topic. Click the article''s title (shown in bold) to see the associated article.
Adding a ScreenTip
If you want people to know something about a hyperlink you added to your worksheet, one way to help them is to use ScreenTips. This tip explains what they are and shows you how to create them.
Adding Excel Information to a Web Page
Besides saving a worksheet as a complete Web page, you can also save smaller portions of your data to an existing Web page. This tip shows how easy such an operation is.
Automatic Text in an E-mail
When creating an e-mail address hyperlink using the Insert Hyperlink dialog box, Excel allows you to enter a subject for the message to be created by the link. If you want to also specify some body text, you need to use the techniques described in this tip.
Can't Use Hyperlinks
Before some features in Excel can function properly, you must have the correct permissions set for the user of the computer. This tip explores where to look to correct some of these problems.
Converting a Range of URLs to Hyperlinks
Converting a single URL into a hyperlink is easy. Converting hundreds or thousands can be much harder if you have to rely on manual methods. Here is a handy macro that can do the necessary conversion for you.
Converting to Hyperlinks in a Shared Workbook
When you enter a URL or e-mail address in a worksheet, Excel usually converts it to a clickable hyperlink. This doesn't occur, however, if the workbook is shared; the URL or address remains regular text. This tip discusses ways you can get around this behavior so that you have clickable hyperlinks in shared workbooks.
Creating a Dynamic Hyperlink
Want to create a hyperlink that will always display a different worksheet in your workbook? There are several ways to do it, each with their individual drawbacks. Here is the lowdown on the ideas.
Dealing with Hyperlinks that Won't Work
You can add hyperlinks to a worksheet and Excel helpfully makes them active so that when you click them the target of the hyperlink is opened in your browser. What do you do if some of the hyperlinks don't work, though?
Deleting a Hyperlink
Hyperlinks can be helpful in some worksheets but bothersome in others. Here's how to get rid of any hyperlinks you don't want.
Drop-Down List of Hyperlinks
Creating a drop-down list with Excel's data validation feature can be a nice touch for a worksheet. What if you want the drop-down list to include active hyperlinks? While Excel doesn't allow you to create this, there are a couple of workarounds you can use.
Dynamic Hyperlinks in Excel
Hyperlinks to many types of Web sites rely on passing parameters in the URL. Knowing this, you can construct a dynamic hyperlink by pulling information from various cells in your worksheet.
Extracting E-mail Addresses from Hyperlinks
If you have a list of hyperlinked e-mail addresses in a worksheet, you may want to extract the addresses from those hyperlinks and store them in the next column as plain text. You can do this using either a formula or a macro, as described in this tip.
Extracting Hyperlink Information
In Excel, a hyperlink consists of two parts: the text displayed for the link and the target of the link. You can use a macro to extract the information in the hyperlink and place it in your worksheet.
Extracting URLs from Hyperlinked Images
When copying information from the Internet to an Excel workbook, you may want to get rid of graphics but keep any hyperlinks associated with those graphics. This can be easily done by using the macro highlighted in this tip.
Extracting URLs from Hyperlinks
When you add a hyperlink to a worksheet, it consists of a minimum of two parts: display text and URL address. If you have a whole series of hyperlinks from which you want to extract the underlying address, this can be a tedious challenge. There are easy ways to get the required info, however.
Generating a Web Page
Want your worksheets to be available to others on the Internet? Excel provides a way you can save your data in HTML format, and it is as easy as saving your workbook in any other format.
Get Rid of Web Stuff
When you copy information from a Web page and paste it into a worksheet, you can end up with more than you bargained for. Here's how to get rid of the extraneous objects that may end up in the worksheet.
Getting Rid of All Hyperlinks
Need to get rid of all the hyperlinks in a worksheet? It's easy when you use this single-line macro.
Hyperlink Doesn't Match Cell Contents
When you add a hyperlink to a worksheet, over time and after doing a bunch of editing, what you see in the cell can get out of sync with the actual hyperlink. This tip discusses how this happens and what you can do about it.
Hyperlinks in Comments
Need to add a hyperlink to a comment or note? It's easy to do by following the steps outlined in this tip.
Hyperlinks in Shared Workbooks
Inserting a hyperlink into a workbook that is shared with others is not possible in Excel. Here's what you can do about it.
Hyperlinks No Longer Work in a Workbook
Hyperlinks can be a great timesaver and very convenient. Unless, of course, if they don't work as you expect. This tip examines a few possible causes of hyperlinks ceasing to work in a workbook.
Links to Hyperlinks
Hyperlinks in a worksheet can be helpful or essential, depending on the nature of your data. If you create a link to a hyperlink, you may wonder why the link doesn't act the same as the original hyperlink. Here's why.
Opening an HTML Page in a Macro
Excel allows you to open HTML pages within the program, which is great for some purposes. What if you want to open a browser window, however, from within Excel in order to display an HTML page? This tip highlights two methods you can use, within a macro, to perform the task.
Opening Sites in a Browser
You can store all sorts of information in a worksheet, including Web addresses. If you want to open those addresses in a browser, you can click on each of them individually, or you can utilize a macro. This tip examines different ways you can open a number of different addresses using macros.
Pasting a Hyperlink
Need a quick link within a document to some external data? You can paste information so that Excel treats it just like a hyperlink to that data.
Pasting HTML without Hyperlinks
Excel allows you to copy information from the web and paste it into a worksheet. Problem is, the pasting could take some time to complete. Why the pasting may be slow (and fixing it) could take some detective work, though.
Pasting Numeric Values in Other Programs
When you paste information from Excel into other programs, you may get more than you actually want. It is not unusual for the paste to include formatting characters such as dollar signs and commas. Here are some ideas for getting rid of those characters and pasting just the values into the other program.
Pulling Apart a URL
Excel provides a good number of worksheet functions that can help you pick apart text strings in various ways. In this tip those functions are put to use to pull apart a URL into its component pieces.
Retrieving Web Query Data without Interruption
If you use Excel's Web Query tools to grab data from a website, you may run into some problems if the site isn't available right away. This tip describes the problems and examines a way you can bypass the problem by interfacing with Internet Explorer from within an Excel macro.
ScreenTip for an Image
You can configure images in Excel so that if someone clicks on them, a macro is executed. You cannot, however, have a macro and a traditional ScreenTip tied to the same object. This tip explains how you can get around this limitation using two separate techniques.
Sending an E-mail when a Due Date is Reached
Wouldn't it be great if Excel could automatically e-mail you when a due date is reached? It can, if you are using Outlook and you implement the macro presented in this tip.
Sending Single Worksheets via E-mail
Got a single worksheet that you want to e-mail to someone, but don't want them to see the rest of the worksheets in the workbook? You can apply the techniques described in this tip to send just the information you want.
Setting Web Fonts
Is your worksheet information destined for a Web page? Here's how you can specify the fonts that should be used when Excel generates the HTML code for those Web pages.
Special Characters In Hyperlinks
Do you use special characters (such as the pound sign) in your worksheet names? If so, you could run into problems creating hyperlinks that target those files. Here’s how to get around this potential problem.
Specifying a Browser in a Hyperlink
Excel allows you to easily add hyperlinks to a worksheet. Click on it, and the target of the link is opened in a browser window. If you want to specify which browser is used to display the link, things get more complex.
Specifying Default Hyperlink Text
When you insert into a cell a hyperlink that references a file on your system, the text displayed by default matches the path and filename. If you want to change that default, you are out of luck, but there are workarounds.
Specifying Your Target Monitor
When you create a worksheet that is destined for viewing on the Web, you will want to specify the monitor resolution you expect users to have when viewing your data. This information is used by Excel as it generates HTML files from the worksheets.
Turning Off Hyperlink Activation
Does it bother you when you enter a URL and it becomes "active" as soon as you press Enter? Here's how you can turn off this activation so that your URLs remain as regular text.
Using Drag-and-Drop to Create a Hyperlink
If you open workbooks in two instances of Excel, you can use drag-and-drop techniques to create hyperlinks from one workbook to another. This is a quick and easy way to link your data together.