How to Update Links in Excel?

TL;DR Use the "Edit Links" feature under the Data tab for straightforward link updates. For more complex scenarios, consider using VBA macros or external tools like Python.

Using Edit Links Feature

The most direct method to update links in Excel is through the "Edit Links" feature found under the Data tab. This allows you to change the source of linked data when files are moved or renamed [2:1]. This approach is suitable for simple updates where you need to point existing links to new file locations or names [5].

VBA Macros for Automation

For scenarios involving multiple embedded Excel objects, such as within PowerPoint presentations, manually updating each link can be cumbersome. Using VBA macros can automate this process by looping through each embedded object and updating the links accordingly [1:1]. This method is particularly useful for large projects with numerous linked elements.

Python Integration

If you're working with Python to manage your data, libraries like openpyxl can help maintain Excel formatting while updating data [3:1]. This approach ensures that your data updates integrate seamlessly without breaking existing links or losing formatting, providing a programmatic solution to handle Excel files efficiently.

Power Query and Dynamic References

For daily updates involving multiple files, Power Query offers a flexible solution. By storing the file path in a cell, Power Query can use it during data import, allowing dynamic updates without manual intervention [4:1]. Alternatively, indirect formulas can create dynamic workbook references, although they may slow down performance [4:1].

Troubleshooting Persistent Link Messages

If you continue to receive messages about updating links despite clearing them, ensure all external connections are removed from the 'Queries & Connections' section under the Data Ribbon [5]. Double-check for any hidden links or named ranges that might still reference external sources.

By leveraging these methods, you can efficiently manage and update links in Excel, tailoring the approach to fit your specific needs and complexity of the task at hand.

See less

Helpful

Not helpful

Get faster answers with our extension.

The Gigabrain extension dives deep into billions of discussions, bringing you the most relevant and informative answers on the spot.

Add to Chrome

Products

Writing Excel Macros with VBA: Learning to Program the Excel Object Model Using VBA 2nd Edition, Kindle Edition

$16.71~~$31.99~~

4.1(76)

Source Threads

POST SUMMARY • [1]

Summarize

Update links in embedded Excel file within PowerPoint?

Posted by Last_Jedi · in r/excel · 6 months ago

1 upvotes on reddit

2 replies

Helpful

Not helpful

View Source

ORIGINAL POST

If you have an Excel workbook and link a cell to a different Excel workbook, that creates a "link" that you can edit in the Data tab in case you move or change the source workbook. Pretty straightforward.

If you have a PowerPoint with a chart that grabs data from a separate Excel workbook, that also creates a link in PowerPoint you can edit if the source workbook is moved or changed.

HOWEVER, if you have a PowerPoint chart which has an embedded Excel workbook as a backend, and THAT workbook grabs data from an external Excel workbook, that link does NOT show up in PowerPoint. You have to manually open each chart's embedded workbook and edit the link there.

I have a situation where I have many, many charts in a PowerPoint deck, each with an embedded workbook grabbing data from different places in an external Excel workbook. If I move that external workbook, how can I update the links in all the embedded workbooks together?

2 replies

AutoModerator · 6 months ago

/u/Last_Jedi - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1 upvotes on reddit

namishir · 6 months ago

Oof, that sounds like a nightmare to update manually! Have you considered using a VBA macro to loop through each embedded Excel object and update the links? It could save you tons of time, especially with multiple charts.

Speaking of saving time, if you're dealing with financial data in Excel, convertmybankstatement.com is a game-changer. It converts PDF bank statements into Excel, making data imports a breeze. Just thought I’d share since it might help streamline other parts of your workflow!

1 upvotes on reddit

See 2 replies

r/excel • [2]

Summarize

Best way to update links in master book?

Posted by RexRolled1984 · in r/excel · 4 years ago

Hey all,

I have 8 workbooks with monthly tabs that mangers update. And a master workbook with many worksheets that have links to various cells in each of those 8 workbooks tabs.

They just started new workbooks that are renamed "thisbranch-2021" and I want to copy the master workbook and have all the links point to the new workbooks. I could go in and edit each link from 2020 to 2021, but is there an easier/faster way to update the links in the master workbook?

2 upvotes on reddit

5 replies

Helpful

Not helpful

View Source

5 replies

AutoModerator · 4 years ago

/u/RexRolled1984 - please read this comment in its entirety.

Read the rules -- particularly 1 and 2
Include your Excel version and all other relevant information
Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1 upvotes on reddit

CaethRhydd · 4 years ago

