Automatic update of links has been disabled

New Page

Control when external references (links) are updated

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More… Less

When you create external references to other workbooks (also called links), you can control if and when they are updated. This is important because if someone else changes the value of a linked cell, Excel won’t update the link or show the new value unless you tell them to.

The workbook that contains the links is called the destination workbook, and the workbook it is linked to is called the source workbook . You can link multiple source workbooks to a single destination workbook.

Workbook contains links

1. The destination workbook contains the external reference (link).

2. The external reference (or link) is a reference to a cell or a range in the source workbook.

3. The source workbook contains the linked cell or range and the actual value returned to the destination workbook.

Note:  Although there is no limit to the number of workbooks you can link to, it is generally a good idea to link to as few as possible. Otherwise, you may experience performance issues with the recalculation of the formula. It’s also possible to cross workbooks, each acting as a source and a destination, but this can get confusing and difficult to manage.

Control links to other workbooks

When the source and destination workbooks are opened on the same computer, the links are automatically updated. When you open a destination workbook and the source workbook is not open, the trust bar may warn you to update the links. You can control whether the trust bar warns you and whether you want to update all links when the alert does not appear. You can also choose to update only certain links if the workbook contains more than one.

In the following sections, we’ll review the most common options for managing link updates.

Manually update all or none of the links in a workbook

  1. Close all source books. If one source workbook is open and others are closed, updates will not be consistent.

  2. Open the destination workbook.

  3. To update the links, in the trust bar, click Update . Close the trust bar if you don’t want to update the links (look for the X on the right side).

Manually update only some of the links to other workbooks

  1. Open the workbook containing the links.

  2. Navigate to Data > Queries and Connections > Edit Links .

    data connections

  3. In the Source list , click the linked object that you want to update.

    You can select individual workbooks with Ctrl+click or all with Ctrl+A .

  4. Click Update Values .

    Excel Edit Links dialog box from Data > Queries and Connections > Edit Links

Cambie el libro de origen de un referencia externa sin afectar otras referencias externas en el libro de destino

  1. Busque el libro de trabajo que dessea usar como la nueva fuente para la referencia externa y anote su ubicación.

  2. In the destination workbook, select the cell with the external reference that you want to change.

  3. In the formula bar button picture, look for a reference to another workbook, such as C:\Reports\[Budget.xlsx], and replace that reference with the location of the new source workbook.

Control el indicator de inicio para actualizar enlaces

You can decide whether the links in this workbook are updated when opening the file according to the user’s setting, not updated when opening the file, or updated automatically without prompting the user.

Warning: This option affects all users of the workbook. If you choose not to update links, and not to prompt, users of the workbook will not know that the data is out of date.

  1. Go to Data > Queries & Connections > Edit Links.

  2. Click Startup Prompt.

  3. Select from one of the following three options:

  • Let users choose to display the alert or not

  • Don’t display the alert and don’t update automatic links

  • Don’t display the alert and update links.

Notes: 

  • Automatic vs. Manual update options – Formula links are always set to Automatic.

  • Even if you specify that Excel should not ask whether to update links, you will still be notified if there are any broken links.

Editar opciones de enlace

When you open the Edit Links dialog (Data > Queries & Connections > Edit Links), you’re presented with several options for dealing with existing links. You can select individual workbooks with Ctrl+click, or all of them with Ctrl+A.

Excel Edit Links dialog box from Data > Queries and Connections > Edit Links

  • Update Values

    This will update all selected workbooks.

  • Change Source

    This option is useful when you want to point existing links to another source. For instance, you might have a prior year workbook, and need to point to a new workbook when a new year starts. Selecting Change source will launch a file explorer dialog, where you can browse to the new source workbook. If the workbook has more than one worksheet, you will be prompted to specify which one to link to – just click the sheet you want and click OK.

    Note: It is possible to point a workbook back to itself by selecting it from the Change source dialog. This will sever any formula links to the originally linked source workbook.

  • Open Source

    This will open the source workbook.

  • Break Link

    Important: When you break a link to a source, all formulas that use the source are converted to their current value. For example, the link =SUM([Budget.xlsx]Annual!C10:C25) would be converted to the sum of the values in the source workbook. Because this action cannot be undone, you may want to save a version of the file first.

    1. On the Data tab, in the Queries & Connections group, click Edit Links.

    2. In the Source list, click the link that you want to break.

      You can select individual workbooks with Ctrl+click, or all of them with Ctrl+A.

    3. Click Break Link.

      Notes: 

      • If the link used a defined name, the name is not automatically removed, so you may also want to delete the name.

      • To delete a name:

      • If you use an external data range, a parameter of a query may also use data from another workbook. You may want to check for and remove any of these types of links.

      • On the Formulas tab, in the Defined Names group, click Name Manager.

      • In the Name column, click the name that you want to delete, and then click Delete.

  • Check Status

    This simply displays a notification in the Edit Links pane whether a linked workbook is still a valid source. It should display OK, but if it doesn’t then you’ll need to check on the source workbook. In many cases, a source workbook may have been moved or deleted, cutting the link. If the workbook still exists, you can use the Change Source option to relink the workbook.

