cancel
Showing results for 
Search instead for 
Did you mean: 
gelkins4
Level 2

Export from QB Online to Excel with formulas

When I generated reports in QB Desktop, the exports to Excel included formulas for the totals. Now, when I generate a P&L report in QB Online the export to Excel has static numbers for totals. Is there a report setting that I need to change and if so, where do I find it? Thank you, Geordie

Solved
Best answer January 19, 2019

Best Answers
Anonymous
Not applicable

Export from QB Online to Excel with formulas

Weird. Transaction detail reports I've created never do.  Must be report specific.

View solution in original post

Malcolm Ziman
Level 10

Export from QB Online to Excel with formulas

I did some testing in QBO exports to Excel:

With formulas:

P&L and B/Sh summary 

P&L summary grouped by month (rows and columns)

 

No formulas:

P&L Detail

Transaction List by  Customer

Transaction Detail by  Account

General Ledger

View solution in original post

38 Comments 38
JonpriL
Moderator

Export from QB Online to Excel with formulas

Hi there, @gelkins4!

 

You don't have to change any settings in your report nor in QuickBooks Online. Instead, you might want to update your MS Office, including your Excel.

 

If you get the same result, I recommend checking your MS Excel settings. You may check this MS Office article for the detailed discussion or seek some help from an Excel expert.

 

Let me know how it works. It'll be my pleasure to assist you if you need any other help. Have a great day!

Rustler
Level 15

Export from QB Online to Excel with formulas


@JonpriL wrote:

Hi there, @gelkins4!

 

You don't have to change any settings in your report nor in QuickBooks Online. Instead, you might want to update your MS Office, including your Excel.

 

If you get the same result, I recommend checking your MS Excel settings. You may check this MS Office article for the detailed discussion or seek some help from an Excel expert.

 

Let me know how it works. It'll be my pleasure to assist you if you need any other help. Have a great day!


the article you reference is HOW TO save an excel sheet in a different format

 

Has nothing to do with the question - delete that part of the reference book you copy from - it is wrong

of course if you had read it, you might know that

Anonymous
Not applicable

Export from QB Online to Excel with formulas

@JonpriL

RE: You don't have to change any settings in your report nor in QuickBooks Online. Instead, you might want to update your MS Office, including your Excel.

 

Excel cannot change what QB Online creates.  Since it creates the Excel file on the server, nothing on the local machine can impact what it does.

 ----edited----

The correct answer is that if QB Online does not send formulas to Excel for the report you're running, there isn't a way to change what it does.

Rustler
Level 15

Export from QB Online to Excel with formulas


@Anonymous wrote:

 

The correct answer is that QB Online does not send formulas to Excel, but instead a static report of numbers.  There isn't a way to change what it does. This is basic product knowledge.


I just checked mine again, it does export the formula's, at least on a P&L report I ran

 

I agree with updating and/or repairing excel though

Anonymous
Not applicable

Export from QB Online to Excel with formulas

Weird. Transaction detail reports I've created never do.  Must be report specific.

Malcolm Ziman
Level 10

Export from QB Online to Excel with formulas

I did some testing in QBO exports to Excel:

With formulas:

P&L and B/Sh summary 

P&L summary grouped by month (rows and columns)

 

No formulas:

P&L Detail

Transaction List by  Customer

Transaction Detail by  Account

General Ledger

gelkins4
Level 2

Export from QB Online to Excel with formulas


@Anonymous wrote:

Weird. Transaction detail reports I've created never do.  Must be report specific.



Thank you to all who have helped! Yes, it is weird and yes, it is report specific. I am not sure why unless it is a glitch or oversight. Seems odd. Thank you all again.

LawJ
Level 1

Export from QB Online to Excel with formulas

Correct, there are no formulas in the detail reports exported to Excel.  This to me is a set back for QB online.  

tdcpa
Level 1

Export from QB Online to Excel with formulas

I have the EXACT same problem!  It has nothing to do with Excel.  It's definitely a QB Online bug.  It does not happen with the QB Desktop.  Did anyone hear ever find a solution???

MirriamM
Moderator

Export from QB Online to Excel with formulas

Welcome to the thread, tdcpa. Thanks for posting in the Community forum. 

 

When exporting reports in QuickBooks Online (QBO), it will show the same information when you pull it up inside your QuickBooks account. It just differs on how it looks like the table. Therefore, having the formula when exporting is not an option. 

 

In the meantime, I encourage you to star our blog to keep up with any product updates or improvements on the online version: https://quickbooks.intuit.com/blog/whats-new/.

 

If there's anything else I can do for you, let me know by commenting below. I'm happy to help. Have a great day!

mhs3
Level 2

Export from QB Online to Excel with formulas

When I export it does not even show numbers as numbers - they are text. Therefore I cannot even add a column - is there something I can do to at least have numbers exported as numbers?

Kendra H
QuickBooks Team

Export from QB Online to Excel with formulas

