Online and Web (Microsoft Excel)
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.
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.
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.
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.
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 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.
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.
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.
Hyperlinks can be helpful in some worksheets but bothersome in others.
Here’s how to get rid of any hyperlinks you don’t want.
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.
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.
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.
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.
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.
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.
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.
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.
Need to get rid of all the hyperlinks in a worksheet? It’s easy when you use this single-line macro.
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.
Need to add a hyperlink to a comment or note? It’s easy to do by following the steps outlined in this tip.
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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.