How to Make an Excel Spreadsheet Update Automatically

Excel updates formulas automatically as long as calculation is set to Automatic, which you check on the Formulas tab under Calculation Options. To make one sheet or workbook update from another, link the cells with a formula, and for data pulled from outside Excel, turn on Refresh data when opening the file or Refresh every in the query’s properties.

These steps are for Excel for Microsoft 365, Excel 2024 and Excel 2021 on Windows, and most also work in Excel 2019 and 2016. Excel for Mac and Excel for the web use the same Formulas and Data tabs, with a few differences noted below.

How to turn on automatic calculation in Excel
Automatic calculation is the setting that keeps formulas current.

Pick the Right Method

“Update automatically” can mean four different things in Excel. Use this table to jump to the one you need.

What should update Use this Works while the file is closed?
Formulas when you change a number Automatic calculation Not needed
A cell that copies another sheet or workbook A workbook link (=) No, it updates when opened
Data from a database, web page, CSV or other file Power Query refresh settings No, the file must be open
Anything, on a timetable, with nobody at the PC Office Scripts with Power Automate Yes, for OneDrive or SharePoint files

Turn On Automatic Calculation

  1. Open the workbook and select the Formulas tab.
  2. Select Calculation Options in the Calculation group.
  3. Choose Automatic. Formulas now recalculate every time you change a value they depend on.

Change any number your formulas use, and the totals and results update right away without pressing anything.

Ways to make Excel update automatically: Calculation Options Automatic, link cells with =, and refresh imported data on open or every few minutes

You can set the same option under File > Options > Formulas > Workbook Calculation. Details are in Microsoft’s formula recalculation article.

On a Mac, workbook options sit under the Excel menu instead, which is also where you change the default save type in Excel for Mac.

What the three calculation modes do

  • Automatic recalculates dependent formulas whenever a value, formula or name changes. This is the default for new workbooks.
  • Automatic except for data tables does the same but skips What-If Analysis data tables, which can be slow. Press F9 to update those.
  • Manual recalculates only when you tell it to. When you pick Manual, Excel also checks Recalculate workbook before saving, so the saved file still has current results.

In the desktop app, the mode you pick applies to every workbook you have open at the time. That is why one file set to Manual can make another file stop updating. In Excel for the web, the setting applies only to the workbook you are in.

Recalculate by hand

If a workbook is stuck on Manual, or you want to force a refresh, use these keys:

Keys What it recalculates
F9 Changed formulas in all open workbooks
Shift + F9 Changed formulas on the active sheet only
Ctrl + Alt + F9 Every formula in all open workbooks
Ctrl + Shift + Alt + F9 Rechecks dependencies, then recalculates everything

The Calculate Now and Calculate Sheet buttons on the Formulas tab do the same as F9 and Shift + F9.

If the cell cursor itself lags when you move around a large workbook, there’s a separate fix for a slow cursor in Excel.

Google Sheets has a similar choice that lets you switch between automatic and manual calculation.

Example: Calculation Options Set to Automatic

In Excel for Microsoft 365 on a Windows 11 laptop, the Calculation Options button on the Formulas tab opened a short list: Automatic, Partial and Manual, with Format Stale Values and Compatibility Version below them.

Automatic had the check mark, which is the default. In a small sample sheet of garden supply orders (item, category, quantity, price, order date and a Qty times Price total), each Total formula calculated the moment I finished typing its row. If your formulas only change after you press F9, this menu is usually set to Manual.

Excel Calculation Options menu with a red arrow pointing to Automatic
Formulas > Calculation Options in Excel.

Update One Sheet or Workbook From Another

  1. Open both workbooks (or go to the sheet that should receive the data).
  2. Select the cell that should update and type =.
  3. Switch to the source sheet or workbook and click the cell you want to pull from.
  4. Press Enter. The cell now shows the source value, and the formula looks like =[Sales.xlsx]Sheet1!$A$1.

