Check journal entries balance per journal before a QuickBooks™ import, in Excel™
A journal file can hold many journals, each made of several lines. Each journal has to balance. If one does not, the import may fail, or it may import something you did not intend. Checking per journal catches it before upload.
What to test
- Balance per journal. For every journal number, debits equal credits.
- Blank cells where a value is required.
- A name on Creditors and Debtors lines. A line to a creditors or debtors account may need a name. Check this against Intuit's current import guidance.
Test by hand
These steps apply to Excel™ in Microsoft 365. Work on a copy. Assume Journal No. is in column A, Debit in D and Credit in E.
- Add a column:
=SUMIFS(D:D,A:A,A2)-SUMIFS(E:E,A:A,A2). This gives the net for the journal on each row. - Filter that column for anything other than zero. Those journals do not balance. Allow for rounding with
=ROUND(...,2). - Check blank cells with Home → Find & Select → Go To Special → Blanks over the data range.
- Add a column that flags missing names:
=IF(AND(OR(B2="Creditors",B2="Debtors"),C2=""),"missing name",""), with the account in B and the name in C. Adapt it to your own account names. - Fix each flagged journal and re-run the checks.
A journal that is out by a penny is still out. Do not hide it with a looser tolerance.
Run the same checks in the pane
Import Cleaner for QuickBooks™ checks journals for balance per journal, blank cells and a Name missing on Creditors or Debtors lines (QI-09). The scan and explanations are free; applying fixes is the paid part. It does not connect to QuickBooks™, and an account that does not exist in your company cannot be seen from a sheet.
See Import Cleaner for QuickBooks™ for Excel™
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. QuickBooks is a trademark of Intuit Inc. Import Cleaner for QuickBooks is an independent product, not affiliated with or endorsed by Microsoft or Intuit Inc.