microsoft excel
Is there a particular order in which tables and connections are refreshed in Excel when using the Refresh All on the data tab?
For example, suppose I have a table that is sourced via an ODBC or SQL Server connection. From this table I create a subset of data that then feeds various Pivot Tables. When I use Refresh All, will Excel understand that the ODBC data source needs to be refreshed first?
Is there any default logic or order to using Refresh All?
Additional detail to @meggie's response above. I suspect this has to do with corrupt Data Model connections.
@Matt - same as you, none of the solutions above worked for me either. What was more frustrating was that I had two Pivot Chart/Tables in the same file, both linked to the same Power Pivot data model, one would maintain its custom formatting but the other would not. Thus I knew it was very unlikely related to my version of excel or a specific bug.
The article linked by harrymc contains the key clue to resolving this, but not using the workaround - it's the XML. I was at the point of comparing the XML for the two Pivot Charts but then thought there may be a formatting hierarchy at work here.
My solution:
I was then able to refresh/slice/filter the data and the custom chart formats were all preserved.
Best Answer
Absolutely. Excel always updates non-dependent "source" data before re-calculating anything that depends on that source. To do it any other way would result in incorrect results.
You can see this in action by observing how Excel calculates a formula by using the Evaluate Formula feature. Here's a formula where the first step involves getting the value in cell
G15. That cell in turn contains a formula, which requires getting the value ofE15. That cell in turn contains a formula, the first step of resolving which requires evaluating the IF function...and so on.This ordered process of "working backward" through volatile values until the root data is identified is inherent in how Excel calculates and evaluates data. Rest assured Microsoft didn't leave this important consideration to mere chance.