Exporting to Excel from Sage 100 is so easy!

Posted by Marty on December 11, 2019 at 10:52 AM
Find me on:

Did you know that you can export to Excel from look-up screens and any Grid in Sage 100? Here is a Bank Reconciliation example of exporting from a Grid:

Bank Reconciliation > Main > Check, Deposit and Adjustment Entry or Reconcile Bank – Just right click anywhere on the Grid then choose Export to Excel at the bottom of the menu.Export to Excel in Sage 100Bank Reconciliation > Main > Check, Deposit and Adjustment Entry or Reconcile Bank

Think of the places where this could be used: GL General Journal Entry, AR Invoice and Cash Receipts Entry, Payroll Data Entry, Sales Order and Invoice Entry, Purchase Order Entry…. and the list goes on.

What about Masterfile Lookups that can be customized to show any fields you want to see and then export to Excel? An example of that might be a list of your 1099 Vendors to make sure you’ve filled out the Company Tax ID behind the Vendor. In addition, you’d like to see the last time you paid the Vendor, so you don’t have to bother with Vendors who didn’t have any activity in the current year.

Here’s how to set up a Customized Lookup Screen – in this case, for the 1099 Vendors. Navigate to Accounts Payable > Main > Vendor Maintenance. Click the Custom button in the lower left corner, choose ‘Create new view’, and give it a Description (1099 Vendors for our example here). Don’t check the Default View button unless you always want this Lookup Screen to be the “Default Screen” when you use the magnifying glass or F2 Key in the future to see a list of your Vendors. Click Next.Accounts Payable > Main > Vendor Maintenance

The list of columns on the right is what you are going to display. Available fields are on the left. You can remove the Zip Code and Sort field, but keep the Phone Number as you might need that for the 1099s. Then add the following: Vendor Type, Form 1099, Taxpayer ID/Social Security No, Box 1099 and Last Payment Date.

Accounts Payable > Main > Vendor Maintenance

Click Next. Now you’ll enter the Filters. We’ll choose Vendor Type Not Equal To N, which means we’ll get all of the Businesses and Individuals that are flagged for 1099s. We also want only Active Vendors, so we’ll add the Status to be Equal to “Active”. This is what your screen will look like: Accounts Payable > Main > Vendor Maintenance

Click Next. Now we’re in the final step. We can name the Lookup and choose the Default Search. Here is what I recommend: Search by Name, ContainsAccounts Payable > Main > Vendor Maintenance

Click Finish and you’re ready to test it out. Your screen will return to the Default Lookup. Accounts Payable > Main > Vendor Maintenance

Click on the “View” Dropdown (that shows “Standard” above) and you’ll see a new 1099 Vendors option. Select that option, and VOILA! The 1099 vendors will be selected.

Now you’re ready to export to Excel. At the bottom of the screen, in the lower right hand corner, you’ll see an Excel icon which will automatically export to Excel:

Accounts Payable > Main > Vendor Maintenance

Here’s what it looks like in Excel with one easy place to see all of your 1099 Vendors, with the type of Vendor (Individual or Business), which 1099 Form the Vendor is set up with, and which Box is the Default 1099 Box.

Export to Excel from Sage 100

Exporting information from Sage 100 is easy. Hopefully this will help give you some ideas of where you might use the Export to Excel function, and as always, if you have questions, please don’t hesitate to give us a call!

Topics: Sage 100c, ERP, Sage 100, Sage Software, Excel, Sage 100Cloud

Don't Miss a Thing!

Subscribe to our blog

Subscribe to our blog

    Recent Posts