How to Create a Reconciliation Report in Google Sheets with ChatGPT

Manually matching transactions between two spreadsheets is one of the most tedious tasks in data analysis, and it's incredibly easy to make a mistake. Thankfully, you can now combine the power of Google Sheets with the intelligence of ChatGPT to build accurate reconciliation reports in minutes, not hours. This guide will walk you through exactly how to set up your data and use ChatGPT as your personal formula-writing assistant.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Reconciliation Report?

At its heart, a reconciliation report is a simple comparison between two sets of data to spot any differences. Think of it as playing a game of "spot the difference" with your financial or operational records. The goal is to make sure the numbers in one system match the numbers in another and to investigate any discrepancies you find.

Here are a few common examples:

• Bank Reconciliation: The classic example. Comparing the transactions in your company's accounting software (like QuickBooks) against your official bank statement to ensure every penny is accounted for.
• Sales Reconciliation: Matching paid orders recorded in your e-commerce platform (like Shopify) against the actual deposits from your payment processor (like Stripe or PayPal). This helps catch failed payments or processing fee errors.
• CRM vs. Actuals: Comparing the "Closed-Won" renewals in Salesforce for Q3 with the actual renewal payments logged in your billing system. This verifies sales team performance and revenue recognition.

Doing this regularly is critical. It helps maintain accurate financial records, catch potential fraud or errors early, and gives you a true picture of your company's health.

The Old Way vs. The New Way in Google Sheets

For years, Google Sheets has been the go-to tool for scrappy teams to build reconciliation reports. It's free, collaborative, and most people are already familiar with its interface. The traditional method involves a combination of manual lookups and complex formulas like:

• `VLOOKUP()` or `XLOOKUP()`: To search for a specific transaction ID from one sheet in a list on another sheet.
• `COUNTIF()` or `COUNTIFS()`: To check if a specific item (like a customer email or order number) exists in another list.
• Conditional Formatting: To highlight duplicate values or rows that don’t have a match.
• Pivot Tables: To summarize large datasets and compare totals between your two sources.

While powerful, this manual process has serious drawbacks. You have to remember the exact syntax for each formula, it gets slow and unwieldy with thousands of rows, and one tiny mistake can throw off your entire report. This is where ChatGPT changes the game. Instead of you needing to be a formula expert, you simply describe what you want to do in plain English, and ChatGPT writes the precise formula for you.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Get Your Data Ready for Reconciliation

The success of any reconciliation depends entirely on having clean, well-structured data. AI can't help you if your source data is a mess. Here’s how to set things up properly in Google Sheets.

Gather Your Two Data Sources

First, you need to export the two datasets you want to compare. This usually involves downloading a CSV file from each platform.

For our example, let's say we want to reconcile our Shopify orders with our Stripe payouts. We would:

• Export all orders for last month from our Shopify Admin.
• Export all payouts for last month from our Stripe Dashboard.

Set Up Your Google Sheet

Create a new Google Sheet. You'll want to put each data source on its own tab to keep things organized.

• Create two tabs at the bottom. Name one "Shopify Orders" and the other "Stripe Payouts."
• Copy and paste or import the CSV data into the corresponding tab.

Your sheet should look something like this, with a dedicated tab for each dataset:

Find Your Unique Identifier

This is the most important step. You need a piece of data that exists and is identical in both datasets. This is the "key" that will link a transaction in one list to its counterpart in the other.

This could be an:

• Order ID
• Transaction ID
• Invoice Number
• Customer Email Address

Look through the columns in both your "Shopify Orders" tab and your "Stripe Payouts" tab to find that common column. Often, payment gateways like Stripe will include the platform's order ID in their export data, making it the perfect key.

Clean and Standardize The Data

Even a small formatting difference can prevent a match. For example, "Order #1001" will not match with "1001". Go through these checks on both sheets:

• Trim Whitespace: Use the `=TRIM()` function to remove any accidental leading or trailing spaces from your ID column.
• Format Dates: Make sure any date columns are formatted consistently (e.g., MM/DD/YYYY). Select the column and go to Format > Number > Date.
• Format Numbers: Ensure currency columns are formatted as numbers, not text, so you can perform calculations later. Select the column and go to Format > Number > Currency.

Step 2: Use ChatGPT to Write Your Reconciliation Formulas

Now for the fun part. With your data cleaned and organized, you can use ChatGPT as an on-demand analyst to generate the exact formulas you need.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Example 1: Finding Missing Transactions

