How to Create a Reconciliation Report in Excel

Manually comparing two sets of financial or operational records to find discrepancies can be one of the most tedious tasks you face. Whether you're matching your bank statement to your accounting software or comparing CRM data to actual sales, this process, known as reconciliation, is critical for accuracy. This guide will walk you through exactly how to build a flexible and effective reconciliation report in Excel, step by step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is a Reconciliation Report?

A reconciliation report is simply a tool used to compare two sets of records to ensure they are in agreement. Think of it like balancing your checkbook, but often on a much larger scale. It's the process of identifying any differences between your records - like transactions that appear in one system but not another, or amounts that don't match up.

While often associated with accounting and finance (e.g., bank reconciliation, credit card statement reconciliation), the concept is broadly applicable. You might want to reconcile:

• Your ad platform's data (like clicks from Google Ads) with your website's analytics (sessions in Google Analytics).
• Sales transactions recorded in your Shopify store with payments processed through Stripe.
• Leads generated in your marketing automation tool (HubSpot) with converted customers in your CRM (Salesforce).

Failing to reconcile regularly can lead to incorrect financial reporting, missed revenue, unidentified fraud, or poor business decisions based on inaccurate data. Excel, with its powerful formulas and data tools, is the perfect starting point for building a reliable reconciliation process.

Before You Start: Preparing Your Data in Excel

The success of your reconciliation hinges entirely on the quality and structure of your data. Before you write a single formula, take a few minutes to get your data sources properly formatted. You’ll need two distinct datasets to compare.

Let's use a common example: reconciling sales from an e-commerce platform (Dataset A) with payment gateway transactions (Dataset B).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

1. Clean and Structure Your Data

First, put each dataset on a separate sheet in the same Excel file. For example, name one sheet "E-comm Sales" and the other "Stripe Payouts." Then, ensure your data is in a structured, tabular format with consistent headers. A good setup includes:

• A Unique Identifier: This is the most crucial part. You need a piece of information that is identical in both datasets to match transactions. This could be a Transaction ID, Order Number, or Invoice Number. Without a unique ID, your work becomes much harder.
• Transaction Date: Make sure dates are formatted as dates, not text. In Excel, select the column, go to Format Cells, and choose the 'Date' format. A common problem is one system using DD/MM/YYYY and another using MM/DD/YYYY. Standardize them.
• Amount: Format currency columns as 'Number' or 'Currency' to ensure accurate calculations. Text-formatted numbers won't work in formulas.
• Other relevant details: Columns like Customer Name or Description can be helpful for manually investigating mismatches.

2. Convert Your Data Ranges into Excel Tables

This is an incredibly easy step that saves a ton of headaches. Click anywhere inside one of your datasets and press Ctrl + T (or go to Insert > Table). Repeat for your second dataset. Excel tables offer huge advantages:

• Dynamic ranges: When you add new data, the table expands automatically, so your formulas will always include new entries.
• Easy formula writing: Instead of cell references like A2:A100, you get structured, readable references like SalesTable[Transaction_ID].
• Built-in formatting and filtering: It just makes your data easier to work with.

Method 1: Using XLOOKUP or VLOOKUP for Reconciliation

For most simple reconciliation tasks, a smart lookup formula is your best friend. While VLOOKUP is the classic choice, XLOOKUP is more modern, flexible, and harder to mess up. We'll focus on XLOOKUP, which is available in Excel for Microsoft 365, Excel 2021, and Excel for the web.

The goal is to check if each transaction from your first list exists in your second list.

Step 1: Set Up your Lookup Columns

Go to your first table (e.g., "E-comm Sales"). Add two new columns to the right: "Found in Stripe?" and "Amount Difference."

Step 2: Find Matching Transactions with XLOOKUP

In the first cell of the "Found in Stripe?" column, type the following formula. Let's assume your Transaction ID is in a column named "Transaction ID" and your Stripe data is in a table named "Stripe".

=XLOOKUP([@[Transaction ID]], Stripe[Transaction ID], Stripe[Transaction ID], "Not Found")

Let’s break that down:

• [@[Transaction ID]]: This is the unique identifier from the current row in your E-comm Sales table.
• Stripe[Transaction ID]: This is the column in your Stripe table where we're looking for that ID.
• ...Stripe[Transaction ID]: This is the column from the Stripe table whose value we want to return if a match is found. In this case, we just want to see the ID itself to confirm it's there.
• "Not Found": This is what the formula will return if it looks for the ID in the Stripe table and doesn't find it. This makes it super easy to spot mismatches.

Since you're using an Excel Table, the formula will automatically fill down for all rows.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Calculate Amount Differences

