Microsoft Office Tutorial - live link to Excel range in Word.
Ashley wants to embed a range of Excel data in a Word document.
If the data in Excel changes (including adding more rows), she wants the Word document to update.
Annoying: If you use Insert, Object in Word, it captures the wrong range and truncates if you add more data.
My method: (1) Set the Print Range in Excel, (2) Select the Print range and Copy. (3) Paste to Word, (4) Open the paste drop-down menu and choose Linked Keep Source Formatting. This reliably works.
How do you solve this? Let me know down in the YouTube comments below.
Table of Contents
(0:00) Problem Statement
Ashley wants to embed a range of Excel data in a Word document.
If the data in Excel changes (including adding more rows), she wants the Word document to update.
Annoying: If you use Insert, Object in Word, it captures the wrong range and truncates if you add more data.
My method: (1) Set the Print Range in Excel, (2) Select the Print range and Copy. (3) Paste to Word, (4) Open the paste drop-down menu and choose Linked Keep Source Formatting. This reliably works.
How do you solve this? Let me know down in the YouTube comments below.
Table of Contents
(0:00) Problem Statement
Transcript of the video:
All right, in this episode we're going to talk about how to embed an expanding Excel range inward with live updates.
This question from Ashley, who was in my seminar at UCF a week and a half ago.
Ashley has a bunch of Excel ranges that she needs to get into Word, and the problem is the data in the range is changing, increasing the number of rows, and it's not being picked up.
So here's the set of steps that I found that successfully will do this.
The goal here is to get this entire range in Excel.
And this range even points to things that are outside of the range, like that formula there.
But I don't want this column or that column to show up.
So what I found is select the range, go to Page Layout, Print Area, Set Print Area.
Of course, make sure that the file is saved.
It has to be saved with a path and file name.
And then copy that entire area, Control+C.
We'll switch over to Word.
And so this is a test.
And then right here, I will paste, Control+V.
But you have to come here to this little dropdown and choose the third item over, which is Link, but keep the source formatting.
Click Okay.
All right, now this has also been saved.
I'll save it again.
All right.
And then the big test, can we insert more rows and maybe even change the numbers outside of the range and have that formula update.
So we'll switch back to Excel, and I'll just insert a few rows here, call it Extra.
And then out here, currently I have 17.
We will change some of these numbers.
Let's see, why did this formula not change?
Because I didn't include it in the range.
And then just added a little bit of color out here.
Home.
All right, good.
So all of those changes happened.
I haven't even saved the file yet.
If I go back, amazingly, I guess because they're both open, that number 29 has changed, the 11 has changed, the yellow has changed, it increased in size.
So that's all great.
It's a little frustrating that we had to follow these steps.
Ashley is convinced that in the past she's done something like this.
I'm going to switch over to another workbook here.
Okay, so this is called 2601_Just_One_Sheet.
It has just one sheet, no named ranges.
We currently have 17 as the formula.
I'll save that.
And then inward, I'll do Insert Object, Create from File, Browse, 2601_Just_One_Sheet.
Link to file, changes to the source file will be reflected in your document.
All right.
But they included columns that I didn't want included, and we're already truncating stuff at the bottom of the worksheet.
I'll undo, come back and see if setting the print range makes a difference.
Page Layout, Print Area, Set Print Area, Control+S to save, and then Insert Object, Create from File, Just_One_Sheet, Link to file.
Click Okay.
Yeah, it doesn't respect the print range at all, so it's so strange that just doing a copy and then a paste, changing to keep source formatting will correctly set up the link, and it grows.
But doing Insert Object does not.
If you're watching this video and you're an expert in this.
And you know some bulletproof way to do this.
Other than the copy and paste method.
Maybe there's some secret hidden setting somewhere.
Please, down in the YouTube comments, make sure to let me know.
I want to thank Ashley for sending that question in.
And I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
This question from Ashley, who was in my seminar at UCF a week and a half ago.
Ashley has a bunch of Excel ranges that she needs to get into Word, and the problem is the data in the range is changing, increasing the number of rows, and it's not being picked up.
So here's the set of steps that I found that successfully will do this.
The goal here is to get this entire range in Excel.
And this range even points to things that are outside of the range, like that formula there.
But I don't want this column or that column to show up.
So what I found is select the range, go to Page Layout, Print Area, Set Print Area.
Of course, make sure that the file is saved.
It has to be saved with a path and file name.
And then copy that entire area, Control+C.
We'll switch over to Word.
And so this is a test.
And then right here, I will paste, Control+V.
But you have to come here to this little dropdown and choose the third item over, which is Link, but keep the source formatting.
Click Okay.
All right, now this has also been saved.
I'll save it again.
All right.
And then the big test, can we insert more rows and maybe even change the numbers outside of the range and have that formula update.
So we'll switch back to Excel, and I'll just insert a few rows here, call it Extra.
And then out here, currently I have 17.
We will change some of these numbers.
Let's see, why did this formula not change?
Because I didn't include it in the range.
And then just added a little bit of color out here.
Home.
All right, good.
So all of those changes happened.
I haven't even saved the file yet.
If I go back, amazingly, I guess because they're both open, that number 29 has changed, the 11 has changed, the yellow has changed, it increased in size.
So that's all great.
It's a little frustrating that we had to follow these steps.
Ashley is convinced that in the past she's done something like this.
I'm going to switch over to another workbook here.
Okay, so this is called 2601_Just_One_Sheet.
It has just one sheet, no named ranges.
We currently have 17 as the formula.
I'll save that.
And then inward, I'll do Insert Object, Create from File, Browse, 2601_Just_One_Sheet.
Link to file, changes to the source file will be reflected in your document.
All right.
But they included columns that I didn't want included, and we're already truncating stuff at the bottom of the worksheet.
I'll undo, come back and see if setting the print range makes a difference.
Page Layout, Print Area, Set Print Area, Control+S to save, and then Insert Object, Create from File, Just_One_Sheet, Link to file.
Click Okay.
Yeah, it doesn't respect the print range at all, so it's so strange that just doing a copy and then a paste, changing to keep source formatting will correctly set up the link, and it grows.
But doing Insert Object does not.
If you're watching this video and you're an expert in this.
And you know some bulletproof way to do this.
Other than the copy and paste method.
Maybe there's some secret hidden setting somewhere.
Please, down in the YouTube comments, make sure to let me know.
I want to thank Ashley for sending that question in.
And I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
