As you would expect, with Microsoft being the company behind both Office 365 and Dynamics 365, there are some powerful integrations between the two. The tight integration between Microsoft products is one of the reasons companies choose Dynamics 365 for their CRM or ERP system.
Excel is one of Microsoft’s most defining products and it is used extensively throughout the Dynamics 365 CRM system. Because our business consultants work with both Excel and Dynamics 365, we have found many ways to integrate both products and improve the efficiency of business processes.
Here are 5 important ways Excel makes Dynamics 365 CRM better.
Excel Templates
The Dynamics 365 CRM includes charts, dashboards, and reports which will meet many of your data analysis and reporting needs. It can also be a benefit to utilize the power of Microsoft Excel when an analysis is more complex, or a dashboard needs to be more dynamic. This can be accomplished by using .
An Excel template merges your CRM data with a workbook you have built to create the exact output you are looking for. One common way Excel templates are used is to create a for a selected customer or salesperson. Excel templates are also used to create professional customer proposals which combine customer specific CRM data with standard company proposal language and formats.
Excel Process Integrations
One of our favorite is to use Excel VBA to push data into the CRM. We usually accomplish this by creating a SQL database on Microsoft Azure that serves as an integration link between Excel and Dynamics 365. VBA can access SQL Azure easily and then Power Automate is used to handle the connection between SQL Azure and the CRM.
Here are a few ways we have used this type of integration:
- Quick Logging: We have created a very quick way to add a new Account, Contact, and Opportunity by using a simple Excel spreadsheet. Instead of adding 3 records to the CRM, a total of 8 fields are entered in a simple Excel spreadsheet and pushed to the CRM with a single button click. This leaves no excuse for not logging new opportunities in the CRM because it literally takes 60 seconds or less.
- Time Tracking: We have created a Time Tracking workbook in Excel that pushes time entries into the CRM for project costing and tracking. This approach allows many users to push their time entries into the CRM without having a Dynamics 365 license for each person.
- Quote Applications: For several clients, we have linked their quoting and product configuration spreadsheets to the Dynamics 365 CRM. This allows our clients to use their existing quoting method but also syncs their data with the CRM. This integration allows many users to create and consume CRM data without having a Dynamics 365 CRM license.
- Accounting System Integrations: In many cases, it is a big benefit to have sales and cost data from an accounting system integrated into the CRM. This provides key trend and performance data for the sales team. To create this integration, we often use Excel to extract required data from the accounting system, transform the data with VBA macros, and then push it into the CRM.
- Advanced Reporting: When VBA or advanced formulas are required to produce complex reports, we can easily bring CRM data into Excel and manipulate it as required.
Export to Excel
Any list of records in the CRM can be quickly exported to Excel with a click of a button. Our clients use this feature constantly because it provides a quick way to filter, analyze, and understand data. This feature is also used to export CRM data for import into another system.
Open in Excel Online
A very useful variation of the “Export to Excel” feature in Dynamics 365 CRM is to open any list of data in Excel Online. This allows the user to modify the data as required and click a “Save” button to automatically push the data back into the CRM. This feature saves a lot of time when batch data updates are required.
Import from Excel
Excel is used to import data into the Dynamics 365 CRM system. This approach is used during a new CRM implementation, but is also used after that whenever mass additions or updates are required. For example, the customer names in the CRM sometimes require modification to maximize the search capability of the CRM. We use Excel in this case to export all data from the CRM, modify it in Excel, and then push it back into the CRM with an Excel import.
Summary
Dynamics 365 integration with Office 365 is a big win for user productivity and is one of the key reasons that companies choose Dynamics 365 as their CRM. Microsoft Excel integration with Dynamics 365 provides many ways to visualize your CRM data, update your CRM data efficiently, and integrate other business processes into your CRM.
About the Author
Brian Busscher is the Founder and CEO of Thesis Technologies which pursues a mission to “help you get your systems right.” If you would like to learn more about the Dynamics 365 business applications or Microsoft Excel, please contact the Thesis team at (888) 705-7253.
Find other Dynamics 365 blog posts here.
This blog post has been updated.