Creating hyperlinks in Excel

BY J. CARLTON COLLINS, CPA

Q: My accounting software enables me to export my customer data to Excel, but the customers’ email addresses do not show up as clickable hyperlinks. I must edit each email address one at a time to delete the leading apostrophe to convert the text-based email address to clickable email addresses. Is there an easier way to do this?

A: Excel 2010, 2007, and 2003 provide a hyperlink function that converts text to a clickable hyperlink. In the example below, I entered =HYPERLINK(L2) in cell M2, which converts the text-based data in cell L2 into a clickable hyperlink. I then copied this formula (in cell M2) down to convert the other text-based email addresses to clickable email addresses.

Caution: Using the Copy, Paste Values commands to convert the =HYPERLINK formula to regular text removes the hyperlink.

RESOURCES

Keeping you informed and prepared amid the coronavirus outbreak

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.

VIDEO

Excel walk-through: Sparklines

Want to liven up your spreadsheets with some color and graphical elements? Kelly L. Williams, CPA, Ph.D., shows how to use Excel sparklines, which illustrate data trends and patterns via small charts that fit in a single Excel cell.