I have seen a few blogs on how to connect to dynamics crm but most of them talks about connect with Odata endpoint. However with dynamics 365, Odata has been deprecated hence you cannot use them and will get error.
Below is the step by step process to connect to dynamics crm 365 online from Excel.
- If you do not have power query option in your excel or its an old version. Please download the excel addin from here : https://www.microsoft.com/en-us/download/details.aspx?id=39379 and run the setup chose 32 or 64bit as per your excel.
- Once done, Launch Excel and select “Power Query” Tab –>”From Online Services” Option and select “From Dynamics 365(Online) from the dropdown.
- In the next tab add Web Api endpoint of your online CRM ( you can find it from Customizations–>Developer Resources–>Copy “Instance Web API” url and paste it below:
- In the next authentication screen – select “Organizational Account” and then click on “Sign In”.
- Following step 4 will take you to your organization sign page. Enter username and password and click Sign In.
- If Signed In successfully; you will be landed back to the authentication page. Click “connect”.
- Post this you will be presented with the connected org details( such as entities etc).
I hope this helps.
Passion Dynamics 