Skip to main content

Dynamics 365 Business Central Tips and Tricks: Displaying Dimensions When Using the Edit in Excel Function

Published Date
Author Timothy Sparks

In this series of bimonthly blogs, Western Computer shares our expertise with tips and tricks to help users work more efficiently in Microsoft Dynamics 365 Business Central. Based on our more than 30 years in business—with 1,100+ solution implementations, including more than 200 in the cloud—we share the expertise we have accumulated to enable users to work as proficiently as possible. You can also subscribe to these blogs in our LinkedIn newsletter. 

It’s a question we hear often from our customers: Is there a trick to get all the Dimensions to show up when you use the <Edit in Excel> feature in Dynamics 365 Business Central?

Take, for example, shortcut Dimensions, which are categories that facilitate analyses. They are not as easily viewable for reporting as is the case with global Dimensions. However, you can add shortcut Dimensions by using the <Design> function after you open the <Edit in Excel> workbook—as shown in the image below for shortcuts #3 through #8:

Adding shortcut to dimensionsDesign connector adding shortcut in excel

Short cut added to data connector

Pre-Configured Integrations in the Cloud

For Business Central on-premises customers, the Edit in Excel action is only available for the web client and only if the Excel add-in has been configured by the system administrator. However, users of Dynamics 365 Business Central in the cloud benefit from the pre-configured integrations between Excel and Dynamics 365 Business Central.

And because Excel is one of the most commonly used tools in financial management, Microsoft continues to add enhancements for using the add-in for Excel with Dynamics 365 Business Central. A recent enhancement involves publishing changes in Excel back to Dynamics 365 Business Central. When users select the <Edit in Excel> action from a list page, most filters set on the list page are applied to the list when it is exported to Excel. This enables publishing the changes back to Dynamics 365 Business Central from a filtered list of records in Excel.

In addition, the number of fields for reading and publishing edits from Excel is expanded. This includes edit fields added through extensions. The <Edit in Excel> action has also been enabled on various sales and purchase document subpages, such as <Sales Order Lines>.

Here’s a rundown of how to make changes to records in Excel and then publish the changes back to Dynamics 365 Business Central:

  • To get the latest data from Business Central, choose <Refresh> in the Excel add-in pane.
  • When Excel opens, you'll see the Excel add-in pane on the right.
  • With this action, Excel respects most filters on the page that limit the records shown.
  • The Excel workbook will contain almost the same records and columns as Business Central.

If your organization has configured OneDrive for system features, the Excel workbook is opened in your browser by using Excel for the web. If you're not using OneDrive for system features, the workbook is downloaded to your device. From there, you can open it using either the Excel desktop app or Excel for the web.

Integrations in the Cloud Create Immersive User Experiences

The connection between Dynamics 365 Business Central and Excel demonstrates the integration capabilities users simply can’t get with an on-prem version of Business Central. It takes a lot less time to set up and maintain integrations when you tap into the Microsoft tech stack in the Azure cloud. The net result from these streamlined integrations is that your employees can work more efficiently, and you free up the time of your IT team to focus on strategic IT initiatives.

With the integration of Dynamics 365 Business Central and the full stack of Microsoft applications that the cloud enables, your users can also quickly access data in any application—from within any application they are working. They benefit from an immersive Microsoft experience that helps them increase their productivity and respond more quickly to customer needs.

To Learn More

Western Computer offers deep expertise across the Microsoft technology stack, particularly in Dynamics 365 Business Central. Our thought leaders can help you ensure your critical ERP application meets your business needs for today and beyond. For more information on how to edit Dimensions in Excel from Dynamics 365 Business Central, or for help in designing, deploying, or supporting your Microsoft Dynamics 365 solution, contact Western Computer today.