Our first goal is to see which Shopify orders are missing from our Stripe Payouts report. This can help identify payment failures.

Craft Your Prompt for ChatGPT:

Open ChatGPT and give it a clear, specific request. Be sure to mention your tab names and column letters.

I have a Google Sheet with two tabs: 'Shopify Orders' and 'Stripe Payouts'.

In the 'Shopify Orders' tab, I want to add a new column called 'Stripe Status'. This new column should check if the 'Order ID' from Column A in the 'Shopify Orders' tab exists anywhere in Column C of the 'Stripe Payouts' tab.

If it finds a match, the formula should return 'Matched'. If it does not find a match, it should return 'Missing in Stripe'.

Get and Apply Your Formula:

ChatGPT will likely return a formula like this one:

=IF(ISNA(VLOOKUP(A2, 'Stripe Payouts'!C:C, 1, FALSE)), "Missing in Stripe", "Matched")

Simply copy the formula. Go to your "Shopify Orders" tab, click on the first empty cell in your new "Stripe Status" column (e.g., cell E2), and paste the formula. Then, click the small blue square on the bottom-right corner of the cell and drag it down to apply the formula to all your rows.

You can now use a filter to quickly see all the rows flagged as "Missing in Stripe."

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Example 2: Finding Mismatched Amounts

Now let's identify orders where the amount recorded in Shopify doesn't match the amount paid out by Stripe. This can happen due to refunds, discounts, or processing errors.

Craft Your Prompt for ChatGPT:

Let's build on our previous prompt. We'll ask it to not just find the match but compare two values.

I have a Google Sheet to reconcile my 'Shopify Orders' (Tab 1) and 'Stripe Payouts' (Tab 2).
- The 'Order ID' is in Column A of the Shopify sheet.
- The 'Order Total' is in Column D of the Shopify sheet.
- The 'Order ID' is in Column C of the Stripe sheet.
- The 'Payout Amount' is in Column F of the Stripe sheet.

I need a formula for my 'Shopify Orders' sheet that does the following:
1. Look up the Order ID from cell A2 in the 'Stripe Payouts' sheet.
2. If it finds a match, compare the 'Order Total' (Shopify sheet, Column D) with the 'Payout Amount' (Stripe sheet, Column F).
3. If the amounts are a perfect match, return 'OK'.
4. If the amounts do NOT match, return the difference between the Shopify amount and the Stripe amount.

Get and Apply Your Formula:

ChatGPT might give you a slightly more advanced formula combining `IFERROR()`, `VLOOKUP()`, and basic subtraction:

=IFERROR(IF(VLOOKUP(A2, 'Stripe Payouts'!C:F, 4, FALSE) = D2, "OK", D2 - VLOOKUP(A2, 'Stripe Payouts'!C:F, 4, FALSE)), "Not Found")

Copy and paste this into a new "Amount Check" column in your Shopify sheet. Now, in seconds, you can see not just if an amount is mismatched, but by how much.

Best Practices for Prompting ChatGPT for a Better Workflow

• Be Specific: The more detail you provide about sheet names, column letters, and desired outcomes, the better the formula will be.
• Ask for an Explanation: A great follow-up prompt is: "Can you explain how this formula works step-by-step?" This is an amazing way to learn what the formula is actually doing.
• Work in Stages: For complex reconciliations, break the problem down. First, ask for a formula to find matches. Then, in a new prompt, ask for a formula to compare values for the matched rows.
• Always Sanity-Check: ChatGPT is an incredible assistant, but it's not perfect. After pasting a new formula, manually check a few rows (a positive match, a negative match, and a discrepancy) to confirm the logic is working as you expect.

Final Thoughts

Combining ChatGPT and Google Sheets transforms reconciliation from a dreaded, error-prone task into a quick and efficient process. By letting ChatGPT handle the heavy lifting of writing complex formulas, you can focus on the critical work of investigating the discrepancies that actually matter to your business.

While this workflow is a massive step up from manual lookups, you might find yourself still spending time downloading CSVs and updating spreadsheets. This is actually why we built Graphed. We connect directly to your tools like Shopify, Salesforce, Stripe, and Google Analytics, eliminating the need for manual exports entirely. Instead of writing formulas to match data, you can simply ask questions in plain English, like "Show me a list of all Shopify orders this month that don't have a matching successful payout in Stripe," and get an always-on, real-time report instantly.