Although it looks like Excel is always thinking, the reality is that it often needs something to happen before it will do its calculations. Normally this is not a problem. But when it comes to dates and times, you may have created some sort of tracking spreadsheet that needs to warn you once a certain date/ time is approaching. You can use the =NOW() function to build in the current time, but that only updates when something happens in Excel (you type in a cell, there is an Autosave etc). Is there a trick to auto refresh Excel every minute (with no VBA)?
There are VBA options but we prefer to use other methods that don’t rely on VBA.
External Data Range Properties
One way to auto refresh every minute is to make use of Excel’s ability to import data from a website (or an intranet site, or a file on your computer- more on this later). You can see how to do it here or do it via the new PowerQuery options, but the focus is on one of the options within this feature.
Once you have linked to a webpage, you can right click on the area and Choose the Data Range Properties. The below option will appear. Note that you have the ability to tell it to refresh itself every x minutes. Default is to auto refresh every 60 minutes but you can get it down to auto refresh every minute.
This is useful in its own right but in this case we don’t care what it refreshes, just that it refreshes. The process of refreshing causes the cells to recalculate, and the recalculate action will update the =NOW() function which then drives the rest of your tracking spreadsheet. Now while the spreadsheet is open it will update itself every minute.
Connect to a local file
As it does not matter what you connect to, and to avoid confusing Excel if you don’t have an internet connection, you can create a webpage on your local computer and make Excel look at it.
If you don’t have access to a web page you can download this one. It is blank and contains nothing else except a blank table (you can open it in Notepad to check).
- Unzip it and save it somewhere on your computer.
- You can track it down, but the easiest way to find the address is to right click on it, and open it with your web browser.
- Copy the URL you see and paste that into the Excel web query wizard and click Go
- Choose the blank table
- Import it.
Now you can do the first steps above (edit the Data Range Properties to refresh this every minute).
Reasons to auto refresh Excel every minute
As mentioned above, this is often a time based issue. If you run a call or support centre you may keep track of your queries as shown below. Now you can create a warning (see column D) which will change when you get closer to your target time to respond.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.