MrExcel.com - Your One Stop for Excel Tips & Solutions

Can Word Hyperlink to a Specific Sheet and Cell in Excel?

January 19, 2018 - by

Can Word Hyperlink to a Specific Sheet and Cell in Excel?

Great question today from Selina:

Can a hyperlink in Word open a specific Excel file, jump to a sheet and jump to a cell?

Selina

The syntax is fairly tricky, but it seems to work.

Well, it may not work like you want it to work, but it nominally works.

First, figure out the full path & file name of the Excel file. I use these steps:

  1. Press Alt + F11 to switch to VBA
  2. Press Ctrl + G to open the Immediate pane
  3. Type Debug.Print ThisWorkbook.FullName and press Enter
  4. On the next line, you will see the full path and file name.
  5. Select the resulting line and press Ctrl + C to copy to the clipboard.

Go to Word. Select where you want the hyperlink to appear. Press Ctrl + K to open the Hyperlink dialog. Press Ctrl + V to paste the results of #5 above.

At this point, the hyperlink would simply open the Excel file to the last active sheet and cell.

Selina's question, though, is how to have Excel jump to a specific sheet and location. Here is an example:

The answer from #5 above is: 

C:\FolderName\FileName.xlsx

You want to jump to the sheet called IncomeStatement

You want to jump to cell Z99.

The hyperlink would have to be: 

C:\FolderName\FileName.xlsx#'IncomeStatement'!Z99

That is *weird*! The syntax does not feel like Excel. In Excel, you would not have to use apostrophes around the sheet name unless the sheet name contained spaces or punctuation. But here, you have to use it.

In the video, I suggest a different approach. Go to the cell in Excel where you want to hyperlink to. Select the cell. Click in the Name Box to the left of the formula bar. Type a name with no spaces, such as JumpHere. Save the file. Your hyperlink in Word simplifies to: 

C:\FolderName\FileName.xlsx#JumpHere

Problem #1: Word shows an annoying warning box when you Ctrl + Click the hyperlink. They warn you that the Excel file may not be safe. To stop this warning, follow these steps:

  1. Get the path to where the Excel file is stored.
  2. Open Word. File, Options, Trust Center.
  3. On the right side, click Trust Center Settings.
  4. On the left side, choose the second choice -- Trusted Locations.
  5. Near the bottom, choose Add New Location. Paste in your path from #1. Click OK. Click OK.
  6. This step is not optional. Close Word. Re-open Word. I skipped this and had to say some four-words when they kept nagging me about the file being unsafe. But after closing and restarting Word, the message went away.

Problem #2: Excel is a Slacker. If you tell Excel to jump to Z99, you might expect Excel to scroll so that Z99 is the top left corner cell in the window. But Excel doesn't do that. Excel scrolls to, perhaps, F78. Why F78? Because if you can see F78 in the top left, then Z99 is visible somewhere in the window. It is looney. Technically, Excel is doing what you want... showing the people Z99. But it is not intuitive. I guess if I want Z99 near the top, I would have to cheat and ask Excel to go to AT123. (Put Z99 in the top left of your window and then figure out what is the last full cell you can see.) This workaround is not perfect, because it assumes everyone has the same size monitor and keeps Excel full screen.

Watch Video

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2182: Can Word Hyperlink to a Specific Sheet and Cell in an Excel File?

Hey, welcome back to MrExcel netcast, I'm Bill Jelen. Today, a question from Selina that I had no clue-- I've never done this before-- and I'm happy that I've figured it out, and I'm sharing it so that way you will know and also 2 years from now, when I have no clue how I ever did this, I can come back and find it as well. 

Alright, so here's what we have. We have a file: Podcast2182.xlsm. It is saved in a folder. Now, if you don't know what folder it is, I'm going to press Alt+F11; I'm going to press Ctrl+G for the Immediate window; I'm going to type "? Thisworkbook.FullName" and press Enter; and it's going to give me this thing here.  That's my full path and file name. So, if I just want to link to the file, not to a specific place in the file, we would come here to Word-- I just copied that, by the way-- and select what we want the hyperlink to be. I did a bad job of selecting that; MrWord.com could tell me a better way to select. Alright, Ctrl+K-- and the address, paste, is just that. Simple. 

Alright, but that's not what Selina wants to do. Selina wants to link to a specific sheet and cell address. Alright. So, here, I've already built this. What we're going to do is we're going to put the whole path and file name like before, then a pound sign or hash sign [#], and then the sheet name in apostrophes, even if it's only a single sheet [name]; close apostrophe; exclamation point; R99. Now, take that, Ctrl+C. Now, you've got to be really careful. I screwed this up at first. Putting xlsx there, make sure the extensions are right. Make sure all the path is right, and so on. So, let's come back to Word, and we're going to go to a specific sheet and cell; Ctrl+K; paste; alright, so, the whole thing, C:\FolderName\FileName.xlsx#'SheetName'!R99.

Alright, but, hey, my recommendation-- don't worry about that. Let's just do this.  Let's say that we want to come here and jump to this cell. What I'm going to do is I'm going to select that cell. I'm going to name it. I'm going to give it a name: "Jumptome," like that. Or, how about "Jumphere"? "Jumphere”-- that's a great name.  Press enter, like that, and we'll go back up to A1. Here, we'll be at A1, and we'll save this in the title card. Save; File; Close; come back to Word. And, for a jump to a Named Range, I think this one is even easier: Ctrl+K, alright, and in this one, we just put the whole workbook name path, workbook name path, pound sign, and the name of the named range (C:\FolderName\FileName.xlsx#NamedRange) like that. Click OK. 

Alright, so, we're in Word. The first hyperlink is just going to open the file. Ctrl+click.  They warn me; I'm sure I could get around this by saving it in a trusted folder. And, they open Excel just at the exact same spot where I had it before. File; close; and then try this one more linking to a specific sheet the question sheet R99. Ctrl+click, and they go to the question sheet and scroll to R99. Now, they didn't scroll so that way R99 was in the top left-hand corner cell, but at least it's in the visible window. Alright-- File; close; and then the last one, to a Named Range-- easier to set up-- you don't have to worry about the apostrophes-- Ctrl+click; yes; and it jumps to the report sheet "Jumphere" for test 1. 

For me, I'm going to say that's working. I'm sure if you had hoped it had jumped to this spot, like that, you're going to say that this is not working. But, that's the way life goes. To Microsoft, this is working. 

Well, hey, for tips like this tip-- 617 Excel Mysteries Solved-- check out this book: Power Excel with MrExcel. The 2017 edition is the latest version, even though we're already in 2018. The next time I'll update this will be in 2019, so you're safe to buy it now.

Alright, recap-- Selina wants to know if you can link from Word to a specific sheet and cell in Excel. Yes. If you build the link carefully enough. The format is C:\FolderName\FileName.xlsx#'SheetName'!A1-- or, whatever cell you want to go to.  Or, easier, if you set up a Named Range, C:\FolderName\FileName.xlsx#NamedRange.

Well, hey, I want to thank Selina for sending the question in, and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.

Alright, so, hey, let's just see if we can get rid of that irritating message. We go to File; Options; (I'm in Word for this-- probably could do it from either side); Trust Center; Trust Center Settings; Trusted Locations; I'm going to Add a New Location; type the location there; click OK; click OK; super annoying-- you have to close Word; reopen Word; and then the trusted locations are updated. Now when we click, Ctrl+click, it will not hassle you and ask you to go to a potentially untrusted location.

Title Photo: geralt / Pixabay