Excel Background Refresh & Pivot Tables

Welcome to my first 2020 #TipTuesday post. Today’s topic is Excel and how a background refresh setting can impact refreshing pivot tables.

The background to this post is related to a report I was creating for a client to validate timesheets prior to approval. The report has a couple of queries in them (from SQL) and a pivot table to summarize the data. In order to make the refreshing easier, I created a tiny bit of VBA and a button for the users to click when they wanted to refresh the data, to pull in the latest timesheets. When testing it, it was doing everything except updating the pivot table. Here’s how I resolved that!

Query Settings

Initially my query settings (properties) looked like this. Both queries has “Enable background refresh” enabled. At the time of creating the queries, I didn’t adjust these default settings.

My simple VBA looked like this. The first 2 lines are the 2 queries I have, which are pulling in some timesheet info and pulling in a filtered list of employees by manager. Those statements refresh the queries. The next two lines are to make the sheet with the pivot table active and then refresh the table on the page. Overall this is pretty basic code, nothing super fancy was needed here.

The Issue

The challenge was, the pivot table wasn’t refreshing – or more accurately described – it didn’t *seem* to be refreshing. It was doing what the command indicates; however, the commands are so short that it was refreshing the pivot while the background refresh of the underlying query was still working to update the queries.

In simplistic terms, “background refresh” means refresh in the background and not to wait for it to finish before continuing with the next command. In my case, I didn’t want “background refresh”, I actually want the query to complete updating before the next command runs. Background refresh may be appropriate if the query were to take time to execute, in which case I would look for other solutions to this problem. In my case, the query is small but long enough that it is still running by the time the pivot table refresh command was started.

The Fix

There are multiple ways to handle this but the choice for me in this case was to revisit the properties of the 2 queries, shown in the screenshot above, and then uncheck the “Enable background refresh” option. Once I did that, everything refreshes as I expect: queries first, then pivot table data.

I could have also handled it by disabling background refresh in the VBA but in this situation, managing it via the query was easy enough to do.

That’s it for this tip… enjoy!