How to Link Data in Multiple Excel Workbooks: A Step-by-Step Tutorial

How can you link data or formulas across multiple Microsoft Excel workbooks? How do you build an Excel workbook that updates from data in another workbook? What are the steps to link Excel data?
In this step-by-step Excel training (see chapter links below):
• Easily build formulas to link data from one Excel workbook to another.
• Update data in a linked or source workbook.
• View and edit links within a workbook.
• Break links you no longer need in an Excel workbook.
⌚VIDEO CHAPTERS (timestamps):
0:00 Introduction
0:25 Microsoft Excel workbook linking concepts
1:24 How to link from one workbook to another with a formula
6:02 Viewing links for external worksheets
8:03 Managing and editing links for external Excel workbooks
13:17 How to remove links from an Excel workbook
15:02 Wrap-Up / Closing
🎬LINKS to mentioned or related videos:
• Video name: How to Link Data Across Worksheets in an Excel Workbook: • Excel Pro Tips: Linkin...
• Playlist with all my videos on Microsoft Excel: • How to Use Excel; Micr...
✅MORE Excel Tips & Tricks:
Discover more Microsoft Excel lessons and download a free Excel keyboard shortcuts handout at TheSoftwarePro.com/Excel.
Be More Productive with Software!
#ExcelTips #Productivity #Excel
📩NEWSLETTER:
How much time are you wasting trying to get the answers you need for your Microsoft applications? Let's change that! For more time-saving tips for Excel and other top software apps, join our free software tips newsletter at TheSoftwarePro.com/Tips AND get access to valuable keyboard shortcuts handouts.
🔽CONNECT WITH ME:
• Web site: TheSoftwarePro.com
• LinkedIn: / dawnbjork
• Twitter: / thesoftwarepro
• Facebook: / thesoftwarepro
• Instagram: / thesoftwarepro
🔔SUBSCRIBE TO THIS CHANNEL ON KZbin:
kzbin.info...
🙌🏻SUPPORT THIS CHANNEL:
Hit the THANKS button in any video!

[@AndrewDonald-ce3ir]

excellent method of teaching, very thorough, well composed in delivery!

[@thesoftwarepro]

Glad it was helpful!

[@LCasson2417]

This is very helpful as I learn to utilize Excel more for my work. Thank you!! I subscribed!

[@irisxie7210]

Thank you so much for sharing! It's very helpful.

[@tiffanyearnest7082]

You are a SAINT! Thank you, thank you, thank you! I needed a master sheet with multiple tabs to feed each tab individually onto their own sheets, but after days of research, it seemed like Excel couldn't do it. I finally stumbled on linking, but all of the videos I found weren't making sense. This one finally accomplished what I needed! For anyone who may need this info: you can also link entire columns - when you do = and then click on the source workbook, you can click and drag to select the whole column, hit enter, and it'll feed the whole thing over to a new sheet.

[@thesoftwarepro]

I'm thrilled that this was helpful for your project, Tiffany! And thanks for sharing the great tip about linking an entire column. A big timesaver!

[@bkim202]

Thank you so much for this video Dawn! Your information was so easy to understand and I feel much more confident with moving forward in organizing my data. This is going to help so much :D!

[@thesoftwarepro]

Glad it was helpful to work with your Excel data!

[@jusuflazami9580]

SUPER CLEAR AND LOVELY EXPLAINED!!

[@thesoftwarepro]

Thank you! I'm glad it was helpful.

[@mproyuclan]

Thank you Dawn, this is such a great video and is really going to make my life a lot easier trying to capture clinical trainee activity metrics 🙂👋👋👋

[@thesoftwarepro]

I'm thrilled that this training is helpful so you can more easily focus on your priorities. 😁

[@vilcheesey]

thank you so much, you led me in the right direction.

[@thesoftwarepro]

You are so welcome

[@techdelasabana]