Can you select edit links (is it under data, I'm not on computer atm) in the top ribbon and change the source to the relevant file? I'm not sure if I've understood properly I am an excel noob so I apologise if this isn't helpful.

4 upvotes on reddit

RexRolled1984 · OP · 4 years ago

>
>
>Solution Verified

Thank you! I totally skipped the links approach! That was quick and easy.

3 upvotes on reddit

CaethRhydd · 4 years ago

Oooh, great, thank you!

1 upvotes on reddit

Clippy_Office_Asst · 4 years ago

You have awarded 1 point to CaethRhydd

^I ^am ^a ^bot, ^please ^contact ^the ^mods ^with ^any ^questions.

1 upvotes on reddit

See 5 replies

r/learnpython • [3]

Summarize

How to Update Excel Reports Without Breaking Links Using Python?

Posted by Ok_Door_8578 · in r/learnpython · 7 months ago

I'm using Python to clean and process my data into a DataFrame. Once it's ready, I export it into an Excel sheet using ExcelWriter. However, the Excel report is already nicely formatted, and I don’t want to break any links (excel formulas) or lose the formatting when updating the data.

Is there a way to use Python to rewrite or update the data in the sheet while keeping the rest of the Excel file (like formatting and connections) intact? Or is there a better approach to ensure the updated data integrates seamlessly into the existing Excel report?

I am looking forward to any suggestions or tips!

1 upvotes on reddit

1 replies

Helpful

Not helpful

View Source

1 replies

Goingone · 7 months ago

I use openpyxl for this all the time.

Example here.

https://gist.github.com/tnhu/33a762d8c76aad25189114dffd9df6a8

Not sure how other libraries handle it (I.e. pandas if that is what you’re using).

1 upvotes on reddit

See 1 replies

r/excel • [4]

Summarize

Is there a way to efficiently update external workbook links?

Posted by l_am_bored · in r/excel · 3 years ago

instead of using the 'edit links' and updating link option to manually repoint the link to a newer file, is there a more quicker/efficient way to do this?

For context, there are some reports I currently need to update daily which references 5-6 different other excel files. im currently just using edit links to update it to reference the new file as they become available. Is there a more efficient way to go about this? for example i'll need to update the link from 'volume_data_day1' to 'volume_data_day2'.

I'm hoping maybe I can keep the directory and filename in some cells in a separate tab and as I update them it will be reflected in all the relevant cells/links.

is this possible?

6 upvotes on reddit

3 replies

Helpful

Not helpful

View Source

3 replies

BigLan2 · 3 years ago

You could use indirect formulas, but they can really slow down workbook performance https://exceljet.net/formula/dynamic-workbook-reference

If you use power query to bring the data into your main file then you can put the path into a cell and have PQ use that during the import https://community.powerbi.com/t5/Power-Query/How-to-use-the-current-file-location-as-the-relative-path-so-I/m-p/1829953#M54521

If you want to use regular formulas, and have a lot of references then it might be quicker to do a find/replace hack in excel

convert all your formulas to a text entry by find/replace = with a random text string like zzzx
find/replace the folder path - should be a lot faster as excel isn't updating formulas and trying to calculate the output
change everything back to a formula by find/replace zzzx with = I can't remember if this process is faster with manual calculations, or if it's ok to do it with automatic calcs still enabled.

2 upvotes on reddit

l_am_bored · OP · 3 years ago

Thank you for the suggestions ill definately look into them.

Ive worked with indirect formulas before but if they slow down the workbook the tradeoff might not be worth it. Will check out the other method you've mentioned

1 upvotes on reddit

AutoModerator · 3 years ago

/u/l_am_bored - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1 upvotes on reddit

See 3 replies

r/excel • [5]

Summarize

Message to update links in excel file keeps appearing

Posted by SAMCRO_2626 · in r/excel · 3 years ago

Despite removing all links from my excel file, i repeatedly get this message box when i open the file. This happens almost all the time though, not every single time i open the file.

What i've done to ensure external links are removed:

Under the Data Ribbon in the 'Queries & Connections' section, i selected 'Edit Links' and cleared the list of all external links.

Is there anything else i missed? Thanks.

1 upvotes on reddit

1 replies

Helpful

Not helpful

View Source

1 replies

AutoModerator · 3 years ago

/u/SAMCRO_2626 - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1 upvotes on reddit

See 1 replies

r/learnpython • [6]

Summarize

How to Update Excel Reports Without Breaking Links Using Python?

Posted by Ok_Door_8578 · in r/learnpython · 7 months ago

I am looking forward to any suggestions or tips!

1 upvotes on reddit

7 replies

Helpful

Not helpful

View Source

7 replies

Zeroflops · 7 months ago

You can edit the excel sheet cell by cell.

But I would suggest having a data sheet where you can write the data frame. Then have a report sheet where you display the data with the formatting.

Then you can just update the data sheet

1 upvotes on reddit

Ok_Door_8578 · OP · 7 months ago

So it would be two separate sheets? One that is being updated and the template sheet that will just refer to the table?

1 upvotes on reddit

Zeroflops · 7 months ago

Yes. If you do it right then you’re just updating the one sheet with all the data.

1 upvotes on reddit

reddit25 · 7 months ago

Openpyxl for existing files

1 upvotes on reddit

_Mc_Who · 7 months ago

As all the other recommendations, use openpyxl. Pandas is not built for tasks like this, and passing things into and out of a data frame is way more trouble and computational inefficiency than it's worth, unless you actually need a dataframe. Openpyxl will let you edit the Excel directly without disturbing the other cells

1 upvotes on reddit

NlNTENDO · 7 months ago

A little outside the box but you might consider doing it in Sheets using the API. Takes a little bit of upfront effort to get the auth stuff set up but after that it’s extremely flexible and allows you to edit cell values independently

1 upvotes on reddit

John_B_Clarke · 7 months ago

Obvious approach is to use Pywin32 and do it through Microsoft's API.

1 upvotes on reddit

See 7 replies

r/excel • [7]

Summarize

Best way to update links?

Posted by Dystopiate_addict · in r/excel · 1 year ago

Hi,

I have an Excel workbook which picks up the latest data from a bunch of other workbooks in a specific directory. Those workbooks will periodically get updated with new ones containing the latest data. I can't change this as an input.

What I'd like to do is have the Excel update the external links automatically at the push of a button.

I've seen this done with VBA before -- where a macro will scan the folder and pick up the new files, updating the link. From there the formulae will automatically pick up the relevant data using lookups or index matches on the latest date.

I don't really know VBA, but I do know python at a basic / intermediate level.

Is there a way to do this with python so that a non python user could later just hit the button and the workbook will refresh?

Or am I better off just learning the VBA I need to do this?

Thanks!

2 upvotes on reddit

8 replies

Helpful

Not helpful

View Source

8 replies

david_horton1 · 1 year ago

Excel 365 now has an Automate Ribbon with several sample scripts included. Learn to use Office Scripts as you then will be able to perform the scripts with Excel for the Web. Power Automate is available through Microsoft Store.

2 upvotes on reddit

Dystopiate_addict · OP · 1 year ago

Oh I'll check it out, though my work computer is annoyingly backwards and limited in terms of new things like that

2 upvotes on reddit

david_horton1 · 1 year ago

I asked Copilot this: python code to automatically update excel spreadsheet links. It came up with what looks like simple code. Are you using Excel 365? I assume you mentioned Python because it is now in Excel 365.

1 upvotes on reddit

itsTheOldman · 1 year ago

Remove all links and use power query to build a data set from all sources. Build report. Now when mew data is loaded you simple refresh your query and done.

Link in formulas will cause major headaches. Move to power query now and you’ll be a hero.

1 upvotes on reddit

Dystopiate_addict · OP · 1 year ago

But will power query know to pick up the data if a new file replaces an old one?

1 upvotes on reddit

itsTheOldman · 1 year ago

Yes. Do a google on power query. Youtube ExcellIsFun channel is solid.

Ill give a quick example. I have a report driven by thousands of individual text files. All the text files represent a month of data. New files are loaded at end of each month. I use pq to connect to the folder where all the individual text files are stored… once new text files are loaded to the folder i simply refresh the pq and the entire report is update.

2 upvotes on reddit

Cb6cl26wbgeIC62FlJr · 1 year ago

I’m on my phone now, but, there should be a way to refresh the data every x amount of minutes, or upon opening the workbook.

2 upvotes on reddit

AutoModerator · 1 year ago

/u/Dystopiate_addict - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1 upvotes on reddit

See 8 replies

r/excel • [8]

Summarize

Excel is trying to auto-update links that do not exist

Posted by DCMurphy · in r/excel · 5 years ago

I'm encountering a problem with Excel in Office365.

The yellow box "**Security Warning** Links to external sources could be unsafe. If you trust the links, click Update. Click for more details." appears under the top ribbon, but there are no external links in the spreadsheet. When I click "Update", it prompts me to update values for [insert name of spreadsheet].

I've checked every tab using Ctrl+`/~ to make sure that no links are going to outside sheets. I've checked to make sure there are no hidden sheets that could contain formulas. All I do is copy and paste raw data from some CSVs and exported XLS files into the sheet. There are no links to outside workbooks. There are only links to other tabs within the same sheet.

This is a problem across a multitude of spreadsheets and a fix-all solution or road to problem solving would be much appreciated. Is there another way that links can manifest themselves that would prompt Excel to look for formulas or data references to outside sheets?

Thanks!

Edit: I also tried the "Break Links" feature in Edit Links from the Data tab but it isn't doing anything.

1 upvotes on reddit

6 replies

Helpful

Not helpful

View Source

6 replies

elpatio6 · 5 years ago

Try changing the links to your current document.

1 upvotes on reddit

DCMurphy · OP · 5 years ago

I edited for clarity: there are no links to other workbooks, only links to separate sheets within this workbook. I've gone through a few thousand cells checking formulas and nothing is linking to a different workbook.

1 upvotes on reddit

elpatio6 · 5 years ago

Yes I understand you have no links, but your workbook might have phantom links, and this is a way to remove them. Did you try it? Go to data, edit links, select change links, and then choose your workbook as the new link. If this doesn’t work, google ‘phantom excel links’ to get more options.

1 upvotes on reddit

basejester · 5 years ago

Check for names referring to external cells in Name Manager.

1 upvotes on reddit

DCMurphy · OP · 5 years ago

The window is blank, so I don't think any name reference are in play.

1 upvotes on reddit

work_account42 · 5 years ago

Check for objects that came over when copying/pasting.

1 upvotes on reddit

See 6 replies

r/excel • [9]

Summarize

SharePoint Excel to "Edit Links"

Posted by JBHedgehog · in r/excel · 4 years ago

Hello Reddit Excel!

We performed a large migration of our network files to SharePoint last night. This morning everything is seeming to work pretty well! But we've run into an issue (probably the first of many).

We have a number of linked spreadsheets (one spreadsheet links to another for data). Not hard stuff. We can update these links in SharePoint. We have spreadsheet A & B open and then just link the cells in the old fashioned way...it works fine.

However, this is pretty slow. The accounting team is used to using (on the Ribbon) Data-->Edit Links and then performing a very quick update with that tool. It works well. But we HAVE NOT been able to figure this out within SharePoint.

Does anyone have any experience or advice with this particular piece of Excel?

1 upvotes on reddit

1 replies

Helpful

Not helpful

View Source

1 replies

AutoModerator · 4 years ago

/u/JBHedgehog - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information
Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1 upvotes on reddit

See 1 replies

r/excel • [10]

Summarize

Updated links in excel

Posted by Chiarka · in r/excel · 3 years ago

Hi folks, at work we have different Excel spreadsheets saved in Sharepoint. These files are often updated manually. Lets say I have one Excel which pulls data from many others. Is there a way to see if data were updated apart from opening the sources? Thank you!

2 upvotes on reddit

4 replies

Helpful

Not helpful

View Source

4 replies

wjhladik · 3 years ago

Check the date/time of the excel file versus a record of when you last got an update.

2 upvotes on reddit

Chiarka · OP · 3 years ago

I am sorry, what do you mean by the record of when I got the update? I need to see the most recent changes. The problem is, that when I click on Update links, sometimes some of the linked values arent updated...

1 upvotes on reddit

wjhladik · 3 years ago

Update all files today. Write down that you did each named file with today's date (10/24/2021). I mean keep a list in your excel file that includes the excel file name and the date last updated.

Wait x days (like in 4 days it will be 10/28/2021). Look at each excel file's date. You can do this manually or via vba. If any are after 10/24/2021 then they have been updated since you last checked. If any are on or before 10/24/2021, they have not been updated. For each that has been updated, initiate your update process on just those files. Then denote their last update date as 10/28/2021 in the list you are keeping.

2 upvotes on reddit

AutoModerator · 3 years ago

/u/Chiarka - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1 upvotes on reddit

See 4 replies

Top People to Talk to

These are some of the top contributors related to

excel

learnpython

AutoModerator

No bio available

2000

Karma

10000

Posts

10000

Comments

View Profile

Chat w/ Profile AI

CaethRhydd

No bio available

3184

Karma

Posts