When both files are open, the linked cell changes as soon as the source changes. If you close the source, Excel adds the full file path to the formula. Excel adds dollar signs to make the reference absolute; delete them if you plan to copy the formula down a column. Microsoft explains the formula formats in Create workbook links.

These workbook links are different from clickable web links. If typed addresses keep turning blue, turn off automatic hyperlinks in Excel. Those links work between workbooks only; a copy inserted into OneNote won’t follow updates, so see how to link Excel to OneNote with a live file link.

To link a whole block at once, copy the cells in the source, select the destination cell, and choose Home > Paste > Paste Link. For a fuller walkthrough of linking one workbook to another, see how to link two Excel spreadsheets.

Make links refresh when the file opens

By default, Excel may show a yellow Security Warning bar instead of updating links. Select Enable Content to update them for that session. To change what happens every time:

  1. Select Data > Queries & Connections > Workbook Links.
  2. Open Refresh settings at the top of the pane.
  3. Choose Always refresh to update links each time the workbook opens, or Ask to refresh to get a prompt.

The Workbook Links pane is in Excel for Microsoft 365. In older versions, the same choices are under Data > Edit Links > Startup Prompt. Microsoft’s Manage workbook links page also covers fixing a moved source file with Change source. Avoid Break links unless you mean it, because it turns linked formulas into fixed values and can’t be undone.

In Excel for the web, both workbooks must be in your online storage, and you select Trust Workbook Links before they refresh.

Refresh Imported Data Automatically

If your sheet pulls data from a database, a web page, or another file through Data > Get Data, set it to refresh on its own:

  1. Select a cell in the imported data.
  2. Select Data > Queries & Connections, open the Connections tab, right-click the query, and choose Properties.
  3. On the Usage tab, check Refresh data when opening the file.
  4. To refresh while the file is open, check Refresh every and enter the number of minutes.
  5. Select OK.

Excel pulls fresh data each time you open the workbook, and on the schedule you set while it stays open. Leave Enable background refresh checked if you want to keep working during a refresh.

How to set Excel imported data to refresh automatically
Refresh settings live in the query’s Properties on the Usage tab.

Steps are from Microsoft’s Refresh an external data connection article. The timed refresh only runs while the workbook is open in Excel. If the data source needs a password, Excel may ask for it once per session.

Importing a CSV file this way, rather than double-clicking it, also lets you control column types. Here’s how to open CSV files with Excel.

Refresh PivotTables when the file opens

A PivotTable keeps its own copy of the source data, so it doesn’t change when the source cells change. To update it on open:

  1. Click anywhere in the PivotTable.
  2. On the PivotTable Analyze tab, select Options.
  3. On the Data tab, check Refresh data when opening the file, then select OK.

Refresh Everything at Once or on a Schedule

To update every query, connection and PivotTable in the workbook at once, select Data > Refresh All or press Ctrl + Alt + F5. Press Esc to stop a long refresh.

To run updates at set times even when nobody has the file open, use Office Scripts with Power Automate. You need a Microsoft 365 business, enterprise or education license, and the file must be saved in OneDrive for Business or SharePoint. Microsoft 365 Personal and Family aren’t on Microsoft’s list of supported plans.

  1. Open the workbook and select the Automate tab.
  2. Select Record Actions, do the task once, and stop the recording, or write a script with New Script.
  3. In Power Automate, create a scheduled flow, add the Excel Online (Business) Run script action, and choose your file and script.

Use sheet names in scripts rather than “the active sheet,” since nobody is looking at the file when the flow runs. Power Automate also runs on UTC time, and each user can make 1,600 Run script calls per day. Some external data connections can’t refresh in Excel for the web, so test the flow once before relying on it. Microsoft’s Office Scripts platform requirements list the supported plans.

Refresh on open with a macro