Thank you Dawn. Very helpful information, clearly explained. Now, getting data from a cell is one thing and getting it from a data table is another. Are there any limitations connecting data tables between two workbooks? Greetings from Colombia.

[@hernanasuncionpepeng2021]

Great video! How can I link multiple input/output workbooks to 1 data & calculation workbook? Or does it have to be a 1 to 1 link of input/output to data & calculation workbook?

[@johnmillstead8396]

Good day. Thanks for this great video. I have a question: I have about 50 budget workbooks that I need to roll into one master. Is there a way to save the workbooks into a folder and have the master add all the workbooks together? I need to add each department's information for account and month: General Fund, Donations, Jan, Feb, Mar...Total. Thanks for any help you can offer.

[@naderfawzy5958]

good value to lenth ratio

[@adstristar]

Thank you. I have a similar problem. I have 2 workbooks. Each workbook contains 46 sheets and I call them week numbers 1 to 46 on both workbooks. Each workbook is unique however the 46 sheets are identical in each of their respective workbooks. So the first workbook I am trying to put a link formula into cell A3 say for week 1 . The information I am putting into that cell would be the data from G3 in week 1 of the other workbook. Once I put the formula into the first workbook in cell A3 I then will drag the formula from A3 to B17 which will then get the data from the 2nd workbook from cell G3 to H17. I can do that part quite easy as I just link the two cells straight away no problem. I then want to do the same with all of the other sheets in the work books for week 2 to week 46. i can do that easy enough but it is very time consuming. I then have an additional 25 workbooks in the same format as workbook 1 that I want to repeat the function as I have done with workbook 1. All the other workbooks would be linked to workbook 2. As I say I can do it ok but it is very time consuming and was hopeful I could take the workbook1 and select all sheets so that then I only have to do the link and drag once with the other workbooks 2 to 46 all having their own week number on it. E.G cell A3 ='[workbook1.xlsx]week 1'!G3 and then repeated for each sheet so the next sheet would say ='[workbook1.xlsx]week 2'!G3 and so on. Is there a quick workaround with this at all?

[@pattyspanks6915]

Is there a way to do this for 2 shared online workbooks. We have a company wide database source that i want to pull data for our team into a separate book just for our reporting.

[@alecosavvas3361]

What if you are wokring with complex data where the layout may change. For example more rows or columns are added affecting the cell where the total can be found. The best bet it to name the total cell and linkto a name? However, if I have a lot of cells that would take plenty time to name is there a better way to try and make links more dynamic without having to change individual names of each cell?

[@cassidywilliams4144]

Thank you for making the linking of workbooks easy to understand. Just wondering how you could copy the formuals so that a new number would be imputed when imputed on the next line down? For example, I enter daily numbers in one work book and in another work books someone else uses the numbers for something different. They are always the same numbers. I have tried to grap the corner of the cell that has formual but it copys the same formula and doesn't advance the cell numbers down. (b12-b11 then next one would be b13-b12)

[@thesoftwarepro]

It's possible the formula has an absolute reference such as $B$12 which means Excel won't copy down the logic, it will copy the literal formula.

[@cassidywilliams4144]

@@thesoftwarepro Thanks for the reply. Is there a way to remove the absolute value so it will be more user friendly to drag and copy down?

[@thomasisabelle8071]

Hello, Dawn! I am looking to link data in multiple workbooks where I would like some way to dynamically update the data from the first to the last workbook without having to manually open and save each one. Example: data in workbook A is linked to workbook B, then workbook B is linked to workbook C. When A is updated, I like to be able to open C and have the data updated from A to B to C. Suggestions?

[@thesoftwarepro]

It's possible the automatic update for workbook links has been turned off. Go to File > Options > Trust Center > External Content and look at the Security settings for Workbook Links.

[@varunsachdevaa5254]

