Tags:

Use Advanced Options to Export QuickBooks Reports to Excel

Mar 17th 2015
Share this content
Advanced Options for exporting QuickBooks reports
iStock/Grzegorz Petrykowski
Advanced Options for exporting QuickBooks reports

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 2


Figure 1. The Send Report to Excel window appears. Select Advanced.

Figure 3

Figure 2. The Advanced Excel Options window appears.

Here are the seven key options—as shown above—and how you can use them:        

  1. 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.
  2. AutoFit. This will allow for the column widths in Excel to display all data.
  3. Freeze panes. This keeps headers and labels visible in Excel. A great option for working with lengthy reports.
  4. 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.
  5. QuickBooks Export Guide. Unclick this box so that QuickBooks doesn’t add the extra worksheet in Excel with export hints.
  6. 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.
  7. 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.

Figure 4
Figure 3. Report without the advanced features changed.

In contrast, here is a report exported from QuickBooks to Excel after making a few changes to the advanced export features.

Figure 5
Figure 4. Report with advanced export features customized.

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.

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By suzeli
Feb 17th 2017 15:29

I am trying to create a report that lists all the donors, how much they have given so far, name, address. It involves multiple accounts. When I export the info, each name has multiple lines and are not aligned. Do I have to manually fix this in excel? or is there a way to create a straight forward report? Here is a sample of what the report looks like:
Capital Donations 02/17/2017
January 1, 2015 through February 15, 2017 Cash Basis
Type Date Account Debit Credit Original Amount Balance
XXXXXXX, Vincent and Barbara
Deposit 07/10/2015 I-04.1b · Patron Letter 10.00 10.00 10.00
Total XXXXXXX, Vincent and Barbara 0.00 10.00 10.00
****** Kenneth
Deposit 11/18/2016 I-04.37 · General Donations 10.00 10.00 10.00
Total ****** Kenneth 0.00 10.00 10.00
******, Kenneth
Deposit 08/12/2016 I-04.1b · Patron Letter 25.00 25.00 25.00
Total ******, Kenneth 0.00 25.00 25.00
#####, Douglas
Deposit 03/25/2016 I-04.37 · General Donations 97.50 97.50 97.50

I would like it to be all in the same line, so later on I can create labels with the name and address columns.
Please help!

Thanks (0)