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:=NameWhere:
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:
26 Responses to “Get busy this weekend, with OR XOR AND [Excel Homework]”
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
next solution for OR
=1*SUBSTITUTE (A1+A2;2;1)
regards
Stef@n
last solution for XOR
=1*SUBSTITUTE (A1+A2;2;0)
regards
Stef@n
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
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
I was obviously playing hooky at the beach during the bit-wise math lesson – you lost me at “Understanding bit-wise operations” 🙂
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))))}
@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.
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)
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)
@stefan,
I just couldn't get your solutions to work.
01010101010 + 01010101110 = 02020210120
what am i doing wrong?
@anup
...I got yours to work!
@Stephen - I get the same, but Stef@an's second solution for AND does work (at least for the test cases I used)
@ 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)
@Stef@n - You're answer is really smart, I never knew about the substitute function before. Great Work!
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
Here is a good resource for people wanting to learn binary and hexadecimal.
http://justwebware.com/bitwise/bitwise.html
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)
@ 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)
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)
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")
Just wandered by:
AND:
=SUBSTITUTE(A1+A2,1,0)/2
Clever, Shane. I like that.
[…] post http://www.excelhero.com/blog/2010/01/5-and-3-is-1.html for examples using Sumproduct, and http://chandoo.org/wp/2011/07/29/bitwise-operations-in-excel/ for examples using Text […]
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!
@Ronald
I'd suggest asking this in the Chandoo.org Forums
https://chandoo.org/forum/
Attach a sample file with an example of some data and describe what you want to achieve