Thanks My Question Is If I need two different range to be linked from WB A to B On update B .system asks A WB already open do you want to Reopen and few other confusing questions And with more complax B WB receive links from multiple work book and from multiple range from same work book ! System many times Hangs So I am trying to develop One Range from One one WB But can you solve issue Multiple Range link from multiple WB link Master One file Thanks

[@official_youtube_1]

Great Video. Count a LIKE from me, too. Just wondering🤨 if I can link my file with source file keeping all the formatting.....!

[@thesoftwarepro]

The links connect to the data but the formatting is in the destination worksheet not the source worksheet.

[@nickypeet5554]

Hi Dawn, I'm having trouble linking ranges of cells. I put the = in my destination workbook, then go to the source and select a range of cells. I return to the destination and hit enter. I get an average for the selected range in the top of the range in the destination workbook, not the individual values identical to the range that I selected. What am i doing wrong please?

[@thesoftwarepro]

It looks like the snag is that you return to the destination before you hit ENTER so there may be additional cells or references added to the linking formula.

[@Gudha_Ismintis]

Hi - i have a master spreadsheet at work, the sheet has thousands of rows and dozens of columns. 1 of the main columns has the names of a significant number of staff members (which are not in order because rows have been gradually added over time to represent that staff member - which brings me to my question) Question: i want these staff members to fill in the subsequent columns that belong to them all the way up and down the spreadsheet - BUT without seeing the overall spreadsheet that contains the other columns for the other staff members entering their data - is there a way perhaps to do this?

[@ivycarter2536]

Hello, not OP, but I would recommend using Power Query for this. Create separate documents per employee and have those documents use Power Query to only get the information for certain employees. Have a column that employees enter the information to. Have all of those docs in ONE FOLDER. Then on your master sheet, create another power query function that links that ONE FOLDER to grab all of the excel sheets. With that connection, you can create a database of “completed” work. Whenever you need to add tasks, add it to your original sheet. Reference the “completed” sheet (that links all of the other excel sheets) to keep track of when tasks are done. I hope this makes sense, I saw your comment and it’s similar to something I’ll be implementing.

[@thesoftwarepro]

Thanks for the follow-up and great suggestions, @ivycarter2536. It sounds like you've created an excellent Power Query solution.

[@tutsecret499]

What if I have many workbook, example, people emailing me back filled survey and I want the another workbook grabs the data from survey and on workbookResponders because you want to put all the responders together Responder 1 and calculate all the responses.

[@thesoftwarepro]

Microsoft Forms might be a better solution to gather survey data into one worksheet.

[@roseballerine4395]

How can I maintain the formula if the workbooks are in sharepoint?

[@thesoftwarepro]

The key with any formulas that link or refer to other workbooks is that the users have access to the source workbooks whether they are in SharePoint, OneDrive, your local network or Teams (which uses SharePoint).

[@Ruby-zx8fk]

How can you do this with Microsoft Teams?

[@thesoftwarepro]

Although Teams can capture links in posts, it doesn't have the same linking features as Excel. You can however, share a workbook with links that team members can view and edit.

[@kamalmalek5542]

what if the source workbook is protected with password? can we still link ?

[@thesoftwarepro]

The answer, like a lot of things, is "It depends." What type of password? If a password is required to open the source workbook, then you'll need to know it as you'll be prompted for the password. If the password is to protect the worksheet from changes, you can link to the source data whether or not you know the password.

[@jaquinite]

its getting tasky if i need to do it for hundreds

[@diamendipity]

Anybody know a simpler way with less talking and opinion? Like 5 min or so or is this just hard?

[@hernanasuncionpepeng2021]

Great video! How can I link multiple input/output workbooks to 1 data & calculation workbook? Or does it have to be a 1 to 1 link of input/output to data & calculation workbook?

[@thesoftwarepro]

Depending on your application, the Excel Power Pivot tools may be a solution to combine multiple workbooks. Although I haven't covered it yet in this channel, there are tons of videos that cover these Excel options. Here's one to check out: kzbin.info/www/bejne/eZu2nmipfpitbtE