8 more things you may not know about Immersive Excel

David Mochrie, 17 November 2020

Back in 2016, I wrote a blog 10 things you may not know about immersive Excel.

In the intervening years (wow, has it been that long?!) I’ve discovered a few more things about immersive Excel that may interest you. I’ve not managed another set of 10. Just 8 this time.

Before we start, to refresh your memory, ‘immersive Excel’ is the term sometimes used to describe the ‘Open in Excel Online’ feature:

This allows you to export data in a view from Dynamics 365 to Excel Online, edit the data there, and then save those changes back to Dynamics 365 . You never leave Dynamics 365 , the Excel Online window opens right there within Dynamics 365 . It’s a quick way of editing multiple records for the same entity very quickly.

So, here goes:

1) What if the record is amended after you export to Excel Online, but before you re-import?

The record won’t update. You'll get an error when you try to re-import it. This is the same as a traditional export and import.

2) What if you export to Excel Online, and then add a column of data to the spreadsheet? Will it update the Dynamics 365 data?

Yes, it does. I tested it by adding 'Fax' as a column in Excel Online for contacts. I added a value to a record in this Fax column, and it imported the data just fine. Please note, the column name must be the field display name and is case sensitive. I tried it with the column name 'fax' and it didn't import. I also changed the label on the form to 'Fax2' and then added 'Fax2' as the column name in Excel Online and it didn't work, but it still worked with 'Fax' as the column name as that was still the display name of the field.

However, (and this is a very big ‘however’), it seems that if you add a column, and don't update a record that has a value in that field already, and then import the Excel Online spreadsheet to Dynamics 365 , the value in the field in Dynamics 365 is removed. This is very dangerous. You could potentially wipe the data from a field for a large number or even all the records in Dynamics 365 .

The simple answer is, don't add columns when using Immersive Excel.

3) Can you amend composite fields in Excel Online?

No, but the behaviour is different depending on the composite field.

For Contact full name, if you have the full name in your view and export to Excel Online, you get the full name field in a column and the first, last and middle names each in a column, even though you didn’t have the first, last and middle names in the view. If you update the full name in Excel Online, and save to Dynamics 365 , it doesn't change. However, if you change any of the first, last or middle names, those changes are saved within Dynamics 365. (incidentally, if you have the full name, AND the first, middle and last names in a view and export to Excel Online, it doesn't put the first, last and middle names in the spreadsheet twice. It's smart enough to see they are in there already and not add them again just because the full name is there. This is good.)

For Address, if you put the composite address field in the view, it shows the full address in the view, but when you export it to Excel Online it only shows the first line of the address in the column. When you try to import it back in, you get an error. So, don't include the address composite field in a view if using immersive Excel.

4) What if you amend a field which is used in a Dynamics 365 calculated field in Excel Online, i.e. a simple filed which is an input to a calculated field?

It works fine. Once the data is refreshed in Dynamics 365, the field recalculates. This all works server-side of course, so you don't even have to open the record form.

5) What if you amend a calculated field in Excel Online?

It lets you update it in Excel Online, but doesn’t have any impact on the record when you re-import it, i.e. the original, correct value for the calculated field is retained. This is good. It would be better if it didn't let you update it in Excel online at all.

6) What if you amend a roll up field in Excel Online?

Just like a calculated field, it lets you update it in Excel Online, but doesn't have any impact when you re-import it, i.e. the original, correct value for the rollup field is retained. This is good. Again, it would be even better if it didn’t let you update it in Excel online at all.

7) Can you use immersive Excel to deactivate records?

You can, but you must be very specific in how you do it.

If you have both the Status and the Status Reason on the view and you change just one, it doesn't work, as it sees that the status and status reason are misaligned. You have to change both, and the must align, i.e. the Status must be ‘Inactive’ and the Status Reason must map to the ‘Inactive’ Status in Dynamics 365.

If you put just the Status Reason on the view it also doesn't work, as it doesn't change the Status when the Status Reason changes. This is surprising to me, and unnecessary as the Status Reason can only map to one Status so Dynamics 365 should just change it to that Status, but it doesn't.

If you put just the Status on the view, it works. It sets the Status Reason to be whatever the default Status Reason is for that Status. This is OK, but not so good if you want the Status Reason to be something other than the default.

In summary, if you want to deactivate a lot of records, do it with bulk deactivate, not immersive Excel.

8) What if you have read only security rights to a record? Can you change it using Excel Online?

No. You can change it in Excel Online but when you re-import it, the import for that record errors, saying you don't have enough privileges. This is good. Would be better of course if it didn’t even let you change it on Excel Online, i.e. before import.

So, there you go, a few more things to consider when using immersive Excel.