CFO
Menu
  • Accounting & Tax
  • Banking & Capital Markets
  • Growth Companies
  • Human Capital & Careers
  • Risk & Compliance
  • Strategy
  • Technology
  • Sign InSign Up
CFO
  • Conferences
  • Webcasts
  • Research
  • White Papers
  • Jobs
  • Training
  • Newsletters
  • Magazine
CFO
The Ongoing Evolution of FP&A
Global Survey Identifies 7 Key Insights
Square Root Costing: A Better Method
Square root costing is the only costing…
Does Diversity Pay Off?
CFOs Look to Quantify Inclusion Initiatives
  • Accounting & Tax
  • Banking & Capital Markets
  • Risk & Compliance
  • Human Capital & Careers
  • Growth Companies
  • Strategy
  • Technology
Spreadsheets

Excel Tip: The Missing Link

When pasted URLs don't become hyperlinks, try this solution.

Bill Jelen
January 29, 2014 | CFO.com | US - Comments: 1
share
Tweet
Print

Email this article

A reader posed the following question: “I pasted hundreds of web site addresses into Excel. They did not turn into hyperlinks (Figure 1). I found that I could select a cell, press F2, then Enter to make the hyperlink. But I don’t want to have to do that hundreds of times.”

fig1excellinks

Recommended Stories:
  • Will Treasurers Ever Dump Their Spreadsheets?
  • Excel Tip: Illustrate the Flow
  • Excel Tip: Fast Stepping

Fig. 1

Solution: Use the =HYPERLINK() function. Insert a blank column near your data. Use =HYPERLINK(A1,A1) (Figure 2).

Enter the formula and copy it down to all rows (Figure 3).

Copy the column of formulas and use Paste Values to paste back on top of the formulas. This will get rid of the hyperlink formula and leave you with only the hyperlinks. You can now copy those hyperlinks back over the original data

fig2excellinks

Fig. 2

Caution: This strategy works great for web addresses that have the leading http://. It will not work for cell A5 in Figure 3. A hyperlink will appear, but when someone follows the hyperlink, it will say the address is invalid. In that case, you could use this formula: =HYPERLINK (“http://”&A5,A5).

fig3excellinks

 Fig. 3

The following video walks a user through the solution:

CFO contributor Bill Jelen is an Excel MVP and the author of 35 books about Microsoft Excel. He is also editor-at-large of the CFO Excel Pro newsletter.

Post navigation

← Dell CFO Brian Gladden Resigns, Eyes a CEO Chair
The Role of Intuition in a World of Big Data →

One response to “Excel Tip: The Missing Link”

  1. raymond said 01/18/15 20:16pm

    Hi there,

    I have a excel with hyperlinks linked to another excel files.
    Why would I continually lose my hyperlinks after few days?
    I need to relinked all of them again.
    Can anyone help me?

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Advertisement

Popular Articles

  1. 10 Habits of Highly Effective CFOs
  2. No Mystery How to Restrain Health Costs
  3. Zero-based Budgeting Is Surging
  4. Pay Ratio Disclosures Mislead Investors
  5. No More Tax Deductions for Bad Actions
Advertisement
 

Topics

  • Accounting & Tax
  • Banking & Capital Markets
  • Human Capital & Careers
  • Growth Companies
  • Risk & Compliance
  • Strategy
  • Technology

Media

  • Videos
  • Whitepapers
  • Research
  • Magazine

Events

  • Conferences
  • Argyle Events
  • Webcasts

Services

  • Reprints
  • Back Issues
  • Mobile
  • Widgets
  • RSS

About CFO

  • About CFO
  • Editorial Staff
  • Press
  • Advertise
  • Contact Us

Want the Magazine?

Relax and unplug with our award-winning coverage.

Subscribe Now
Follow Us