Next, let's see if the amounts match for transactions that were found. In the first cell of the "Amount Difference" column, use another XLOOKUP to pull the amount from the second table and subtract it from the first.

=IF([@[Found in Stripe?]]<>"Not Found", [@[Sales Amount]] - XLOOKUP([@[Transaction ID]], Stripe[Transaction ID], Stripe[Payout Amount]), "")

Let's analyze this formula:

• IF([@[Found in Stripe?]]<>"Not Found", ...): This first part checks to see if the transaction was actually found. We only want to calculate a difference if a match exists.
• [@[Sales Amount]] - ...: If the transaction was found, we take the sales amount from the current table...
• XLOOKUP(...]): And subtract the corresponding amount from the Stripe table. This XLOOKUP finds the matching ID and returns the value from the "Payout Amount" column.
• "": If the "Found in Stripe?" column says "Not Found," we just want to leave this cell blank.

A result of 0 in this column means the amounts match perfectly. Any other number indicates a discrepancy you need to investigate.

Step 4: Do the Reciprocal Check

You're not done yet! You've found items in Sheet 1 that are missing or mismatched in Sheet 2. But what about items in Sheet 2 that are missing from Sheet 1? Just repeat the process, but from the other direction. Add the same columns to your "Stripe" table and use similar XLOOKUP formulas referencing the "E-comm Sales" table.

Step 5: Filter and Analyze

Now, click the drop-down filter arrows in your headers. You can easily filter for:

• Rows where "Found in Stripe?" is "Not Found."
• Rows where "Amount Difference" is not 0.

This gives you a clear to-do list of discrepancies to investigate.

Method 2: A More Advanced Approach with Power Query

If you perform reconciliations regularly or with large datasets, using Excel's built-in Power Query tool is a game-changer. It automates the process and creates a reusable workflow.

Step 1: Load Data into Power Query

Go to the Data tab in Excel. With your cursor in your first Excel Table ("E-comm Sales"), click "From Table/Range." The Power Query Editor window will open. Once it's loaded, click "Close & Load To..." in the top-left, select "Only Create Connection," and click OK. Repeat this process for your second table ("Stripe"). You now have two connections in the "Queries & Connections" pane.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Merge the Two Queries

In the Data tab, go to Get Data > Combine Queries > Merge. A dialog box will appear.

1. Select your first table from the top dropdown (e.g., Ecomm_Sales).
2. Select your second table from the bottom dropdown (e.g., Stripe_Payouts).
3. Click on the unique ID column header in each table preview (e.g., "Transaction ID").
4. Crucially, for the Join Kind, select Full Outer (all rows from both). This type of join keeps all records from both tables, showing matches where they exist and nulls where they don't.
5. Click OK. A new Power Query Editor window opens with the merged data.

Step 3: Analyze the Merged Data

You’ll see a new column (likely named after your second table) with the word "Table" in each cell. Click the expand icon (two diverging arrows) in that column header. Uncheck "Use original column name as prefix" and click OK. All the columns from your second table will now appear next to the first.

You now have one complete table. Discrepancies are easy to spot:

• Items Missing from Stripe: Filter the Transaction ID column from the Stripe side to show only (null) values. These are sales that never showed up in your payment processor.
• Items Missing from E-commerce Data: Filter the Transaction ID column from the E-comm Sales side to show only (null) values. These are payouts that aren't tied to any recorded sale.
• Amount Mismatches: Add a new custom column by going to the "Add Column" tab > "Custom Column". Use a simple formula like =[Sales Amount] - [Payout Amount] to create an instant "Difference" column, just like we did with formulas.

Step 4: Load the Final Report

Click "Close & Load" to drop a new sheet into your Excel workbook with the completed, unified reconciliation report. The best part? Any time you update your source tables with new data, you can simply right-click your final report and hit "Refresh." Power Query will re-run all the steps for you instantly.

Final Thoughts

Crafting a reconciliation report in Excel moves you from daunting, manual cross-checking to a structured, repeatable process. Whether you opt for the quick and straightforward approach with XLOOKUP or build an automated workflow in Power Query, taking the time to set it up properly ensures your data is accurate and reliable.

While Excel is fantastic, the process of manually downloading CSVs and updating your reconciliation sheets each time can still create friction. At Graphed, we help eliminate that last mile of manual effort. We make it easy to connect your data sources like Shopify, Stripe, and Google Analytics in seconds. Instead of wrestling with files and formulas, you can simply speak to Graphed in plain English and have real-time reconciliation dashboards and reports built automatically, keeping all your important information updated and in one place, freeing you to focus on the insights, not the setup.