Use Advanced Options to Export QuickBooks Reports to Excel
If you are like most accountants, you often need to export a report from QuickBooks to Excel to do further analysis. The problem is that we continually make the same changes to exported worksheets in Excel. We delete the QuickBooks Export Guide, we delete extra columns, we delete the header, and we add row labels. Sound familiar? Is there a workaround?
Consider the Advanced Excel Options in QuickBooks. In this article I’ll show you how to save time from tedious and repetitive tasks with just a few simple clicks. (These instructions are for QuickBooks Desktop versions—Enterprise, Premier, and Pro.)
Start by running a report in QuickBooks that you want to export to Excel. Click Excel and select Create New Worksheet.
Figure 1. The Send Report to Excel window appears. Select Advanced.
Figure 2. The Advanced Excel Options window appears.
Here are the seven key options—as shown above—and how you can use them:
- Space between columns. How many times have you had to delete the extra blank columns in Excel? Just unclick the box next to Space between columns and those annoying columns won’t show up in your Excel spreadsheet.
- AutoFit. This will allow for the column widths in Excel to display all data.
- Freeze panes. This keeps headers and labels visible in Excel. A great option for working with lengthy reports.
- Auto Outline and Auto Filtering. This is especially handy if you want to analyze and sort data in QuickBooks. Auto Outline enables you to collapse and expand section of the report within Excel. Auto Filtering enables you to filter data within Excel by choosing from a drop-down list at the top of each column.
- QuickBooks Export Guide. Unclick this box so that QuickBooks doesn’t add the extra worksheet in Excel with export hints.
- Show report header option. I’m so glad this feature is available. In the past, the report header was not visible in Excel. If you are like me, you probably exported a report and spent a few hours editing it. Then you emailed your boss the report only to realize that you forgot to change the header. Your boss is confused and you’re embarrassed. Click on the option to show the report header on printed report and screen and you’ll never have that problem again.
- Repeat row labels on each page. This will automatically set up the row labels on each page when printing from Excel. It’s helpful when you have a report that is more than one page long.
Click OK to confirm your selections.
Here is an example of a report you would normally export out of QuickBooks if the advanced export features are not changed.
In contrast, here is a report exported from QuickBooks to Excel after making a few changes to the advanced export features.
The best thing is that QuickBooks remembers the Advanced Export options you have set up so that you don’t have to keep making the same changes over and over.
Next time you export from QuickBooks to Excel, remember to use the Advanced Export options. You’ll see how easy it is and you’ll be amazed at the time that you save.
About the author:
Veronica Wasek is an Advanced Certified QuickBooks ProAdvisor, a CPA, a public speaker, and a member of the national Intuit Trainer/Writer Network. Veronica is the founder of VM Wasek CPA, LLC, a QuickBooks-centric firm specializing in customized QuickBooks set up, training, clean up, consulting, and bookkeeping. Veronica’s blog, http://5MinuteBookkeeping.com, provides tips, videos, and tutorials for small businesses to get their bookkeeping done in as little as five minutes a day using QuickBooks Online.