If you work with Microsoft’s Finance & Operations app, you know how powerful and versatile it is. But did you know that you can also use Dynamics 365 Finance & Operations Excel Add-In to access and update your data in Dynamics 365? In this blog post, we’ll explain how to use the Excel add-in for Dynamics 365 Finance & Operations, and how it can make your life easier.
What is the Excel add-in for Dynamics 365 Finance and Operations?
The Excel add-in is a feature that lets you connect Excel to your Dynamics 365 Finance & Operations environment and use it as a data entry and reporting tool. You can open any data entity from Dynamics 365 in Excel, make changes, and publish them back to Dynamics 365. You can also create templates and reports in Excel that use live data from Dynamics 365. It is built on top of the data management framework and utilises dynamic connections to data entities to retrieve, update, and add data to Dynamics 365 Finance & Operations.
Practical Use-Cases of the Excel Add-in for Finance & Operations
- Data Entry and Update: The sales manager wants to update the sales targets for the next quarter. They can use the Excel add-in to open the “Sales Targets” data entity from Dynamics 365 Finance & Operations directly in Excel. They can then update the targets in Excel and publish them back to Finance & Operations. This way, they can leverage Excel’s user-friendly interface and features for data entry and editing.
- Reporting and Analysis: The company’s analyst wants to create a report showing the sales performance for the past year. They can use the Excel add-in to create a template in Excel that pulls live data from the “Sales Performance” data entity in Dynamics 365 Finance & Operations. This allows them to use Excel’s powerful data analysis and visualisation tools (like pivot tables, charts, etc.) to analyse the sales data. Since the data is live, the report will always show up-to-date information when opened.
- Adding Data: A new product line is launched, and the product manager wants to add this new product information into the system. They can use Excel to enter all the product details and then publish it back to the “Product Information” data entity in Dynamics 365 Finance & Operations.
- Inventory Management: An inventory manager can also use the Excel add-in to manage stock levels. They can open the “Inventory Levels” data entity in Excel, update the stock levels after a physical count, and then publish the changes back to Dynamics 365. This can be particularly useful for bulk updates.
- Financial Reporting: A financial analyst can create complex financial reports using live data from Dynamics 365 Finance & Operations. For example, they can create a Profit & Loss statement in Excel that pulls data from the “General Ledger” data entity. This allows them to leverage Excel’s capabilities for financial analysis.
- Budget Planning: The finance department can also use Excel for budget planning. They can open the “Budget” data entity in Excel, enter their budget forecasts for the next fiscal year, and then publish it back to Dynamics 365. This allows them to use Excel’s forecasting tools in conjunction with Dynamics 365 F&O.
- Customer Relationship Management: A sales representative can manage their customer interactions using Excel. They can open the “Customer Interactions” data entity in Excel, log their interactions with customers, and then publish it back to Dynamics 365 Finance & Operations. This allows them to use Excel as a CRM tool.
These examples illustrate how versatile and powerful the integration between Excel and Dynamics 365 Finance & Operations can be.
Why use the Excel add-in for Dynamics 365 Finance & Operations?
The Excel add-in can help you save time and improve accuracy when working with data in Finance & Operations. Here are some of the benefits of using the Excel add-in:
- Seamless User Experience: It enables Excel to become a seamless part of the Dynamics 365 Finance & Operations user experience, enhancing productivity. You can use familiar Excel features and functions to manipulate data, such as filters, formulas, charts, pivot tables, etc.
- Data Management: It allows you to retrieve, update, and add data to Dynamics 365 Finance & Operations directly from Excel. This makes managing your operations smoother and more efficient.
- Dynamic Connection: The Excel Add-in creates a dynamic connection to the data entity which can be used to accurately update, create, and refresh data to and from the system.
- Data Filtering: You can filter the data to focus on specific information. For instance, only displaying male customers below 35 years of age or selecting customers who reside within 5 km of a store location.
- Design Modification: You can modify the design of your data presentation.
- Calculated Fields: You can also create calculated fields for more complex data analysis.
- Dynamic Reporting: The Excel Add-in serves as a strong reporting tool that can be connected directly to your Dynamics 365 Finance & Operations that opens the doors to comprehensive reporting and informed decision making.
- Offline Work: You can work offline with data in Excel, and later sync it with Dynamics 365 Finance & Operations when you’re online. That adds another layer of flexibility to the Excel Add-in experience.
By leveraging these features of Finance & Operations Excel Add-in, you can drive many operational benefits, such as:
- Flexibility: Users can work with familiar tools while benefiting from integration with a powerful business management solution.
- Efficiency: By enabling users to manipulate bulks of data and creating custom reports and templates directly from Excel, businesses can save time and increase productivity.
- Accuracy: The add-in reduces manual data entry errors by automating data transfer between systems.
How to Use Dynamics 365 Finance & Operations Excel Add-In?
When You Start from Excel
1. In Excel, on the Insert tab, in the Add-ins group, select Store to open the Office Store.
2. In the Office Store, search on the keyword Dynamics, and then select Add next to Microsoft Dynamics Office Add-in (the Excel add-in).
3. If you’re running the Excel add-in for the first time, select Trust this Add-in to enable the Excel add-in to run. The Excel add-in runs in a pane on the right side of the Excel window.
4. Select Add server information to open the Options pane.
5. In your browser, copy the URL of your target finance and operations app instance, paste it into the Server URL field, and then delete everything after the host name. The resulting URL should have only the host name. For example, if the URL is https://abc.dynamics.com/?cmp=usmf&mi=CustTableListPage, delete everything except https://abc.dynamics.com.
6. Select OK, and then select Yes to confirm the change. The Excel add-in is restarted and loads metadata.
The Design button is now available. If the Excel add-in has a Load applets link, you likely aren’t signed in as the correct user. For more information on addressing this issue, see the Load applets troubleshooting entry.
7. Select Design. The Excel add-in retrieves entity metadata.
8. Select Add table. A list of entities appears. The entities are listed in “Name – Label” format.
9. Select an entity in the list, such as Customer – Customers, and then select Next.
10. To add a field from the Available fields list to the Selected fields list, select the field, and then select Add. Alternatively, double-click the field in the Available fields list.
11. After you’ve finished adding fields to the Selected fields list, make sure that the cursor is in the correct place in the worksheet (for example, cell A1), and then select Done. Then select Done to exit the designer.
12. Select Refresh to pull in a set of data.
When You Start from Dynamics 365 Finance & Operations
1. On a page in a finance and operations app, select Open in Microsoft Office.
If the root data source (table) for the page is the same as the root data source for any entities, default Open in Excel options are generated for the page. Open in Excel options can be found on frequently used pages, such as All vendors and All customers.
2. Select an Open in Excel option and open the workbook that is generated. This workbook has binding information for the entity, a pointer to your environment, and a pointer to the Excel add-in.
3. In Excel, select Enable editing to enable the Excel add-in to run.
4. If you’re running the Excel add-in for the first time, select Trust this Add-in.
5. If you’re prompted to sign in, select Sign in, and then sign in by using your credentials for the finance and operations app. The Excel add-in then automatically reads the data for the entity that you selected.
Once you have opened entity data in Excel by using either method, you can view, update, and edit the data by using the features of the Excel add-in.
Tips and Tricks for Using the Excel Add-In for Dynamics 365 Finance & Operations
Here are some tips and tricks that can help you use the Excel add-in for Finance & Operations more effectively:
- To refresh or update entity data in Excel, use the Refresh or Publish buttons on the Dynamics 365 tab of the ribbon.
- To filter or sort entity data in Excel, use the standard features of Excel such as the filter arrows, sort buttons, or slicers.
- To add or delete entity records in Excel, use the Add record or Delete record buttons on the Dynamics 365 tab of the ribbon.
- To create or edit entity templates in Excel, use the Create template or Edit template buttons on the Dynamics 365 tab of the ribbon. You can also use the Template gallery button to access a collection of predefined templates for common scenarios.
- To validate entity data in Excel, use the Validate button on the Dynamics 365 tab of the ribbon. You can also use the Error list button to view and fix any errors or warnings that occur during validation or publishing.
- To customise the Excel add-in settings, use the Settings button on the Dynamics 365 tab of the ribbon. You can change settings such as the number of records to read or publish, the default company to use, or the language to use.
Conclusion
The Excel add-in for Dynamics 365 Finance & Operations is a handy tool that can help you work more efficiently and effectively with data in Dynamics 365. It lets you leverage the power of Excel to access, update, and analyse your data in Dynamics 365. If you haven’t tried it yet, we encourage you to give it a try and see how it can improve your productivity and performance. And should you need any help or consultation about Dynamics 365 Finance & Operations, Dogma’s D365 experts are here to help!
