Beam Me Up Scotty – Excel Hyperlinks

Share

Facebook
Twitter
LinkedIn

What are Hyperlinks ?

A Hyperlink is a reference to a document, a location or an action that the reader can directly follow by selecting the link.

Hyperlinks are used extensively on the Internet and are generally Words highlighted in Underlined Blue <– Like that.

The use of Hyperlinks in Excel has been extended to a number of areas and this includes:

  • Opening Files (of any type)
  • Opening Web Pages (Internet or Intranet)
  • Jumping/Navigating to locations within an existing document
  • Creating New Documents (Excel files only)
  • Sending Emails

Microsoft has added the ability to place Hyperlinks,

  • Directly on an Excel worksheet ,
  • Connected to a number of worksheet objects, including shapes, charts and wordart
  • Included as a worksheet formulas.
  • Programmatically using VBA

These 4 methods above will be discussed here.

Inserting Hyperlinks

As described above there are 4 methods for inserting hyperlinks in an Excel Workbook.

Directly on an Excel worksheet

There are 3 ways to insert a Hyperlink directly into a cell, either:

Right click on the cell and select Hyperlink; or

Use the Insert, Hyperlinks Tab; or

Use a Keyboard Shortcut – Ctrl K

Connected to a number of worksheet objects, including shapes, charts and wordart

You can also add a Hyperlink to many objects within Excel including Pictures, Shapes, Text Boxes, Word Art and Charts.

Right clicking a lot of these objects brings up the Objects Shortcuts Menu, select Hyperlink…,

or

Select the object, Use the Insert, Hyperlinks Tab; or

Select the Object and Use the Keyboard Shortcut – Ctrl K

Hint: Right Clicking on Charts Doesn’t Show the Add Hyperlink option, so Select the Chart and Ctrl K

Adding Hyperlinks using Worksheet Formulas.

Hyperlinks can be added using worksheet formulas.

=HYPERLINK( Link Location, Name)

Link Location: This is the path and file name to the document to be opened.

The Link Location can refer to a place in a document – such as a specific cell or named range in an Excel worksheet or workbook, or to a bookmark in a Microsoft Word document. The path can be to a file that is stored on a hard disk drive. The path can also be the path on a server or a URL, HTTP or FTP and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet. The Link Location can be a text string enclosed in quotation marks or a reference to a cell that contains the link as a text string.

Name: This is the text or value that is displayed in the cell.  The Name is displayed in blue and is underlined.

Eg:

Jump to a cell on Another sheet

=HYPERLINK(Sheet3!B3,”Monthly Budget”)

The above  will add a Hyperlink, titled “Monthly Budget” and link to Sheet3!B3 of the current workbook

Jump to a Named Range on Another sheet

=HYPERLINK(Budget,”Yearly Budget”)

The above  will add a Hyperlink, titled “Yearly Budget” and link to the Named Range “Budget” of the current workbook

Open a File on a network Drive

