1. Traditional, manual ways to build GL reconciliation

Below are practical, tried‑and‑true ways finance teams reconcile the general ledger using Excel and Google Sheets.

‍

Method 1: Simple one‑tab reconciliation template

1. Export your GL account data for the month from your accounting system to CSV.
2. Open Excel or Google Sheets and create columns like: Account, Date, Description, Reference, Debit, Credit, GL Balance.
3. In the same sheet, create a section for source data: Bank/Subledger Balance, Source Reference, Adjustments, Reconciling Items, Adjusted Balance.
4. Manually paste GL data into the GL area and bank/subledger data into the source area.
5. Use a formula to calculate variances, e.g. =GL_Closing_Balance - Adjusted_Source_Balance.
6. Highlight non‑zero variances with conditional formatting.
   • Google Sheets: see Conditional formatting guide – https://support.google.com/docs/answer/78413
   • Excel: use Conditional Formatting – https://support.microsoft.com/en-us/office/use-conditional-formatting-cbfd4d46-4a8f-4a39-a68b-79a504d7a2f5

‍

Method 2: Account‑per‑tab reconciliation workbook

1. In Excel/Sheets, create one worksheet per GL account (e.g., Cash, AR, AP).
2. On each tab, mirror the same layout: Opening Balance, Period Activity (detail lines), Reconciling Items, Closing Balance.
3. In a Summary tab, list all accounts with links to each tab’s variance cell (e.g., ='Cash'!F40).
4. Use a total variance cell that sums all accounts, so you can see if the entire balance sheet is reconciled.
5. This approach is great for auditors: every account has its own clear narrative.

‍

Method 3: Import and reconcile via CSV exports

1. Export GL detail and bank/subledger data as CSVs.
2. In Google Sheets, import each CSV into its own tab via File → Import (docs: https://support.google.com/docs/answer/3093335).
3. In Excel, use Data → From Text/CSV to bring in files (help: https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba).
4. Create a Reconciliation tab that uses lookup formulas to match transactions by date, amount, or reference:
   • Sheets VLOOKUP: https://support.google.com/docs/answer/3093318
   • Excel VLOOKUP: https://support.microsoft.com/en-us/office/vlookup-function-adceda66-30de-4f26-923b-7257939faa65
5. Mark any GL lines that return #N/A as unmatched items to investigate.

‍

Method 4: Pivot‑table‑driven reconciliation

1. In Excel, turn GL detail into a Table (Ctrl+T) and insert a PivotTable (help: https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576).
2. Summarize by Account and Month to get total balances.
3. Do the same with bank/subledger data.
4. In a reconciliation sheet, reference pivot outputs and compute GL_Total - Source_Total per account.
5. In Google Sheets, use pivot tables similarly (guide: https://support.google.com/docs/answer/1272900).

‍

Method 5: Narrative‑first reconciliation

1. Add a free‑text section at the top of each account’s tab for: Purpose, Key Drivers of Balance, Major Reconciling Items.
2. Write a 3–5 line explanation after each close.
3. This turns your template into an audit‑ready story, not just numbers.

These manual methods are simple, transparent, and work well for small teams—but they are click‑heavy and error‑prone as volume grows.

‍

2. No‑code automation on top of your template

Once your basic Excel or Google Sheets format is in place, the next step is to reduce repetitive imports and checks with no‑code tools.

‍

Method 6: Automate data imports in Google Sheets

1. Use scheduled exports from your bank or accounting system to Google Drive.
2. In Google Sheets, use Extensions → Apps Script templates or simple add‑ons to auto‑import fresh CSVs into the same tabs.
3. Alternatively, use =IMPORTRANGE to pull GL data from a master workbook into your reconciliation file (docs: https://support.google.com/docs/answer/3093340).
4. Lock formula ranges and use protected ranges so only finance can edit the logic (guide: https://support.google.com/docs/answer/1218656).
5. Result: your CFO opens one Sheet and the latest GL and bank data are already in place, ready to review.

‍

Method 7: Use Excel Power Query for repeatable refresh

1. In Excel, go to Data → Get Data → From File → From Workbook/From Text/CSV.
2. Use Power Query to define how GL and bank files should be cleaned (remove extra columns, standardize headers, etc.).
3. Load the cleaned data into dedicated tables in your reconciliation workbook.
4. Each month, just replace the source files and hit Refresh All.
5. Documentation: https://support.microsoft.com/en-us/office/get-started-with-power-query-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a

‍

Method 8: Formula‑based exception reporting

1. Add a Status column for each transaction.
2. Use formulas like `=IF(ISNA(VLOOKUP(...)),

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

Block quote

Ordered list

1. Item 1
2. Item 2
3. Item 3

Unordered list

• Item A
• Item B
• Item C

Text link

Bold text

Emphasis

^Superscript

_Subscript