QuickBooks reports can be a useful way to get a better look at your data. But as you may have seen, there are times when you need to take the data we get from QuickBooks and expand on it.
Why Would I Export Reports from QuickBooks Desktop to Excel?
Some Reasons to Export to Excel
Maybe you need to remove some extra data you couldn’t filter in the QuickBooks report. Maybe you need to reorganize the data or perform some calculations on it. Maybe you use another tool that allows you to import data that you already have in QuickBooks.
One way we can do these things is by exporting the report to Excel. By exporting the report we make in QuickBooks over to Excel, we have the freedom to make such changes or any other changes we need – using the tools we’re already familiar within Excel.
How Do I Export a Report to Excel from QuickBooks?
How to Export – Step by Step
Within each report in QuickBooks, there is a toolbar along the top of the report window. Amid available report options – customization, memorization, or collapsing for example – is a button labeled “Excel.” This button opens a drop-down menu with two options:
1. “Create New Worksheet”
2. “Update Existing Worksheet.”
“Create New Worksheet” will give us further options for exporting this report to a new Excel sheet. “Update Existing Worksheet” can sometimes be used to try and replace a previously-exported worksheet with updated report information, without starting over on your Excel modifications and formulas, although it is not available for all types of reports.
When we select “Create New Worksheet,” the “Send Report to Excel” window will come up, giving us several options for export. We can choose one of two options
1. Create our worksheet in a new workbook
2. Creates a new worksheet in an existing one.
File Extension or File Type Required in Order to Export a Report to Excel
If your workbook is in a format other than the standard .xls or .xlsx, maybe a macro-enabled .xlsm for example, then QuickBooks will not be able to add the worksheet to that workbook. You would need to export to a .xls or .xlsx file, then copy it over.
The option,” update an existing worksheet,” is included in this window also, along with an option to replace the worksheet, rather than update it. The bottom option will allow you to export to a .CSV file, a simpler type of spreadsheet file that Excel and similar software can also read. Unlike the other options here, the CSV option is available even if the computer you are working on does not have Excel installed.
Advanced Options for Exporting to Excel from QuickBooks Desktop
The “Advanced” button provides several options for how certain parts of the export should work. You can choose whether to transfer over the following from the QuickBooks report to the Excel sheet:
– Fonts
– Colors
– Column spacing
– Row heights
You can also choose from various Excel options to apply to the imported data, such as Auto-fitting the column widths to show all data, or freezing panes to keep header data visible anywhere in the report.
The bottom section provides some printing options, such as where to show the report header and whether to repeat row labels on each page of the printed report.
Once you’re satisfied with your options, you can close them by hitting “OK,” and send the report to Excel by hitting “Export.”
If the report is particularly large, it may take a few minutes to export. You may see a progress bar as it exports; if the report is very large, it may even come up with a pop-up warning that lets you know it might take a bit.
If you like, this is one of those pop-ups you can keep from coming back in the future by ticking the checkbox “Do not display this message in the future.”
If you realize you’ve forgotten to filter your report or need to make other changes you may have forgotten before beginning the export, you can hit the “Esc” key to try and cancel this process, rather than wait for it to finish and re-export.
With a very large report, however, it may still take a moment after cancelling the export before you can interact with QuickBooks again.
What the Exported Report Looks Like in Microsoft Excel
Once the data has finished exporting, that’s usually it for the QuickBooks side of this. If you’ve enabled a lot of customization options to your report before export – and may use it again – you might want to memorize it. Otherwise, you’re free to start making any changes or adding any formulas you might need to the data in Excel.
Whas Next after Exporting and What Other Options are There for QuickBooks Reporting?
While there are other ways to expand on the basic QuickBooks reporting tools, such as QuickBooks Advanced Reporting or several third-party reporting tools, exporting report data to Excel, (a software you likely already have) may help you find very useful data in a flexible format you or someone in your business may already be familiar with.
By combining these two tools you’re probably using already, you get to add together the strengths and knowledge you have in both, which can go a long way towards empowering your business success.
To get even more out of this powerful process, dot forget to utilize our two-part series on Custom QuickBooks Reporting. Part 1 can be accessed, here; Part 2 can be accessed, here.
If you do run into additional questions or issues with exporting reports to Excel, call us!
