INTELLIGENT TECHNOLOGIES, INC’S NEWS & TIPS BLOG

Acumatica Pro Tip: How to Export Data to Excel

Laura-Square

Laura Schomaker, Senior Marketing Associate

March 28, 2019

Even with all the fancy data visualization tools on the market today, like Power BI, Tableau and many others, Excel remains one of the most popular reporting tools. There are many reasons this could be true. Personally, I think Excel’s popularity has a lot to do with the fact it’s fairly easy to use and something most accounting and financial pros are already comfortable with. They can get their reporting done without the hassle of learning a new system.

So, it’s no surprise that one of the most common requests we hear from our clients in accounting and finance roles is to learn how to export data from their accounting/ERP system into Excel for further analysis. While most systems allow this, some make it easier than others. Luckily for Acumatica users, this process couldn’t be simpler. Let me walk you through it with our interactive tutorial below.

2 Minute Tutorial: Export Data to Excel from Acumatica

5 STEPS


1 Navigate to the screen showing the data you'd like to export. In this example, we're going to export Journal Transactions.

Step 1 image

2 Use the drop down menus at the top of the table to filter out any data you're not interested in exporting.

Step 2 image

3 Click the button highlighted here. 

Step 3 image

4 If you're exporting more than 1,000 records, you may see this warning. If you'd like the system to truncate your Excel file click "Apply." Otherwise, to export all records shown in the table, click "Ignore."

Step 4 image

5 Wait a few seconds for your Excel file to download.

Step 5 image

Here's an interactive tutorial for the visual learners

https://www.iorad.com/player/1557263/How-to-Export-Acumatica-Data-to-Excel

Can All Acumatica Reports Be Exported to Excel?

Yes, you can export any Acumatica reports to Excel using the process above. Once you download one to a workbook, the real fun begins. You can now manipulate the raw data however you like in Excel.

 

When I Export Data to Excel from Acumatica Will it Update Automatically, or is it a Static Snapshot Taken at a Specific Point in Time?

The data you export from Acumatica to Excel can either be static or refreshable. You export static data using the steps we covered before. If you would like the option to update the data at a later time without re-downloading it, the steps you must follow are a little different.

First, find or set up a generic inquiry in Acumatica containing the data you want to work with. Not familiar with generic inquiries? Watch this video.

Once you have your generic inquiry set up, you will need to connect it to Excel. You can do this using an OData connection. If you have no idea what that is, it’s just a geeky term for the way web-based systems share data with one another. Setting it up is easy.

  1. Open a workbook in Excel. Go to “Data > Get external data from other sources > From OData feed.”
  2. A new window will open asking you for a URL.
  3. To find the URL you need, open Acumatica and navigate to the generic inquiry you would like to work with in Excel.
  4. Copy the URL you see in your browser’s address bar.
  5. Paste it into the window that opened in Excel a moment ago. Before proceeding type “/ODATA” at the end of the URL.
  6. Excel will ask you for your Acumatica credentials, enter them and click “Finish.” The default settings for where to import the data will work fine, click “OK” to accept them.

Now, you can slice and dice your data until your heart’s content. Anytime you want to update the information in your spreadsheet, all you have to do is click “Refresh All” on your Excel ribbon’s “Data” tab.

Is My Acumatica Data Safe When I Connect it to Excel with an OData Feed?

Absolutely! The data is encrypted as it moves from one web service to another. Additionally, a user must login with their Acumatica credentials before they can view it inside of Excel. And, any role-based permission or security settings which you put in place to protect that data still apply. If an Acumatica user could not view the data used in your report when they’re logged into Acumatica, they won’t be able to see it in your Excel report either.

Still have questions about working with your Acumatica data in Excel? Type the keyword “Excel” into the search bar at the top of any Acumatica screen. Then, click the “Help Articles” tab and you will find many more resources on this subject.

Need expert help? Contact us. Our reporting gurus will be more than happy to assist.

Other Posts You May Enjoy

Two Simple Ways Acumatica Users Can Share Their Feedback

Calling all Acumatica users, do you have a brilliant idea to share with the development team? Did you know, there’s a fast and easy way to do this? You can even vote for your favorites. Or, if that is not quite your speed, what about sharing your experiences with other potential users? You can even earn a $25 Amazon gift card.

Do You Do A Lot of Work in Excel? Paste Special Should Be Your Best Friend

Accounting and finance pros, who work in Excel, would you like to get more done in less time? Meet Excel’s little known Swiss Army knife, the paste special feature can speed calculations, formatting and other tedious tasks. Once you discover all you can do with this tool, you’ll wonder how you got along without it for so long.

Acumatica Pro Tip: Tame Document Overload with Acumatica File Management Tools

One of the most frustrating parts about collaborating on file is often keeping track of which version is most up to date. No one likes putting hard work into a file, only to find out their team members are already three steps ahead of them. See how Acumatica’s file management features put an end to these issues.

Acumatica Cloud ERP Data Security: Your Backup and Recovery Questions Answered

Thinking of moving to the cloud but worried about your data security? We understand. There’s a lot to think about when considering moving your business data off your own server and on to someone else’s. That’s why Acumatica offers a variety of backup and disaster recovery features to their cloud ERP customers. Learn more.

Acumatica Pro Tip: How to Create an Acumatica Favorites List

Tired of searching through endless menu options to find the windows you use most often? Set up your Acumatica favorites list to save yourself time and energy! Just like your favorite Internet browser, Acumatica offers a tool to help you store and easily access the windows that are most important to you. Learn how to set it up.