External Data Refresh Failed

I have already done this. It's a good idea, because if the stock identifier has changed without one noticing, this could throw up the error but I've checked all 35 of mine and they all update successfully when copied to a clean workbook. It seems Excel now throws out this error message (which didn't used to be the case) if any cell contains a formula which is different to those beside it if, and only if, one does a data refresh.

I agree that MSFT could do a much better job of addressing the problem.

The solution I posted worked for me. I did run into the inconsistent formula problem but didn't have that many.

Might I suggest that you copy and paste your stocks to a new sheet without any formulas and try and refresh all data then use the error check tool to see if it will isolate the problem cells.

That doesn't work. Nor does the more complete 'solution' of error checking, options and then unclicking all of the 'error checking rules' except for 'cells containing data types that couldn't refresh'.

In my case that takes me to an almost endless series of formula which are highlighted because they are inconsistent with adjacent formula but they are inconsistent for a reason. For example, some share prices are pulled in £, others in pence and some in US$, so they have to be made consistent either by dividing the prices in pence by 100 or those in US$ by the exchange rate.

It's ridiculous that Excel has begun generating these error messages and no one at Microsoft seems to care about fixing what is pretty clearly a bug.

Re 365:

I solved the problem by opening a page; clicking on the formulas tap at the top of the page then click on error checking.

Go to the Formulas tab. In the Formula Auditing section, click Error Checking. In the lower left of the resulting window, click on Options. At the bottom of the resulting window is a section titled, Error Checking Rules. Deselect everything except, "Cells containing data types that couldn't refresh." Click OK. Click Resume. You should then get a list of the problematic stock data cells.

Yes, this is what I needed! I looked all over for it but couldn't find it. Thank you!

Obviously the problem many of us have is that when stock is delisted or the symbol changes, it can't be updated, so we get the error message at the top of the screen. (Especially now that Excel added auto-refresh options.) I thought I had checked every stock data type, but I kept getting the error message. This helped me find the two I missed.

** Microsoft, if you're listening, you need to update the error checking feature with an option to check the whole workbook, not just the current worksheet. I have about a dozen tabs, so I had to repeatedly go through all the steps above for each sheet. Of course the problematic cells were in the very last tab. :-(