Preguntas frecuentes

  • Can I replace a single formula with its calculated value?

    Yes. When you replace a formula with its value, Excel permanently removes the formula. If you accidentally replace a formula with a value and you want to restore the formula, click Undo cancel button immediately after you enter or paste the value.

    1. Press Ctrl+C to copy the formula.

    2. Appuyez sur Alt+E+S+V pour coller la formule comme valeur, ou allez dans Accueil > Presse-papiers > Coller > Coller les valeurs .

  • Que faire si je ne suis pas connecté à la source ?

    Cliquez sur Ne pas mettre à jour . Excel ne peut pas mettre à jour à partir d’une source qui n’est pas connectée. Par exemple, la source peut se trouver sur un réseau et vous n’êtes peut-être pas connecté à ce réseau.

  • Je ne veux pas que mes données actuelles soient remplacées par de nouvelles données

    Cliquez sur Ne pas mettre à jour .

  • J’ai essayé de mettre à jour la dernière fois, mais cela a pris trop de temps

    Click Don’t Update . If your data doesn’t need to be the most up-to-date, you can save time by not updating all the links. After opening the workbook, on the Data tab, in the Connections group , click Edit Links , and then update the links only from the sources you need.

  • Someone else created the workbook and I don’t know why I’m seeing this message

    Click Don’t Update and contact the workbook owner. You can also search for links within the workbook. On the Data tab , in the Queries and Connections group , click Edit Links .

  • I respond to the ad the same way every time and I don’t want to see it again

    You can respond to the boot notice consistently and avoid seeing the boot notice for this book.

    • Don’t ask for every workbook I open and update links automatically    

      This option affects all open workbooks on the computer. Users who open the workbook on another computer are not affected.

      1. Go to File > Options > Advanced .

      2. Under General , uncheck Prompt to update automatic links . If this box is unchecked, the links are automatically refreshed and no alerts are displayed.

    • Ask the same way for each user of this workbook    

      Warning:  This option affects all users of the workbook. If you choose not to update the links and not request them, workbook users will not know that the data is out of date.

      Navigate to Data > Queries and Connections > Edit Links > Boot Notices and select the desired option.

      Note:  You will always be notified if there are broken links.

  • What happens if I use a parameterized query?

    1. Click Don’t Update .

    2. Close the destination workbook.

    3. Open the source workbook.

    4. Open the destination workbook.

    5. Click Update .

    A link to a parameterized query cannot be updated if the source workbook is not open.

  • Why can’t I choose “Manual” as the update option for a specific external link?

    Formula bindings are always set to Automatic.

Do you need more help?

You can always ask an expert in the Excel Tech community or get help in the Answers community.

Facebook LinkedIn Email

Do you need more help?

Develop your skills

EXPLORE TRAINING >

Get new features first

JOIN MICROSOFT MEMBERS >


Video Automatic update of links has been disabled

Related Posts

Free chat room code for my website

Contents1 How to set up a free chat room on the website.2 3 comentarios2.1 Trackbacks/Pingbacks2.2 Submit a Comment Cancel reply3 How to Create a Chat Room Website…

Background image full screen css

Contents1 Cómo – Full Page Image1.1 Example1.2 Example2 CSS background image tamaño tutorial: how to codify a complete page background image3 Perfect Full Page Background Image3.1 Méthode CSS géniale,…

WordPress leverage browser caching

Contents1 Aproveche el almacenamiento in hidden del navegador1.1 Will it works for my website?1.2 Where are plugin options1.3 Some JavaScript files still display under Leverage Browser Caching1.4…

WordPress post to facebook page

Contents1 How to Automatically Post to Facebook from WordPress1.1 Download Now: How to Launch a WordPress Website [Free Guide + Checklist]1.2 1. Create an IFTTT account.1.3 2….

Download images from wordpress media library

Contents1 How to export your WordPress media library1.1 Download maintenant : How to launch a WordPress website [Free Guide + Checklist]1.2 How to export your WordPress media…

WordPress single post template

Contents1 How to Create Custom Unique Post Templates in WordPress2 Post Template Files2.1 author.php2.2 Fecha.php3 Handbook navigation4 How to Create Custom Single Post Templates in WordPress5 Video…