Excel automatic refresh and recalculation tricks

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

There are several ways to make Excel automatically refresh data connections and recalculate a worksheet.  These tricks are more important with the Stock data type in Excel  for Microsoft 365 customers.

Normally Excel will update itself when you change a cell value.  These days there are situations where cells change value but Excel does NOT update the worksheet.  In other words, modern Excel has changed in ways that Microsoft hasn’t yet fully adapted to.

Ideally, Excel would have an overall setting to refresh the worksheet every ‘n’ seconds or minutes.  As it stands, we need workarounds to make it happen.

Here’s some situations where forcing data refresh or recalculation might be necessary or prudent.

Stock Data Type

With the Stock Data Type  the ability to update automatically is more important.  Users will want their worksheets to grab the latest prices automatically, something the current preview releases can’t do.  Instead of having a nice automatic ‘ticker’, we’re expected to click ‘Refresh’ to get the latest prices.

The Stock and Geo data types are curious beasts. They are data connections to external sources but do NOT appear as Excel Data Connections.  That means you can’t setup an automatic data refresh, as you would with normal data connections.  In fact, there’s no exposed controls for the Stock or Geo data types.

NOW() and other volatile functions

The NOW() function updates to the latest date and time whenever Excel recalculates the worksheet. But if there’s nothing to make that happen, Now() doesn’t change value.  Some external factor is needed to make Excel update Now() and the rest of the worksheet.  In other words, you should be able to glance at a worksheet and know it’s up to the second but that’s not possible with Excel ‘out of the box’.

Microsoft calls NOW() and similar functions ‘volatile’ because their values can change even if no other cells have changed.  Other volatile functions are Today(), Randbetween(), Offset() and Indirect().  In some situations Info(), Cell() and SumIf() can also be volatile.

VBA custom functions can also be tagged as volatile using this line in the function code:

Application.Volatile

That line makes the function run anytime Excel updates/recalcs the worksheet.

Extra caution

Maybe you want your worksheet to update automatically as a precaution?  Many old Excel hands remember situations where Excel hasn’t properly updated so they like the ‘belt and braces’ approach (at least occasionally).

Code

The standard method of forcing automatic update of Excel is a short snippet of VBA code. Here’s what we use, there are many variations on the same theme. The full code is at the bottom of the article.

excel automatic refresh and recalculation tricks microsoft excel 19644 - Excel automatic refresh and recalculation tricks

There are three functions.

RefreshAllDataConn

does the actual refreshing of data connections ( Workbooks(ThisWorkbook.Name).RefreshAll ) and we added two, optional,  lines to display the last time refreshed on the bottom status bar.

If you wanted to be extra careful, add line to explicitly force recalculation.  Either  ActiveSheet.Calculate  or the extreme Application.CalculateFull (this would slow down a large worksheet, use sparingly).

AutoRefresh

run the RefreshAllDataConn sub every minute or whatever value you set on the line  Application.OnTime Now + TimeValue(“00:01:00”), “AutoRefresh”

Workbook_Open

an in-built Excel function that runs automatically when the worksheet is opened.  In this case it starts AutoRefresh.

Data Connection workaround

The disadvantage of the VBA approach is that a .xlsm worksheet is necessary (macro enabled Excel worksheet).  There can be problems sharing macro enabled files because of security concerns.

The arrival of PowerQuery / Get and Transform means there’s another way to force a worksheet recalculation.  It’s a workaround and not perfect, but it’s possible and doesn’t need a macro-enabled worksheet.

In short, ensure that there’s a data query setup with auto-refresh.  If there isn’t a data connection, add a small one to the worksheet.

Once you have an auto-refreshing query, the worksheet including any volatile functions should also refresh.

Ideally the Stock and Geo data types should also refresh.

Any Excel data query comes with some refresh options in the Query properties.  Most of them default OFF.

excel automatic refresh and recalculation tricks microsoft excel 19645 - Excel automatic refresh and recalculation tricks

Refresh every nnn minutes –  defaults off with 60 minutes suggested.

Refresh data when opening the file

Enable background refresh

Refresh this connection on Refresh All

The auto-refresh workaround is to create a small and practically insignificant data connection.  Then configure that data connection to update every minute or whatever time you wish.  That should force the worksheet to update including the volatile functions mentioned above.

Some versions of this workaround add a link to a tiny csv file on the same computer.  We’ve got the same result using a data connection from a table in the worksheet.

Create a small table with a single cell.  The cell can have anything but we create a cell with NOW() in it, for reasons we’ll explain later.

excel automatic refresh and recalculation tricks microsoft excel 19646 - Excel automatic refresh and recalculation tricks

Select the table then choose Data | Get Data | From other sources | from Table/Range.  The exact menu item maybe different depending on your version of Excel.

excel automatic refresh and recalculation tricks microsoft excel 19647 - Excel automatic refresh and recalculation tricks

When the Query Editor opens, just Close and Load it.  In the data connections pane, you’ll see a query.

img 5b4505d2e9d02 - Excel automatic refresh and recalculation tricks

Right-click the query, choose properties to see the settings we need.

excel automatic refresh and recalculation tricks microsoft excel 19649 - Excel automatic refresh and recalculation tricks

Set the refresh rate that suits you.

To be tidy, we move the source table (right) onto the same sheet at the loaded query (left).  Because we used NOW() in the source table cell, it’ll be easy to see when/if the worksheet has refreshed.

excel automatic refresh and recalculation tricks microsoft excel 19650 - Excel automatic refresh and recalculation tricks

 

VBA Code example

Sub RefreshAllDataConn()
    ' Refresh all Data Connections.

    ' This should include Stock and Geo data types, even though they aren't listed.

    Workbooks(ThisWorkbook.Name).RefreshAll

    ' Show update time on status bar to confirm.
    ' comment these lines out if not needed.

    Application.DisplayStatusBar = True

    Application.StatusBar = "Refreshed at: " & Now()

End Sub




Sub AutoRefresh()

' to run a Refresh All on the workbook every n minutes

   RefreshAllDataConn

   ' Repeat every minute or change to whatever value you prefer.

   Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"


' this is a simple example. There's no coded way to exit this function.

End Sub

Private Sub Workbook_Open()

' Starts the automatic refresh when the workbook is opened,

' commented out as a precaution.

    ' AutoRefresh

End Sub

Make automatic Excel worksheet list or table of contents

Complete Excel NetworkDays() solution with holidays & vacations

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address