Building on the approach by @guillaume-jacquenot we can use apply to apply this to an entire Series.

df = pd.DataFrame({'Year': [2000, 2001, 2002 , 2003]})

For cleanliness, I wrote a helper method.

def make_hyperlink(value):
    url = "https://custom.url/{}"
    return '=HYPERLINK("%s", "%s")' % (url.format(value), value)

Then, apply it to the Series:

df['hyperlink'] = df['Year'].apply(make_hyperlink)

    Year    hyperlink
0   2000    =HYPERLINK("https://custom.url/2000", "2000")
1   2001    =HYPERLINK("https://custom.url/2001", "2001")
2   2002    =HYPERLINK("https://custom.url/2002", "2002")
3   2003    =HYPERLINK("https://custom.url/2003", "2003")

Probably you need a comma (,) instead of a semi-colon (;) in the formula. This is because Excel stores formulas in US-style syntax (see Non US Excel functions and syntax in the XlsxWriter docs).

When I run your formula through XlsxWriter I get an Excel warning about "We found a problem with some content in 'demo.xlsx'" and when I click on "yes" to recover the formula is zero, as your described.

Changing the semi-colon to a comma makes the program work without warning and as expected:

import xlsxwriter

workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

link = '=HYPERLINK(\"{0}/tr_events.php?triggerid={1}&eventid={2}\", \"{3}\")'.format('www.foo.com', 'abc', 'def', 'event1')

worksheet.write('A1', link)

# Or with a hyperlink format.
url_format = workbook.get_default_url_format()
worksheet.write('A2', link, url_format)

workbook.close()

Output:

The fully reproducible example provided below demonstrates how you would create a workbook, in which you are able to click on a particular cell and bring up another sheet in the same excel document.

In this example we use Xlswriter as the engine= parameter in pandas.DataFrame.to_excel(). This done to make use of some methods in the worksheet class of Xlsxwriter library, including worksheet.write_url()(link to docs).

import pandas as pd
import numpy as np

# Creating a dataframe 
df = pd.DataFrame(np.random.randn(10, 2), columns=list('AB'))
writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')

df.to_excel(writer, index=False, sheet_name='data')

workbook  = writer.book
worksheet = workbook.add_worksheet('sheetWithLink')

worksheet.write_url('A1',  'internal:data!A1')
worksheet.write('A1',  "A link to another sheet")


writer.save()

Expected Output:

def make_hyperlink(value):
    url = "{}"
    return '=HYPERLINK("%s", "%s")' % (url.format(value).split(' ')[0], url.format(value).split(' ')[1])

Il combine my url and price as prices = 'urls' +' '+'price' append it to main dataframe as ['price'] and apply next myDF['price'] = myDF['price'].apply(lambda x: make_hyperlink(x)) and it works! =)

This can be done with openpyxl, I'm not sure its possible with Pandas at all. Here's how I've done it:

import openpyxl

wb = openpyxl.load_workbook('yourfile.xlsm')
sheets = wb.sheetnames
ws = wb[sheets[0]]
# Deprecation warning
# ws = wb.get_sheet_by_name('Sheet1')
print(ws.cell(row=2, column=1).hyperlink.target)

You can also use iPython, and set a variable equal to the hyperlink object:

t = ws.cell(row=2, column=1).hyperlink

then do t. and press tab to see all the options for what you can do with or access from the object.

You can use the import xlsxwriter library to add hyperlinks. Speaking of hyperlinks, the example here shows some examples such as:

worksheet.write_url('A5', 'http://www.python.org/', tip='Click here')

But, if you don't want to manually write a line of code for each cell, then you can loop through and add the hyperlinks dynamically if you have a list of all the hyperlinks.

hyperlinks = ['a.com', 'b.com', 'c.com' ... etc.]

for i in range(1, len(hyperlinks)):
    worksheet.write_url(f'A{i}', hyperlinks[i-1], tip=df['column string'][i-1])

Your hyperlinks would obviously have to be in the correct order in the list, or you could create a dictionary that makes the text and hyperlink a key-value pair and use .map to bring the hyperlink into your dataframe as a column. Then you could sort the values and send the hyperlink to a list with hyperlinks = df['hyperlink'].to_list(). Then you could run the for-loop.

But, I think you will have to create a list or dictionary first.

Also, check out this answer for reading in date with hyperlinks using openpyxl:

Pandas read_excel with Hyperlink

And, this one for writing data with hyperlinks using pandas:

add hyperlink to excel sheet created by pandas dataframe to_excel method

Here is one way to do it using xlsxwriter as the Excel engine:

import pandas as pd

df = pd.DataFrame({'ID': [1, 2],
                   'link':['=HYPERLINK("http://www.python.org", "some website")',
                           '=HYPERLINK("http://www.python.org", "some website")']})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')


# Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Get the default URL format.
url_format = workbook.get_default_url_format()

# Apply it to the appropriate column, and widen the column.
worksheet.set_column(2, 2, 40, url_format)

# Close the Pandas Excel writer and output the Excel file.
writer.close()

Output, note that the second link has been clicked and is a different color:

Note, it would be preferable to use the xlsxwriter worksheet.write_url() method since that will look like a native Excel url to the end user and also doesn't need the above trick of getting and applying the url format. However, that method can't be used directly from a pandas dataframe (unlike the formula) so you would need to iterate through the link column of the dataframe and overwrite the formulas programatically with actual links.

Something like this:

import pandas as pd

df = pd.DataFrame({'ID': [1, 2],
                   'link':['=HYPERLINK("http://www.python.org", "some website")',
                           '=HYPERLINK("http://www.python.org", "some website")']})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('test2.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the worksheet handle.
worksheet = writer.sheets['Sheet1']

# Widen the colum for clarity
worksheet.set_column(2, 2, 40)

# Overwrite the urls
worksheet.write_url(1, 2, "http://www.python.org", None, "some website")
worksheet.write_url(2, 2, "http://www.python.org", None, "some website")

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output: