How to Connect Excel to Dynamics 365 (CRM)

Microsoft Dynamics 365 is a high-end CRM solution that stores and helps manage vast amounts of information, and it provides an interface for that purpose. However, there is an alternative way. You can connect Microsoft Excel to Dynamics 365 and enjoy the advantages of Excel's familiar environment and advanced data analysis and visualization tools. All you need to do that is Devart Excel Add-in for Dynamics 365. It enables two-way data synchronization, allowing you to load data from Dynamics 365 into an Excel workbook, edit it, and save it back to Dynamics 365.

In this tutorial, we'll see how to connect Excel to Dynamics 365.

Configure a connection to Dynamics 365 from Excel

1. Download Excel Add-in for Dynamics 365, open the downloaded file, and install the add-in following the instructions in the setup wizard.

2. Open Microsoft Excel and either start a new workbook or open an existing one.

3. On the toolbar, go to the Devart tab. This tab appears automatically once the Excel Add-in is successfully installed. If the Devart tab isn't on the toolbar, you may need to restart Excel.

4. On the Devart tab, click Get Data.

5. In the Import Data Wizard that opens, select Microsoft Dynamics 365 as the data source.

Next, specify the Dynamics 365 connection parameters:

• Authentication. Select the method you prefer:
• UserNamePassword. You'll need to provide your Dynamics 365 account credentials in the following fields.
  
  • Server: Enter your Dynamics 365 URL.
  • User Id: Enter the email address used to log in to Dynamics 365.
  • Password: Enter the Dynamics 365 account password.
• RefreshToken. You'll be able to log in with your Dynamics 365 account in a web browser.
  
  • In the Server field, enter your Dynamics 365 URL.
  • Click Web Login.
  • Sign in to your Dynamics 365 account.
  • Review the list of required permissions, select the Consent on behalf of your organization checkbox, and click Accept.

6. In addition to the connection parameters, you can specify whether to:

• Save add-in-specific data in the Excel worksheet.
• Save the connection string in the Excel worksheet.
• Save security information.
• Reuse the connections in Excel.

7. If you need to include additional options in the Dynamics 365 connection string, click Advanced and enter the required values. To save the changes, click OK. To learn more about advanced options, refer to Advanced Connection Options.

8. Click Test Connection. The Successfully connected message should appear.

9. Click Next.

Import data from Dynamics 365 into Excel

After a successful connection, let's see how to load Dynamics data into Excel.

1. In Import Data Wizard, switch to the Visual Query Builder tab and select the required database object in the Object list. Here, we are going to select the Customers table.

2. When you are ready to proceed, click Finish to import data and start editing.

Load data using an SQL query

The Visual Query Builder is not the only way to load Dynamics 365 data to Excel. To preview or customize the SQL query, you can switch to the SQL Query tab in Import Data Wizard.

On the Preview Data page, preview the data and specify the Excel import options:

• Create a new worksheet for the imported data.
• Replace data on the existing worksheet with the imported data.

4. Once done, click Finish.

Live edit Dynamics 365 data in Excel

Devart Excel Add-in for Dynamics 365 allows you to conveniently edit data directly in the worksheet. To edit data in the table, you will need to start an update session. To do this, click the Edit Mode button. Now, you can edit data in the imported table like you usually do.

• Modify data

Modify individual cells; after modification, they are highlighted in olive.

• Add new rows

By default, the empty row at the bottom of the table with empty cells will be highlighted in green. In order to add new rows, simply fill in the cells of the said row and press Enter.

Alternatively, right-click on the number of the row to insert a new row before the selected one. Then, click Insert and enter the required data. The new row will be highlighted in yellow.

• Delete rows

Select the rows you wish to remove, make a right click, and select Delete from the menu. The deleted row(s) will be highlighted in dark yellow.

Upload Excel data into Dynamics 365

Now that we know how to import data from Dynamics 365 into Excel and how to manipulate it, we can move on to how to upload the modified data back to Dynamics 365. The Commit button serves this exact purpose. Thus, as soon as you are content with the looks of your data, feel free to click this button:

In case you have committed something that you didn't mean to, there's always an escape route. To undo what was done, click the Rollback button.

Conclusion

The integration of Microsoft Excel with Dynamics 365 streamlines data management by allowing you to easily connect, import, edit, and load data between these platforms. This enhances the efficiency of your management, giving you more control over your e-commerce data, handled directly from Excel. And since there is nothing quite like firsthand experience, we invite you to download Devart Excel Add-in for Dynamics 365 and see it in action during a free trial. To maximize your reach, consider downloading the Universal Pack.