Everyone in this industry has used Excel. So, let me start by saying I’m not going to try to tell you how to use it, there’s enough online courses for that!
What we will catapult into (bad joke, I know), is the awesome functionality between Microsoft Dynamics Business Central and Excel. This functionality has been utilized in many ways, by different types of users. We all know most ERP’s export and Excel spreadsheet and it’s super helpful. But what sets the compatibility with Business Central and Excel apart? It could just be enough to make you want Business Central if you don’t have it.
There are four key uses in Business Central for Excel: Open in Excel, Copy & Paste from Excel, Edit in Excel and Configuration Packages.
From an End-User perspective, Open in Excel is the well-known use. You run a report and then can open it in Excel and do as you please. This use will never go away. Maybe you need to flip data into Pivot Tables or build out a dashboard of Excel graphs to present. Whatever your use for this, it’s still there. What did happen, is it is more accessible and open to a wider range of uses. Open in Excel is an option in the menu ribbon on every list page and essentially every report. Widening the use of this is more helpful than a game changer, but we’ll take any help that’s offered up. In addition, Print to Excel has been added as a toggle on many reports which changes the report layout to be Excel friendly. A nice little bonus.
Another feature that builds upon this is Copy & Paste. One of the oldest tricks in the book and pretty self-explanatory. However, in the design of Business Central, this core use was considered. You can copy a row from your journal, copy it and then paste this row into Excel. This doesn’t just take your data in that row, but it takes all the header for your journal and puts them in Excel too! You can save this as a template to build out journals that need to get copy and pasted on a regular basis to make it super easy (although check out Standard Journals and Recurring Journals in Business Central too!)
Where Microsoft really upped their game is with Edit in Excel. Now, we’re not talking about Opening in Excel and changing the data. Edit in Excel opens your data in Excel and has an Excel Add-in on the right-hand side. You can make changes to the data and then Add-in has a ‘Publish’ button that can click to send your changed data right back to Business Central as if you saved the Excel sheet and imported it. It is fantastic for mass changes.
Where this functionality can become really efficient is when combined with filters. When you Edit in Excel, only filtered data gets exported. This can stop you scrolling through a head of data to make the changes to the information you need. A perfect example is sorting your Customer List by a blank field so that you can add in the missing information for those without it. Maybe you want a quick way to add in a bunch of Dimension Values for Different Dimensions (you can do it all on the same Edit in Excel sheet) or you want to add a heap of lines to a General Journal. Edit in Excel will also give you any validation errors right here when you try to publish, allowing you to quickly make your corrections here and publish again. This is definitely one of our favorite features.
My example below shows finding all Customers with a missing phone number, ready to be added in Excel and published right bank to Business Central.
The final key use of Excel is for Configuration Packages. If you are someone considering moving to Business Central, you will love this one! Now, this one will not interest most End-Users as it is likely you will never see a Configuration Package whilst using Business Central. A Configuration Package allows us (your favourite Microsoft Partner) to load tables of information into Business Central from and Excel sheet.
What does this mean for you? Well, you’ve just started your bold journey to implement Business Central as your ERP solution and now you are wondering how to put all your company’s data in the system. Here’s how. We simply give you an Excel field with a page for each piece of data (Customer, Vendors, Bank Accounts, Company Information, Dimensions etc). Since we have already established that everyone in this industry has used Excel you simply copy and paste this information from the Excel export from your old system into these sheets from us (clear out any old stuff in between!) and then we load it in. It makes your life simple in a format you are comfortable with. Configuration Packages are hugely helpful when migrating client data and setting up a fresh Business Central solution.
Business Central and Excel are awesome together. The only thing to stop you making use of all these awesome features is messy data formatting. We know Excel hates that (preceding zeros, decimal points etc). You can check out our last blog, Rethinking our Master Data with Business Central, to help you out with that! Whether you’re on Business Central and looking to get the most out of its compatibility or if you’re considering implementing Business Central as your ERP solution, this functionality is something to make your life a little easier!