=HYPERLINK(“//Server01\01 Administration\Administration.docx”,”Open Admin File”)

The above  will add a Hyperlink, titled “Open Admin File” and link to the file at: //Server01\01 Administration\Administration.docx

Open a File on a network Drive at a specific bookmark

=HYPERLINK(“[//Server01\01 Administration\Administration.docx]Contents”,”Open Admin File @ TOC”)

The above  will add a Hyperlink, titled “Open Admin File @ TOC” and link to the Named Section “Contents” of the file at: //Server01\01 Administration\Administration.docx

Jump to a Web Page

=HYPERLINK(“http://chandoo.org/wp/”,”Goto Chandoo.org”)

The above  will add a Hyperlink, titled “Goto Chandoo.org” and link to http://chandoo.org/wp/

Send an Email

=HYPERLINK(“mailto:chandoo.d@gmail.com”,”Email Chandoo”)

The above will add a Hyperlink, titled “Email Chandoo” and send an email to chandoo.d@gmail.com

Adding Hyperlinks Programmatically using VBA

Hyperlinks can be added to a worksheet or a worksheet object programmatically using some simple code

Sheets(SheetName).Hyperlinks.Add Anchor:=Sheets(SheetName).Range(Range), Address:=””,  SubAddress:=”Address!Range“,  TextToDisplay:=Name

Where:

SheetName: The Name of the Sheet where the Hyperlink is to go

Range:  The Range where the Hyperlink is to go

Address!Range: The address and Range linked to in the Hyperlink

Name: The Display Name of the Hyperlink

Types of Hyperlinks

There are 5 Types of Hyperlinks which Excel offers, each is described below:

  • Existing File
  • Existing Web Page
  • Place in This Document
  • Create a New Document
  • Send an Email Link

Existing File

Select the existing File or Web Page icon in the Link to: area

Navigate to the existing file using the Look in: area of the dialog

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink

Use the Bookmark… button to jump to predefined Named Ranges and common Cell References dialog

Existing Web Page

Select the Existing File or Web Page icon in the Link to: area

Navigate to the existing file using the Look in: area of the dialog

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink

Place in This Document

Select the Place in this Document icon in the Link to: area

Type in Cell Reference using the Type in Cell Reference: area of the dialog or select a Defined Names in the Defined Names area

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink


Create a New Document

Select the Create New Document icon in the Link to: area

Type in the Name of the New Document in the Name of the New Document: area of the dialog.

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink

You can choose wether to Edit the File Now or Later in the When to Edit area

Send an Email Link

Select the Email Address icon in the Link to: area

Type in the Email Address in the Email Address: area of the dialog.

Add your Display Text in the Text to display: area

Add your Email Subject in the Subject: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink.

Editing Hyperlinks

Once you have a hyperlink established you can edit the hyperlink by right click on the hyperlink and select Edit Hyperlink

The Edit Hyperlink dialog will vary depending on the type of Hyperlink as described above.

Deleting Hyperlinks

Once you have a hyperlink established you can delete the hyperlink by right click on the hyperlink and select Remove Hyperlink


Hyperlink Uses

Hyperlink can be used for a number of uses as described above.

Tables of Contents

One common use of hyperlinks is the creation of Tables of Contents.

The construction of a Table of Contents page was discussed here Table of Contents

The construction of Tables of Contents can also be automated using some simple VBA.

So instead of reinventing the wheel I will direct you to The Microsoft Office Blog where Tables of Conents were recently discussed.

Table of Contents 1 or Table of Contents 2

Dealing with Lots of Hyperlinks

The following 2 posts at http://chandoo.org/forums have solved users problems and will easily be adapted to other Hyperlink issues

Find Dead Hyperlinks

http://chandoo.org/forums/topic/check-broken-external-hyperlinks

Edit Hyperlinks

http://chandoo.org/forums/topic/marco-for-editing-link-in-workbook

How have you used Hyperlinks?

How have you used Hyperlinks?

Let us all know in the comments below:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

26 Responses to “Get busy this weekend, with OR XOR AND [Excel Homework]”

  1. Stef@n says:

    first solution for AND
    The two numbers are in A1 and B1
    = SUBSTITUTE (SUBSTITUTE (A1+B1*9*9, 9, 1), 8, 0)
    regards
    Stef@n

  2. Stef@n says:

    next solution for OR
    =1*SUBSTITUTE (A1+A2;2;1)

    regards
    Stef@n

  3. Stef@n says:

    last solution for XOR
    =1*SUBSTITUTE (A1+A2;2;0)

    regards
    Stef@n

  4. Irvine says:

    Or you could make use of the VBA logical operators!

    Define the following as custom functions
    Public Function BITXOR(x As Long, y As Long)
    BITXOR = x Xor y
    End Function

    Public Function BITAND(x As Long, y As Long)
    BITAND = x And y
    End Function

    Public Function BITOR(x As Long, y As Long)
    BITOR = x Or y
    End Function

    and then use them such:
    A B =BITOR(A,B) =BITAND(A,B) =BITXOR(A,B)
    0101 0100 0101 0100 0001

  5. Stef@n says:

    an another solution for AND
    =1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)

    note:
    the binary numbers are in A1 and A2 !
    regards
    Stef@n

  6. Shair says:

    I was obviously playing hooky at the beach during the bit-wise math lesson – you lost me at “Understanding bit-wise operations” 🙂

  7. Anup Agarwal says:

    After looking at the above solutions, I find my solution silly, but still:

    For the following formulae,
    Row 1: headers,
    Row 2: OR
    Row 3: AND
    Row 4: XOR

    Column 1: Input 1
    Column 2: Input 2
    Column 3: Result

    OR
    {=SUM(IF(MID(A2,ROW(OFFSET($A$1,0,0,LEN(A2),1)),1)+MID(B2,ROW(OFFSET($A$1,0,0,LEN(B2),1)),1)>0,1,0)*10^(LEN(A2)-ROW(OFFSET($A$1,0,0,LEN(B2),1))))}

    AND
    {=SUM(IF(MID(A3,ROW(OFFSET($A$1,0,0,LEN(A3),1)),1)+MID(B3,ROW(OFFSET($A$1,0,0,LEN(B3),1)),1)=2,1,0)*10^(LEN(A3)-ROW(OFFSET($A$1,0,0,LEN(B3),1))))}

    XOR
    {=SUM(IF(MID(A4,ROW(OFFSET($A$1,0,0,LEN(A4),1)),1)+MID(B4,ROW(OFFSET($A$1,0,0,LEN(B4),1)),1)=1,1,0)*10^(LEN(A4)-ROW(OFFSET($A$1,0,0,LEN(B4),1))))}

  8. Hui... says:

    @Anup
    Please don't consider your solution silly
    Firstly, You are the 3rd person to submit an answer
    Secondly, The best formula/function is the one that you know and understand.

  9. Michael Azer says:

    I think I have a very tedious solution, which people won't have the patience to do except in small numbers.

    I used the same problem setup as "Anup Agarwal"

    AND =IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)

    OR =IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)

    =IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)

  10. Michael Azer says:

    Sorry my last post was totally messed up

    AND
    =IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)
    OR
    =IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)
    XOR
    =IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)

  11. Stephen says:

    @stefan,
    I just couldn't get your solutions to work.
    01010101010 + 01010101110 = 02020210120
    what am i doing wrong?

  12. Stephen says:

    @anup
    ...I got yours to work!

  13. Rich says:

    @Stephen - I get the same, but Stef@an's second solution for AND does work (at least for the test cases I used)

  14. Stef@n says:

    @ Stephen / Rich
    yes , you are right ! - only this works:

    OR
    =1*SUBSTITUTE (A1+A2;2;1)
    XOR
    =1*SUBSTITUTE (A1+A2;2;0)
    AND
    =1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)

  15. Michael Azer says:

    @Stef@n - You're answer is really smart, I never knew about the substitute function before. Great Work!

  16. Stef@n says:

    Thx Michael 🙂
    yes - it is simply easy 😉
    if you add 1 and 1 - excel calculate 2
    and then you have to substitute the 2 - new = 0 respectively 1

  17. Rob says:

    Here is a good resource for people wanting to learn binary and hexadecimal.
    http://justwebware.com/bitwise/bitwise.html

  18. Kyle McGhee says:

    Three that weren't asked for:
    NOT
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),1,0),3,1)

    EQV
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),2,3),1,0),3,1)

    IMP
    =SUBSTITUTE(SUBSTITUTE(A1+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,3),1,0),3,1),0,1),2,0)

    (was using Daniel Ferry's bitwise file to verify against)

  19. Q says:

    @ Kyle

    Not only takes one parameter and inverts 0 -1 and 1-0

    Took out the +A2
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,3),1,0),3,1)

  20. Great solutions!

    I'll add two:

    NAND =1*SUBSTITUTE (A1+A2,2,0)

    NOR=1*SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A1+A2,0,2),1,0),2,1)

  21. John Fuller says:

    This will work for binary numbers of any size (although the text format mask will have to have as many zeroes as there are digits in the longest addend)

    Assume binary #s are in C35 & C36, then add and format as text in C37:
    =TEXT(C36+C35,"000000000000")
    -sum- = 101112211112

    AND - SUBSTITUTE 0s for 1s in -sum-, then sub 1s for 2s
    =SUBSTITUTE(SUBSTITUTE(C37,"1","0"),"2","1")

    OR - sub 1s for 2s in -sum-
    =SUBSTITUTE(C37,"2","1")

    XOR - sub 0s for 2s in -sum-
    =SUBSTITUTE(C37,"2","0")

  22. Shane says:

    Just wandered by:

    AND:

    =SUBSTITUTE(A1+A2,1,0)/2

  23. Ronald Vonk says:

    Hi Chandoo,

    I am not (yet) really into bitwise calculation, but I am looking for a way to speed up my vba calculation with very big numbers. Would is ben convenient to use bitwise notation for this?

    Best regards,

    Ronald (the Netherlands)
    p.s. love your country!

Leave a Reply