Some may consider this to be a controversial recommendation but hear me out on this one. Giving a user permission to Edit in Excel as a means to find and work with data is a viable option. Not as “scary” as it used to be, Edit in Excel may just be what you need. Hear me out on this one before you get your panties all in a bunch about recommending Edit in Excel for viewing and working with data.
First off, did you know we can limit the use of Edit in Excel to view only?
So now that you know we can give permission to Edit in Excel without concern of the users actually editing the data in Business Central through Excel, you may be thinking, why bother? Isn’t this the same as Open in Excel? Nope, it’s not.
Open in Excel will only include the fields on the page in the exported file. Edit in Excel will include all fields available to a user whether visible on the current page or not. I teach users to eliminate or at a minimum reduce the amount of scrolling you need to do in list pages. Therefore, few and only the fields used on a regular basis are included on their list pages. I don’t want a user to add fields to their list just to get them in an Excel spreadsheet.
Let me show you what happens when using the Customer List page with Open in Excel and with Edit in Excel. I think the results will tell the story.
Open in Excel exported 11 fields with values from my customer list page.
Edit in Excel exported 63 fields including Last Date Modified, all address fields, email, posting details, Tax Area Code, and payment terms to name a few.
True, the fields in the exported file may not be something you want all users to see. Assigning permission to Edit in Excel – View Only should be taken seriously.
If you do have permission to edit the data when using Edit in Excel, be careful not to make any edits and unintentionally publish them back to BC. I don’t find this to be something you can do accidentally. Regardless, to prevent this from happening, save the Excel workbook as a different name, then close the Edit in Excel workbook. In this way, I have the data to work with and am no longer connected to Business Central.
Controversial but effective, use Edit in Excel to find and work with data. I find that Edit in Excel along with Page Inspection provide most users with the information they need when troubleshooting issues related to posting or other everyday activities in Business Central.
Let me know your thoughts in the comments. I would love to hear from some of you!