If you use desktop Excel without a business plan, a short macro can refresh everything each time the file opens:

  1. Press Alt + F11 to open the Visual Basic editor.
  2. Double-click ThisWorkbook in the project list on the left.
  3. In the code window, type Private Sub Workbook_Open() on the first line, ThisWorkbook.RefreshAll on the second, and End Sub on the third, then close the editor.
  4. Save the file as Excel Macro-Enabled Workbook (.xlsm).

Excel blocks macros in files from the internet and may show a warning bar, so this works best for your own files. Workbook.RefreshAll refreshes all external data ranges and PivotTables.

Show a Date or Time That Updates Itself

To keep today’s date or the current time in a cell, use Excel’s built-in functions:

  • =TODAY() shows the current date.
  • =NOW() shows the current date and time.

Both update when the worksheet recalculates, such as when you open the file or edit a cell. NOW is not updated continuously, so it won’t tick like a clock. If either one doesn’t change when you expect, check that calculation is set to Automatic, as Microsoft’s TODAY function page recommends.

You can build on them, too. Microsoft’s example =DATEVALUE(“1/1/2030”)-TODAY() counts the days until a future date and updates every day you open the file.

If you want a date that never changes, such as the day an order came in, press Ctrl + ; instead. That types today’s date as a fixed value.

Tips That Keep a Spreadsheet Up to Date

  • Use a table. Select your data and press Ctrl + T. Formulas and charts that point to a table include new rows automatically, and a formula such as =SUM(Table1[Sales]) grows with the data. A chart built on a fixed range like A1:D100 misses anything you add below it. To try this with a simple chart, make a pie chart in Excel.
  • Save to OneDrive. With AutoSave on, everyone who opens the file sees the latest changes. Here’s how to let multiple people edit a workbook.
  • Protect your formulas. If other people type in the sheet, lock the formula cells so nobody overwrites them by accident. Follow the steps to lock cells in Excel.
  • Go easy on TODAY, NOW and RAND. These recalculate every time anything changes, so hundreds of them can slow a large workbook.

Troubleshooting Formulas That Won’t Update

  • The formula shows as text. The cell was formatted as Text before you typed the formula. Change the format to General on the Home tab, then press F2 and Enter. Changing the format alone doesn’t fix it.
  • Every cell shows a formula. Show Formulas is on. Press Ctrl and the grave accent key (left of the 1 key), or turn it off on the Formulas tab.
  • One formula shows as text. There is an apostrophe or a space before the =. Delete it and press Enter.
  • A Circular References warning appears. A formula refers to its own cell. Select Formulas > Error Checking > Circular References to find it, then fix the range.
  • Links show old values. The Security Warning bar is blocking them. Select Enable Content, or set Always refresh in the Workbook Links pane.
  • Imported data didn’t change. The source may be offline or need a password. Select Data > Refresh All and look for an error message.

How to Confirm It Worked

Type a new number in a cell that a formula uses. If the total changes as soon as you press Enter, calculation is automatic. For links and imported data, change the source, then close and reopen the destination file. The new value should appear without pressing anything.

Automatic Spreadsheet Update Questions

Why are my Excel formulas not updating?

Calculation is probably set to Manual, often because the workbook came from someone who changed it or another open workbook was set to Manual. Switch it back to Automatic. Also check that the cell isn’t formatted as Text, which makes Excel show the formula instead of the result. The same setting affects a text box that is linked to a cell, as explained in the steps to show a formula result in an Excel text box.

Can Excel update a spreadsheet while it’s closed?

Not on its own. Calculation, links and timed refresh all need the file open. To update a closed file on a schedule, save it to OneDrive for Business or SharePoint and run an Office Script from Power Automate.

Can a drop-down list update automatically too?

Yes, if the list pulls from a table. Here’s how to create a drop-down list in Excel.

Does automatic calculation slow Excel down?

On very large workbooks with many lookups or volatile functions, it can. Choose Automatic except for data tables first. Use Manual only if you remember to press F9 before you read the results.

Next, if you share the workbook, set it to save to OneDrive so everyone sees the same, current numbers.