RetrieverRetriever
ProductTemplatesSecurityPricingStart HereGuides
Try for free
Guides
/

Show, Sort & Filter QuickBooks Reports by Account Number, in Google Sheets

Show, Sort & Filter QuickBooks Reports by Account Number, in Google Sheets

The problem

When I pull a P&L or Balance sheet in modern reports everything is out of order as it does not show COA numbering. If I switch to classic reports the numbering shows up and the report is in numerical order.

In the modern reports, account numbers get buried inside the account label (or don't show at all), rows sort by amount instead of number, and there's no way to filter a Balance Sheet or P&L to a range of account numbers. Firms that key their templates and reporting entities off account numbers (1-XXX for one entity, 2-XXX for another) are stuck reformatting by hand.

Why QuickBooks can't do it

The modern reports treat the account number as part of a fixed parent:sub:sub label, don't offer a reliable numeric sort, and dropped the ability to filter by account number. There's no per-report setting to make the account number the primary key or to include only a range of numbers.

Do it in Google Sheets with Retriever

In Google Sheets, the account number is just data in a column, so you can display it, sort by it, and filter by it however you want.

  1. Pull the report into Google Sheets

    Bring your P&L, Balance Sheet, or Trial Balance in with Retriever, account numbers included.

  2. Show the account number the way you want

    Split the parent:sub:sub label so the account number is its own column, and show the number or the plain description, your choice, not a fixed format. On the Profit and Loss, Balance Sheet, Cash Flow, and Budget you can insert that column inside the report and it survives refreshes; on other reports put it on a separate tab.

  3. Put the rows in numerical order

    Select entire rows and drag them into account-number order. Retriever tracks the row, so the order holds through every refresh and you set it once. Drag whole rows on these statements rather than using the Sheets Sort feature, and never sort a cell range, which is the one movement Retriever does not follow.

  4. Filter by account-number range

    Filter to just 1-XXX (or any prefix) to isolate a reporting entity, then refresh, no more manual splitting.

See also

Filter a P&L or Balance Sheet by account numberFix account order on comparative reportsHow to build a Profit & Loss report by monthExport your Chart of Accounts from QuickBooks OnlineChanges to your chart of accounts: renaming and re-ordering rowsModern reports renamed your total rows and broke your lookupsSummary P&L templateMulti-Entity P&L templateChange the company name on a reportHow Midwest CPA saves 15 hours a month

Report on your account numbers, your way

Retriever pulls your QuickBooks reports into Google Sheets, where account numbers are just data you can show, sort, and filter, then refresh on a schedule.

See how Retriever works

Frequently asked questions

Can I sort a QuickBooks report by account number?

Not reliably in the modern reports. In Google Sheets you set the order once by dragging whole rows, and Retriever keeps it through every refresh. The one thing to avoid is sorting a cell range, because Retriever tracks rows and columns rather than individual cells. See how Retriever handles row re-ordering.

How do I split a Balance Sheet into reporting entities by account number?

Filter the pulled report by account-number prefix (for example 1-XXX vs. 2-XXX) in Google Sheets. Each filter is a reporting entity, and it refreshes on a schedule.

Will new accounts show up with their numbers?

Yes. Retriever pulls the current chart of accounts each refresh, so new accounts appear with their numbers automatically.

Questions about this? Reach out at aubrey@retrieverhq.com.