Analyze your Dynamics 365 data in Excel Online

Dynamics CRM 2016
 

Updated: November 1, 2016

Applies To: Dynamics 365 (online), Dynamics CRM Online

No need to leave Microsoft Dynamics 365 to analyze your data. Now you can do a quick ad-hoc analysis using Microsoft Excel Online in Microsoft Dynamics 365 (online).

For example, if you’re a sales manager, you might want to analyze the opportunities your team owns and review key performance indicators (KPIs) to see how you can assist your team members. If you’re a sales rep, you can open your opportunities in Excel and conduct what-if analysis for different incentive scenarios. Or, you may want to quickly open the data in Excel Online so that you can copy it somewhere else such as an email.

When you make changes to your data in Excel Online, you can save the updated information in Dynamics 365. Remember to keep the existing format of the Excel cells to prevent problems during import. Adding additional information to the spreadsheet, such as graphs, charts, or colors, will not be saved.

  • This feature requires CRM Online 2015 Update 1 or later.

  • This feature requires that you have an Office 365 subscription or a subscription to an online service such as SharePoint Online or Exchange Online. For more information, see What is Office 365 and how does it relate to Dynamics 365 (online)?

  • You need a Microsoft account.

  • You need export to Excel privileges in Dynamics 365.

The option to open data in Excel Online isn’t available in all Dynamics 365 record types. If you don’t see the option, it’s not available for that record.

System_CAPS_noteNote

Updated data in Dynamics 365 won’t immediately be reflected in Excel Online if the same view was opened in the last two minutes in Excel Online. After that timeframe, any updated data should show in Excel Online.

  • Open a list of records in Dynamics 365 (online). Click Export to Excel > Open in Excel Online.

Export Dynamics 365 data to Excel Online
System_CAPS_noteNote

By default, you can’t open your Dynamics 365 data in Excel Online in the Advanced Find view. However, you can save your advanced find as a personal view and then go to your personal view to do an ad-hoc analysis in Excel Online.

  1. On the top right, click Save Changes to Dynamics 365.

    System_CAPS_noteNote
    • The data for ad-hoc analysis with Excel Online is stored temporarily. Any additions, such as charts, calculations, and columns won’t be saved from the ad-hoc analysis that you do in Excel Online back to Dynamics 365. If you need to make lots of changes to your data and import it back to Dynamics 365, it’s recommended that you export the worksheet in Microsoft Excel.

    • The file import might fail if you made a lot of changes or changed the format of the Excel file. If you need to make lots of changes to your data and import it back to Dynamics 365, it’s recommended that you export the worksheet in Excel.

    • By design, you can’t do a File > Save As in Excel Online. If you do, you’ll get a Can’t Save Workbook error message.

  2. On the Data Submitted for Import dialog box, click Close.

After you save your changes from Excel Online to Dynamics 365, verify that the data has been imported back in to Dynamics 365 (online).

  1. In the Data Submitted for Import dialog box, click Imports.

    -OR-

    Go to Settings > Data Management. Then click Imports.

  2. In the list of imported files, look for your imported file and check the status.


Find out how to analyze your data and transform it into meaningful knowledge with Dynamics 365 (online) Excel integration.

<iframe width="500" height="364" src="https://www.youtube.com/embed/NZNvWz9xuZ0" frameborder="0" allowfullscreen></iframe>

To see video captions, click the Closed Caption (CC) button YouTube Closed Caption button in the lower-right corner of the YouTube window.

Banner for Dynamics 365 YouTube channel

Do you need to analyze your data from Microsoft Dynamics 365 and transform that data into meaningful knowledge that helps to drive sales?

Dynamics 365 allows you to do just that with your Dynamics 365 Excel integrations. Quickly export your data to either Microsoft Excel for Rich Client or Microsoft Excel Online for easy consumption. The new Export to Excel feature is very simple. With a single click you can export data to a standard Excel file that can be used on any client: Phone, tablet, desktop, as well as Excel Online. Data is exported in the same format as it is in Dynamics 365. You don't need to worry about data formats getting lost in translation; text will remain text, numbers will remain numbers, and dates will remain dates. You can export static or dynamic worksheets, both of which are importable. But what if you need more advanced functions?

You can also export a Dynamic Pivot Table, which makes it very easy to tabulate and summarize data. Also, analyzing large data sets is not a problem - Dynamics 365 can export up to 100,000 rows. Sometimes you may need more reports that are not in your standard business analytics tool box. You can now quickly perform data analysis on Dynamics 365 data by using the ad hoc analysis with Excel Online.

Let's demonstrate a quick example.

Select Export to Excel, open Excel Online, and your data will be immediately available in Excel Online. Now let's perform a quick analysis on the data. Let's determine the expected revenue for open opportunities. 0h, and my colleague just informed me that the estimated revenue for one opportunity went up ten thousand dollars. Let's make the change and send it back to Dynamics 365. Okay, now let's cover the details about this functionality. Ad hoc analysis with Excel Online is only available for Dynamics 365 (online) and does require an Office 365 license. In addition, the user will require Export to Excel privileges in Dynamics 365. The data for ad hoc analysis with Excel Online is stored only temporarily. Any additions, such as charts, calculations, and columns won’t be saved from the Ad hoc Analysis Excel Online Editor back to Dynamics 365. If you need to perform heavy lifting with the Dynamics 365 data, we recommend you export the worksheet for use in Excel Desktop.

Enjoy the new data analysis features in Dynamics 365. We hope that you can leverage your data to bring you new insights about your business. Thanks for watching.

Show: