Sage 100 – Export Sage 100 Data to Excel transcript:
Welcome to SWK’s video series on Sage 100.
This video is going to demonstrate how to export information from Sage into Excel.
On screen, we’re looking at the subscription version of Sage 100, Version 2020. Let’s start with Customer Maintenance. The very first place we can export to Excel is in the look-up list.
When you’re looking at either this list or any other list that you have customized, you have an Excel button. Clicking on this will then export whatever is in this list very nicely to Excel. Let’s exit out of this and let’s move to one of the customers and see what our options are. Coming to the History tab, if I right click in this list, once again I have a little icon to export to Excel and you’ll see that it comes out perfectly.
If I look at the Invoices tab or the Transactions or Sales Order tab, all of them have an Excel button and as you can see it’s not lit up. I have to click on any one of the line items, and now I can export whatever is in this list to Excel. And you can see that it exports beautifully.
I want to show you something else in Sales Order. I’m going to drill into this sales order and this is not very apparent, that you can export the lines into Excel. Once again, I need to right click and I have my “Export to Excel” and this is going to create a worksheet with all of the line items of the sales order. And, once again you can see it exports beautifully.
Let’s come out of the sales order and out of the customer and we’re going to take a quick look at an Explorer View. At the bottom of the menu item list, for each module, there is the Explorer View with several options. We’re just going to look at the Customer View. In looking at this sheet, there’s a lot of data in here and of course hopefully for you know that you can modify this. But what we’re going to do is look to see how to export this to Excel.
Coming up to File, Export Options, we see we have an option for Excel. And, once again this will export very nicely into Excel. With this however, we are going to have to expand some of the columns. Unlike the other ones where it comes over perfectly, I may have to come in to make a couple of changes to the column widths.
If I want to export to Excel something that is in the secondary grade, let’s go ahead and choose “History Invoices”. And if I double click on my customer, it’s going to bring the secondary into the primary grid and from here, now, of course, I can export to Excel.
One more area where we can export information into Excel is through Reports. Not all reports will export as perfectly as you would like, but I’m looking at an accounts receivable aging report. I’m going to preview this and then I will export this to Excel and you’ll see that it comes up quite nicely. I find that clicking on the “Maintain Relative Object Position” helps keep my column headers in position to the data. And, we’ll see what happens when this comes up. I do often times have to modify the row height. But once I do this, you can see that everything lines up quite nicely. Again, I may have to modify some of the column widths, but as you can see everything comes over quite nicely. Now, I do have a column here without a header and that’s because this is the column for Retention. And because I’m not using retentions, there’s no column header.
Now, I have to tell you, I did make a few changes in the Crystal Reports to be able to have this come over as nice is this. The first thing that I had to do, the column header that says “Customer and Invoice” is combined. What I did is: I separated them and made sure the one for the header of Invoice Number was directly above the details for the invoice number.
Also, there was a field in here for the phone number. It was a text for the header Telephone Number and then the data, and I removed those. With those two fields in there, I ended up with some empty spaces. By taking them out, everything lined up perfectly. Now, if the phone number was important, what I would have done is: I would just reposition it so that it came directly under a header, and size the data appropriately, and then I would have had all of my headers come over just as it is now. Just a couple little tricks there to make sure that your Crystal Reports can export to Excel as well.
As you can see there are a number of options that you could deploy to show your data inside Sage into Excel. This is true for many of the modules, especially when we’re looking at General Ledger, Customers, Vendors, Inventory Items. You have so many options. Hope you’ll give them a try.
Hope this helps you get more out of Sage 100. And, thanks for watching our video!
Stay Up on the Latest Sage 100 News with SWK Technologies
SWK delivers the latest news, updates, and tips & tricks for Sage 100 and Sage 100cloud users to help them capture the most value from their software. View any of our Sage 100 video resources to learn more about what your Sage ERP can actually do.Contact us if you have any questions on how to best use your Sage 100 or Sage 100cloud software.
Talk to the Sage 100 Support Experts