Created with Sketch.Learn about PPP and Loan Forgiveness, stay informed with the latest changes.
Created with Sketch.Questions about how to close your books for 2020? Visit our Year End Resources page.
Created with Sketch.New to QuickBooks or using a new product? Visit our Get Started resource page to help you get going.
Created with Sketch.Need to make changes to your account? Visit the Account Manager Portal.
cancel
Showing results for 
Search instead for 
Did you mean: 
Level 2

Trial Balance by Class

I am a QB desktop user and need to modify the trial balance report. I want to use the raw data contained in the trial balance report to be the underlying data in a financial model. I need the individual rows where the account and debit/credit information is contained to include at a minimum Class, but date would also be helpful.

 

What other reports have QB users found to be helpful for downloading historical financial data and loading it into a projection model?

 

One suggestion I found online suggested running the IS & BS detail reports and combining them to get a trial balance proxy with class. The issue i'm running into with this is the report formatting when I export to excel. There's too many rows and columns with subtotals that make rolling this into a pivot table impossible.

Solved
Best answer November 16, 2020

Best Answers
Level 2

Trial Balance by Class

You may be done with your project by now, but you can do this in seconds with a 3rd party app.

 

QuickBooks does not have a Trial Balance that separates out the expenses by class name.

The Trial Balance is not in one column where Debits are Positive and Credits are Negative.

The account numbers are embedded with the account name.

This makes data analysis clunky and time-consuming.

 

I am an accountant who wrote a utility called TBX Trial Balance Exporter back in 2004, which gives you these options. I don't see any sign that the T/B will be changed in Desktop.

 

Here is a link to the QuickBooks Desktop App page: Enjoy!

https://desktop.apps.com/apps/167772/tbx-trial-balance-exporter#!overview

View solution in original post

8 Comments
QuickBooks Team

Trial Balance by Class

Hi there, @jparker24.

 

Let me share some insights on how to get this kind of report in QuickBooks.

 

Instead of running the Trial Balance report, you can pull up the Journal reports showing the underlying Class and especially to show up the dates. This report also contains the debits and credits of the account.

 

Here's how:

  1. Go to the Reports menu and then select Accountant & Taxes, then Journal.
  2. Click the Customize Report button.
  3. On the Search Columns box, type Class and then put a checkmark.
  4. Click OK to save it.

 

Once done, you will now view everything else you need for the report.

 

I'll add this article as a reference on how to personalize a report in QBDT: Customize reports in QuickBooks Desktop. It includes links that cover basic information about reports like setting up and modify Scheduled reports.

 

In case you wanted to remove other columns for a cleaner view, you can follow the steps above and uncheck the column names.

 

Feel free to tag me (@Jovychris_A) if you need further assistance in pulling up QuickBooks reports. I'm always here to help. Stay safe and well!

Level 2

Trial Balance by Class

Thank you, this is very helpful. My only issue with this report now, is that when i go to export to excel, the line breaks inbetween transaction numbers make this again not usable in a pivot table. 

 

Based on my reading, it seems like the best way to get a proxy trial balance that includes class is to download the PL and BS detail reports and exclude net income. These also come with terrible formatting for a data dump to excel. 

 

Do you know of a way to change report formats in QB so excel downloads work cleanly?

QuickBooks Team

Trial Balance by Class

Allow me to provide additional information about customizing reports in QuickBooks Desktop (QBDT), jparker24.

 

QBDT allows you to format the data you want to see when you export a report to Excel. To do so, you'll need to use the Advanced Excel Options feature to control the look on your statement.

 

Let me show you how:

  1. Open the report you need.
  2. Click Excel on the toolbar, then choose Create New Worksheet or Update Existing Worksheet
  3. Select the Advanced button to open the  Advanced Excel Options window. 
  4. Check or uncheck the boxes under Excel Options to remove or add those features.
  5. Click OK, then Export.

I've added this page: Export reports as Excel workbooks for more details on how to format a statement before exporting it.

 

Stay in touch if you need additional information by commenting below. I'm always glad to help.

Level 2

Trial Balance by Class

Thank you for these replies. I'm getting much closer to what I need, but I'm still not quite there yet. My issue lies with these line breaks for subtotals. I only care about the individual entries. Manually deleting breaks in order to turn this into a pivot table isn't a long term solution. Thanks to all the other guidance I'm now able to manipulate the columns how I need, but I'm still hung up on these subtotals.pl detail snip it.PNG

QuickBooks Team

Trial Balance by Class

Thanks for getting back to us, @jparker24.

 

We're unable to remove the line breaks for the subtotals on the Journal report. Though, we can run the Transaction Detail by Account report. This way, the line breaks will no longer appear in Excel.

 

Here's how:

  1. From the Reports menu, select Accountant & Taxes.
  2. Choose Transaction Detail by Account.
  3. Click Customize Report.
  4. On the Search Columns box, search Class and then place a checkmark beside it.
  5. On the Search Columns box, search Account, then place a checkmark beside it.
  6. Type in Trans # in the Search Columns box and put a checkmark beside it.
  7. Once done, click on OK.

 

Then, we can now export the report to excel. I'll guide you how:

  1. Click the Excel drop-down menu.
  2. Choose Create New Worksheet.
  3. Select the Advanced button.
  4. Check or uncheck the boxes under Excel Options to remove or add those features.
  5. Click OK, then Export.

 

I've included a reference below:

 

Here's a guide in exporting your reports to excel from QuickBooks Desktop: Export reports as Excel workbooks in QuickBooks Desktop.

 

You may also want to consider memorizing reports. Doing so helps save the current customized settings for easy access in the future.

 

Stay in touch with us if you have other QuickBooks related concerns. We're always here to help you. 

Level 2

Trial Balance by Class

The excel output still has row totals by account. This will not load properly into a pivot table. I agree that the transaction detail by account is 99% of the way there. I just need a simple way to download the raw data without any row breaks for totals and subtotals. My columns include Trans #, Type, Date, Num, Name, Memo, Paid Through, Account, Class, Clr, Split, Debit, Credit, Balance and Account Type. 

Level 2

Trial Balance by Class

You may be done with your project by now, but you can do this in seconds with a 3rd party app.

 

QuickBooks does not have a Trial Balance that separates out the expenses by class name.

The Trial Balance is not in one column where Debits are Positive and Credits are Negative.

The account numbers are embedded with the account name.

This makes data analysis clunky and time-consuming.

 

I am an accountant who wrote a utility called TBX Trial Balance Exporter back in 2004, which gives you these options. I don't see any sign that the T/B will be changed in Desktop.

 

Here is a link to the QuickBooks Desktop App page: Enjoy!

https://desktop.apps.com/apps/167772/tbx-trial-balance-exporter#!overview

View solution in original post

Level 2

Trial Balance by Class

Thank you for this. I wound up just pressing ahead with the transaction detail report and manipulating the outputs to work for me. It's still shocking that QB wouldn't make a tool for this. Exporting the trial balance with class and date information is a common way to load historical financial information into projection models.

Need to get in touch?

Contact us