May 03 2018
- last edited on
Jul 25 2018
We use Excel web parts to display charts in SharePoint online. Our web parts were working fine for months but now don't show bars due to the reference cell formula not working. When attempting to open the spreadsheet in the Excel browser we get warning about features not supported and if we want to create an editable copy. If we say yes, we can then go to the reference cells and see the formula displaying #REF in the cell. However, there doesn't seem to be an issue with the formula because if after clicking in the cell and then hitting enter the #REF goes away and the formula renders the value. After saving the 'editable' copy the Excel web parts is fixed and the charts show up as they should.
So my question - can features that aren't supported in Excel web app hinder a web part from displaying data that may rely on the feature? Like a macro? We can't quite pin down the issue so any insight would be helpful. Has something changed recently in O365 that might have had an impact on how our spreadsheets work?
May 14 2018 09:11 AM
Data is in table format (using traditional Excel locally).
When you use the local excel application and make a formula that references something in the table boundaries, the formula uses a reference to the table first and then the specific cell (a 'structured reference' to use Excel terms).
Excel online does not understand those references. In particular it returns a #REF. In other words it’s saying sorry, I don’t recognize what your formula is referring to.
So then you have to go into excel online to edit the formulas and when you select the cell you need in a formula it gives you a traditional reference like “E5” (an 'explicit cell reference').
Then the excel online knows what the heck you are talking about and doesn’t return the #REF.
This is a new problem that cropped up in the last few weeks. We have been using the same spreadsheet successfully for months with a SharePoint webpart. Suddenly it stopped working and returning the #REF. With a simple spreadsheet the user can deal with going in and changing the formulas to only use explicit cell references. With a more complicated spreadsheet that is going to be a very big problem.
May 15 2018 08:49 AM
IMHO, Excel online correctly works with structured references, the only point you shall enter formulas with structured references manually, Excel online doesn't autofill them. It looks like
May 15 2018 11:19 AM
That has not been our experience. After editing the workbook in the client app, the formulas fail in the web app. We have tested this many times. Screen shots before and after are attached.
May 15 2018 11:37 AM
Can't reproduce that. Is such behaviour for any Excel file in your environment or only for the one we are discussing?
If you create simple file from scratch with the table and structured references formula (in Excel Online or in Desktop client), edit it in Desktop client (e.g add couple of rows plus change the formula with structured reference), save and edit in Excel online (that's what I did with above sample) will you have #REF?
May 15 2018 11:41 AM
It appears to be changing the document in the desktop client that breaks the online version when using structured references.
May 15 2018 12:08 PM
To be sure better to play with the simple file created from scratch, that's few minutes job. Your two existing documents may have the same issue.
May 15 2018 01:25 PM
I can't replicate it because I can't get webaccess to save it in the weird "Editable.xlsm" format like it did to the other files.
May 16 2018 12:44 AM