Microsoft Excel Tutorial: Embedding Excel Range in Microsoft Word
How to create a live link from an Excel workbook to a Word document.
This video is to accompany the article in the July 2023 issue of Strategic Finance Magazine: https://www.sfmagazine.com/articles/2023/july/excel-embedding-a-range-in-a-word-document
Table of Contents
(0:00) Problem Statement: Excel Table in Word Document as Live Link
(0:22) Name the range to be embedded
(1:32) Save as East, Change to West
(1:45) Copy from Excel & Paste as Link in Word
(2:20) Live link between Excel and Word
(3:01) After you close Excel workbook
(3:35) Re-opening Excel
(4:10) Re-calc Word with F9
(4:40) Re-create the immediate link between Excel and Word
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
How to create a live link from an Excel workbook to a Word document.
This video is to accompany the article in the July 2023 issue of Strategic Finance Magazine: https://www.sfmagazine.com/articles/2023/july/excel-embedding-a-range-in-a-word-document
Table of Contents
(0:00) Problem Statement: Excel Table in Word Document as Live Link
(0:22) Name the range to be embedded
(1:32) Save as East, Change to West
(1:45) Copy from Excel & Paste as Link in Word
(2:20) Live link between Excel and Word
(3:01) After you close Excel workbook
(3:35) Re-opening Excel
(4:10) Re-calc Word with F9
(4:40) Re-create the immediate link between Excel and Word
Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel
Transcript of the video:
Many times we have a long Word document and we need to insert a nice table that would be better if it was an Excel table.
So here I have an example where I have some data in Excel and I want to embed this in Word, but create the live link, so when I update the data on the left, the data in Word will update.
First thing we have to do is select the entire range that we plan on pasting.
And you'll notice that there's data outside the range and the data inside the range, it relies on this data. This is a filter or V stack with a filter function here. If I would change from east to central, let's say, this is automatically updating. And I'm not embedding this data in the Word document, but it'll all still work. It's really amazing.
So, first thing we have to do is name the range. Select the entire range that you want to have embedded in Word, like that. Click here in the name box, and we're going to type any name. There can't be any spaces in the name, but you can use an underscore. So I'll just type any name here.
It doesn't really matter. You can call this income_statement or balance_sheet or just balsheet, or whatever makes sense.
Now, I need to make sure that that name gets saved, and you also have to make sure that this workbook is saved somewhere that the Word document's going to be able to see it.
So we click save here. I'm also just going to note that we're going to paste this with the west region active. And that was done after the save.
I'm just trying to show an interesting feature here.
All right, so at this point now I don't have the name range selected anymore.
I want to make sure to select that name range. So I go to the name box, open the dropdown, and select my name. Control C to copy.
Over to Word, if we just do a default paste, that's a control V, there's going to be the wrong selection made for that paste. So we open up this little Control.
And the third item here is called Link and keep source formatting.
By doing that, we've now set up an amazing link between the Word document and the Excel document.
And I love the state that we're in right now, because anything that happens in Excel is going to immediately happen in Word. This is amazing to me the way that this works.
For example, if I would choose the central region, you see that the Word document automatically updates. If I would come over here and bold something and change the color, automatically happens.
I love this state, but it's very easy to break this state.
All right. Now, remember, when we save this document, it was showing west. And to test what happens, I'm going to close the Excel file and not save it at this point, which is really interesting because this is showing the central region and that green there in the background, and it's pointing to a spot on the hard drive. And that saved file does not reflect the central region. So over here, we'll do file, close. Do I want to save changes?
I'm going to say no, don't save. And you see that even though what's saved on the hard drive is west, I continues to show central here.
All right. Now let's see if what happens when we reopen the Excel file. I'm going to pause the video here while I open this and get them arranged again. Okay, check this out.
What's saved on the hard drive is the east region, and that word choose doesn't have the green color.
But what is, let's say cached, in the Word document is the central region and the green color. I'm a little disappointed here that I've broken this awesome link between the two. When I change the thing on the left in Excel, it's not changing on the right. Now, one way that I've found to fix this is to come here to Word, select everything, so control A.
And then it's very interesting, it's the same key we use in Excel to recalc, the F9 key.
And that will set up a link here, but I think it's a one-time link.
So if I would come here and now change from east to central, you see it's not working.
It's not the immediate reaction that I had initially.
So here's what I found on how to set up that immediate reaction.
We're going to keep that Excel file open on the left-hand side, over here in Word I'm going to save and then close, and then in Word, reopen. And it says that there's links that may to refer to files, and we say yes. And now it's back to the perfect state where if I change something on the left, it will instantly change on the right.
So, I know when you're building your annual report, or your quarterly report, or just trying to build some Excel, you may not need this immediate reaction to work.
You might just set it up once and not make any changes to Excel.
But I love how this does work, and it's important to understand how we can get it back into this immediate changing of the Word document in response to the Excel document.
All right, thanks for watching.
So here I have an example where I have some data in Excel and I want to embed this in Word, but create the live link, so when I update the data on the left, the data in Word will update.
First thing we have to do is select the entire range that we plan on pasting.
And you'll notice that there's data outside the range and the data inside the range, it relies on this data. This is a filter or V stack with a filter function here. If I would change from east to central, let's say, this is automatically updating. And I'm not embedding this data in the Word document, but it'll all still work. It's really amazing.
So, first thing we have to do is name the range. Select the entire range that you want to have embedded in Word, like that. Click here in the name box, and we're going to type any name. There can't be any spaces in the name, but you can use an underscore. So I'll just type any name here.
It doesn't really matter. You can call this income_statement or balance_sheet or just balsheet, or whatever makes sense.
Now, I need to make sure that that name gets saved, and you also have to make sure that this workbook is saved somewhere that the Word document's going to be able to see it.
So we click save here. I'm also just going to note that we're going to paste this with the west region active. And that was done after the save.
I'm just trying to show an interesting feature here.
All right, so at this point now I don't have the name range selected anymore.
I want to make sure to select that name range. So I go to the name box, open the dropdown, and select my name. Control C to copy.
Over to Word, if we just do a default paste, that's a control V, there's going to be the wrong selection made for that paste. So we open up this little Control.
And the third item here is called Link and keep source formatting.
By doing that, we've now set up an amazing link between the Word document and the Excel document.
And I love the state that we're in right now, because anything that happens in Excel is going to immediately happen in Word. This is amazing to me the way that this works.
For example, if I would choose the central region, you see that the Word document automatically updates. If I would come over here and bold something and change the color, automatically happens.
I love this state, but it's very easy to break this state.
All right. Now, remember, when we save this document, it was showing west. And to test what happens, I'm going to close the Excel file and not save it at this point, which is really interesting because this is showing the central region and that green there in the background, and it's pointing to a spot on the hard drive. And that saved file does not reflect the central region. So over here, we'll do file, close. Do I want to save changes?
I'm going to say no, don't save. And you see that even though what's saved on the hard drive is west, I continues to show central here.
All right. Now let's see if what happens when we reopen the Excel file. I'm going to pause the video here while I open this and get them arranged again. Okay, check this out.
What's saved on the hard drive is the east region, and that word choose doesn't have the green color.
But what is, let's say cached, in the Word document is the central region and the green color. I'm a little disappointed here that I've broken this awesome link between the two. When I change the thing on the left in Excel, it's not changing on the right. Now, one way that I've found to fix this is to come here to Word, select everything, so control A.
And then it's very interesting, it's the same key we use in Excel to recalc, the F9 key.
And that will set up a link here, but I think it's a one-time link.
So if I would come here and now change from east to central, you see it's not working.
It's not the immediate reaction that I had initially.
So here's what I found on how to set up that immediate reaction.
We're going to keep that Excel file open on the left-hand side, over here in Word I'm going to save and then close, and then in Word, reopen. And it says that there's links that may to refer to files, and we say yes. And now it's back to the perfect state where if I change something on the left, it will instantly change on the right.
So, I know when you're building your annual report, or your quarterly report, or just trying to build some Excel, you may not need this immediate reaction to work.
You might just set it up once and not make any changes to Excel.
But I love how this does work, and it's important to understand how we can get it back into this immediate changing of the Word document in response to the Excel document.
All right, thanks for watching.