Thanks for joining this thread, @mhs3

 

As my colleague @MirriamM stated, the formula for the numbers doesn't export when you export the report. I am going to submit feedback to our Product Development Team for them to review and consider making this a feature soon. 

 

For now, I recommend keeping an eye on our QuickBooks Blog for future updates. 

 

If you have any more questions or concerns, please don't hesitate to comment below. Have a good rest of your day!

 

 

mhs3
Level 2

Export from QB Online to Excel with formulas

Thanks for your reply. I understood that a FORMULA is not going to export, I do not consider a number a formula. I consider a number = number, not a number = text. This is a very basic concept and it should be considered high priority for QB online programmers. When you have to type over hundreds of numbers to be able to submit a report to a tax agency, that is pretty ridiculous.

Fiat Lux - ASIA
Level 15

Export from QB Online to Excel with formulas

@mhs3 

Utilize the trial period of 3rd party tool to export the data as numbers, not texts.

https:// transactionpro.grsm.io/qbo

 

LawJ
Level 1

Export from QB Online to Excel with formulas

There shouldn't be a need to use a third party for something that should be a basic feature for an accounting program.  thank you for your suggestion though

mhs3
Level 2

Export from QB Online to Excel with formulas

Is there a list of what is being worked on?

It is sooooo frustrating to have some reports export numbers as numbers and some reports export numbers as text. What is the point of exporting to excel if numbers export as text? 

Who decides programming priorities? 

This should be a very basic fix. If some reports handle it correctly, why not all?

WSP
Level 1

Export from QB Online to Excel with formulas

I am having this problem from Quickbooks Premier Edition Desktop 2015. These posts are all over the place, some with ... fix Excel ... some with ... it is Quickbooks.

 

Has anyone definitively solved this issue?

 

WSP
Level 1

Export from QB Online to Excel with formulas

I've used the export function for years, perhaps more than a decade, from multiple versions of desktop QB. In Jan-Feb of this year, the exports were coming out with the formulae .... for BalSheet and P&L by month reports ... and now for Mar and thereafter, coming out as numbers, losing the formulae.

 

These threads implying that it is a problem with QB not doing a formula export, or it is a problem with Excel, are missing something .... 

 

Frustrated

JonpriL
Moderator

Export from QB Online to Excel with formulas

I wish I could make it better, @WSP.

 

Let's update first all your software so the formulas will show up on your Excel file when exporting your Balance Sheet and Profit and Loss report.

But if you still receive the same results after doing so, I'd recommend contacting our Customer Care Support. They can pull up you're account, securely, and create a case or ticket ID to be submitted to our engineers for further investigation about this unexpected behavior.

  1. Go to Help.
  2. Select QuickBooks Desktop Help.
  3. Click Contact Us.
  4. Enter Excel Formulas in the Tell us more about your question: field.
  5. Select Search.
  6. Click Message an Agent.

In addition, Here's an article you can read to learn more about our support hours and types: Support hours and types.

 

Lastly, I've got you these helpful articles for ideas to help manage your account and related tasks: Help Articles for QuickBooks Desktop.

 

If there's anything else that I can help you with, let me know in the comments. I'll be happy to help.

kldjackson
Level 2

Export from QB Online to Excel with formulas

Did you ever figure out a fix for this?  Here is a clumsy work-around - https://support.microsoft.com/en-us/office/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4...

 

 

vcochran
Level 2

Export from QB Online to Excel with formulas

I'm still having issues with this when I export QBO reports to Excel. I can't put the data in a pivot table because the number formats are off. I tried various things to correct it but nothing is working. 

Any help?

Jen_D
Moderator

Export from QB Online to Excel with formulas

Thanks for joining this thread for your report exporting concern, @vcochran.

 

The data format of the reports we export to Excel are fixed, but you can customize it to your preference once exported.  

 

Please know that this forum can only handle general QuickBooks concerns. If you need help creating Excel reports with pivot tables, you'll need to reach out to Microsoft Support for assistance and tips. They are the best team to help you with this specific task.

 

I'll be attaching some of their support links fr additional reference:

 

 

 

Reach out back to me if you have other reporting concerns in QuickBooks Online. I'll be more than happy to help. Have a good one!

CLP417
Level 2

Export from QB Online to Excel with formulas

HI

On QBOnline - I can export PL and BS reports and use the auto outline function

I cannot use it on any of the transaction reports

If these are small reports then it is OK but not on a detail PL transaction report of 6000 rows

 

I have been using Desktop this week end for other companies and it has been a joy

The problem will become a deal breaker when the decision to stay with online or move to Sage is reviewed in the autumn. But for now I am stuck

I am on the UK version of QBO - and no one seems to have an idea why

Fiat Lux - ASIA
Level 15

Export from QB Online to Excel with formulas


@CLP417 wrote:

I am on the UK version of QBO - and no one seems to have an idea why


 

@CLP417 

Is your company located in UK?

Need to get in touch?

Contact us