New · CMCG Online Academy — 10 free accounting lessons · 10 free lessonsTry it
Accounting

Review an accounting export in Excel: a practical workflow

Prepare an accounting export, reconcile its totals and investigate possible duplicates in Excel while keeping every finding connected to the original records.

CMCG editorial illustration of an accounting grid, highlighted review items and a completed control card
New · CMCG’s flagship program

Practise this topic in CMCG Online Academy

Short lessons and corrected exercises on Moroccan accounting. The introduction (10 lessons) is free: your first name and city are enough.

Start for free

Your supervisor sends you a Sage export and asks you to check the supplier accounts. You can sort the file and add formulas, but the first question is whether the spreadsheet faithfully represents the accounting records. A tidy worksheet can still contain incomplete selections, numbers imported as text or references that lost their leading zeros.

The useful skill is turning an export into an investigation that someone else can follow. This guide explains how to protect the original data, establish control totals and investigate a possible duplicate. The spreadsheet helps you find and explain a problem; the complete entries and supporting documents provide the evidence for resolving it.

1. Establish the scope before checking balances

Write down the entity, period, journals and accounts selected for export. Record the extraction date and whether opening balances, provisional entries and validated entries are included. Keep those settings with your working notes. Two exports from the same accounting file can legitimately disagree when their selections differ.

Determine what each row represents. Is it an individual posting, an invoice total or a summary by account? Total debits and credits should agree in a complete journal covering both sides of every entry. They need not agree in a supplier-only extract, because the corresponding expense, tax or bank lines may be excluded. Your scope determines which comparison makes sense.

2. Keep the original and create a working copy

Retain the received file unchanged and carry out your checks in a clearly named copy. Separate the source data, your analysis columns and the review summary. Add a note explaining where the export came from and which transformations you applied. A colleague should be able to rebuild your result from the original file.

If the export lacks a stable row identifier, add a source row number before sorting. Keep that identifier attached to the whole row. Do not remove inconvenient items or overwrite an amount with the value you expect. Store the data in the organisation's authorised working locations and use fictional records when practising outside the actual accounting file.

3. Check data types and preserve references

Treat account numbers, supplier codes and document references as text identifiers. A reference such as “00148” is not a quantity. If import changes it to “148”, displaying leading zeros later may fail to restore the original meaning. Check sample references against the source report before proceeding.

Confirm that debit and credit amounts are numeric. A value imported as text may be omitted from a sum, while a misread decimal or thousands separator may change its magnitude. Inspect several examples, including a negative amount and a value with decimals. Dates require the same care: establish the intended day-month order before converting an ambiguous value.

Where spaces or inconsistent letter case interfere with matching, create a separate comparison column and retain the original reference beside it. Avoid removing every punctuation mark indiscriminately. Two document numbers that look similar may be distinct within a supplier's numbering system, and the spreadsheet should preserve the evidence needed to tell them apart.

4. Establish totals you can reconcile

Record the row count and the separate debit and credit totals before and after preparing the data. If debits occupy column G and credits column H, =G2-H2 calculates a signed movement for one row. Under this convention, a positive result is a debit movement and a negative result is a credit movement.

Use SUM to total numeric amounts, checking that its range includes every transaction and excludes any subtotal rows already present in the export. Identify those report rows explicitly in your working process while retaining them in the original file. Otherwise, summing detail and subtotals together can double-count the same activity.

Compare your totals with the accounting report for the identical scope. An ordinary sum includes rows hidden by a filter, so distinguish whole-file totals from totals for the visible selection. For the meaning of movements and balances, revisit the guide to reading an accounting trial balance.

5. Investigate repeated references rather than deleting rows

Start by grouping records with the same supplier and invoice reference. Then compare the journal, account, amount, debit or credit side and entry identifier. Highlighting repeated values or counting matching records creates a review list. It does not establish that a transaction should be removed.

The same invoice reference can appear for unrelated suppliers. A single invoice can also create several legitimate posting lines, and its later payment is a separate transaction. Conversely, an invoice entered twice may have different entry identifiers. Compare the complete entries with the supporting invoice before deciding what happened. The guide to reviewing a supplier invoice before entry provides the document checks that support this investigation.

6. Worked example: a possible duplicate supplier invoice

The fictional extract below contains supplier account lines only. Amounts are teaching currency units, and each entry identifier represents a different accounting entry.

EntrySupplierDocumentDebitCredit
E101SUP-AINV-014802,400
E118SUP-AINV-014802,400
E125SUP-APAY-00622,4000
E130SUP-BINV-014802,400

Debits total 2,400 and credits total 7,200, producing a net credit movement of 4,800. Their inequality is not itself an error: this selection excludes the other sides of the entries. SUP-A contributes a net credit movement of 2,400, and SUP-B contributes another 2,400. These movements equal closing balances only if the opening balances are zero.

E101 and E118 deserve investigation because they repeat the supplier, reference and amount. E130 belongs to another supplier and cannot be treated as the same purchase on reference alone. Retrieve the complete entries, invoice and payment evidence. If they confirm that E118 duplicates E101 and that the payment settles that invoice, the repeated posting explains SUP-A's remaining credit position. Document the finding for an authorised correction in the accounting system. Deleting E118 from Excel would only hide it from your review.

7. Summarise by supplier and explain unusual positions

Build a pivot table or equivalent summary showing debit and credit movements for each supplier. Check its source range and refresh the summary after adding records. Its grand totals must reconcile with the detailed worksheet. To report closing balances, include opening balances using the same sign convention throughout.

Use unusual positions as questions. A supplier debit balance may relate to an advance, a credit note or an allocation that needs checking. An unexpected amount is a reason to inspect the ledger, rather than proof of an error. Once invoices, credit notes and payments are identified, the guide to customer and supplier account matching explains how to connect them.

8. Deliver findings that another person can act on

For each issue, record the source row, supplier, document reference, observation, evidence inspected and remaining question. Assign a follow-up owner and a simple status such as awaiting review, explained or corrected after approval. Write “two entries may relate to one invoice” while the evidence remains incomplete. Avoid presenting an untested assumption as a confirmed duplicate.

After an authorised correction in the accounting system, obtain another export using the same settings. Reconcile the revised totals and locate the affected entries. Keep the explanation connected to both the original finding and the new evidence. The review is complete when the records and supporting documents agree, with any unresolved questions still clearly assigned.

Checklist before handing over your workbook

  • The scope and treatment of opening balances are recorded.
  • The original export and source row identifiers are preserved.
  • References remain text and amounts are numeric.
  • Row counts and control totals reconcile with the source report.
  • Possible duplicates are distinguished from legitimate repeated lines.
  • The supplier summary includes the intended data range.
  • Evidence, approved corrections and outstanding questions are documented.

Continue with CMCG Online Academy

To reinforce the journal-to-ledger reasoning behind these spreadsheet checks, continue with CMCG Online Academy, CMCG's learning app for Moroccan accounting (CGNC and the Moroccan chart of accounts) through short interactive lessons. It includes exercises corrected instantly, mock exams and an accounting entry simulator covering the journal, general ledger and trial balance, along with a business-French course. The interface is available in French or Arabic. Start with the free first module, with no bank card needed.

Related training: practical accounting training in Tangier · online accounting course

Turn what you read into real practice.

Work on real accounting files with a certified accountant, in Tangier or online.

Message on WhatsApp
Message on WhatsAppOnline Academy