Was this page helpful?
Thank you!

Comments or suggestions?

Enter Email Address (optional)

Excel formula does not work in a report exported from QuickBooks

This occurs when working with a QuickBooks List report that contains user defined fields as a row in Microsoft(R) Excel. Defined fields don't respond to Excel formulas in exported list reports.

This can also occur with exported Time reports or reports where billable time are included.  A time in QuickBooks  reports may be displayed as a whole number, in Excel it will appear as ### : ## .

How to fix it

Intuit recommends 3 solutions for this problem. The first solution may solve your problem, or you may need to try all 3 to resolve the issue. For best results, perform the solutions in the order shown.

Solution 1: Export the report as a comma separated values (.csv) file:

  1. With your report open, go to the Excel menu and select Create New Worksheet.
  2. Select A comma separated values (.csv) file radio button and then click Export.
  3. When prompted, name and save the file.

Note: If it is necessary to have the file in .xls format, you can go to Save as in Excel and change the format.

Solution 2: Copy the worksheet to a new worksheet:

  1. Copy the entire worksheet.
  2. Open another sheet on the workbook.
  3. Select Edit and Paste Special and then select Values.
  4. Create a new formula where needed on the worksheet

The formula should work on the new page. Although it may look like a problem in Excel, there is no clear definition why the formulas don't work within our imported regions. If you create a SUM formula outside the marked page breaks within the original worksheet, the formulas will work.

If these solutions don't resolve the issue, you can read discussions and post messages and questions relating to your issue on the Intuit QuickBooks Community site for free. You can contact an agent for additional guidance. Fees may apply.

KB ID# SLN40315
8/31/2015 4:48:51 PM
PPRDQSSWS406 9125 Pro 2015 d20936