How to Create a Reconciliation Report in Tableau

Building a report to reconcile two different datasets is one of those fundamental data tasks that sounds simple but can quickly become a headache. You have sales data in your CRM and transaction data in your payment processor - they should match perfectly, but real-world data is never that clean. This guide will walk you through a clear, step-by-step process for creating a powerful reconciliation report in Tableau, helping you spot discrepancies and investigate mismatches with confidence.

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 Exactly is a Reconciliation Report?

A reconciliation report is a way to compare two or more sets of data to ensure they are in agreement. It’s all about spotting the differences. The goal is to identify records that are present in one data source but missing from another, or records that exist in both but have mismatched values (like a different transaction amount or date).

This is an incredibly common and critical task across many business functions:

• Finance: Comparing bank statements against accounting software (e.g., QuickBooks).
• Sales &amp, Marketing: Reconciling leads generated in a marketing automation platform (like HubSpot) with leads that actually exist in the CRM (like Salesforce).
• E-commerce: Matching orders from your storefront (e.g., Shopify) with shipment data from your fulfillment center.
• Operations: Comparing inventory logs from a warehouse management system against actual sales data to identify shrinkage or data entry errors.

Without this process, you’re flying blind. Small discrepancies can compound into significant financial errors, skewed performance metrics, and poor business decisions.

Before You Open Tableau: Preparing Your Data

Jumping straight into Tableau without properly preparing your data is a recipe for frustration. A successful reconciliation report is built on a solid foundation. Spending a few minutes here will save you hours of troubleshooting later.

1. Identify a Unique Key

To compare records across two datasets, you need a common, unique identifier - a “key” - that exists in both. This is the single most important element of your report. This key is how Tableau knows which row in Source A corresponds to which row in Source B.

Good examples of unique keys include:

• Transaction ID or Order Number
• Invoice Number
• Customer Email or Customer ID
• Shipment Tracking Number

Make sure this key is truly unique in each dataset and that the format is identical. An "Order ID" labeled as 1001 in one sheet and #1001 in another won't match up.

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

2. Clean and Standardize Your Data

Inconsistencies are the enemy of reconciliation. Look for common data quality issues that can prevent records from matching correctly:

• Leading/Trailing Spaces: A simple space character at the beginning or end of an ID (e.g., " 1001" vs. "1001") will cause a mismatch. Use the TRIM function in Excel or Google Sheets to remove them.
• Casing: Ensure text fields have consistent casing. "John.doe@email.com" and "john.doe@email.com" are different values to a computer. Use LOWER or UPPER functions to standardize them.
• Date Formats: Check that dates are formatted identically. MM/DD/YYYY in one source and DD-MM-YY in another will cause joining issues.
• Field Names: For your sanity, name your key columns something similar in both sources (e.g., Order_ID in both or Transaction_ID and Shopify_Order_ID).

Step-by-Step: Building the Reconciliation Report in Tableau

With your data prepped, it's time to build the report. Follow these steps carefully to set up a clear and dynamic reconciliation dashboard.

Step 1: Connect to Your Data Sources

First, open Tableau and connect to your primary data source. This could be an Excel file, a Google Sheet, a database connection, etc.

1. On the start page, select the appropriate connector under Connect.
2. Navigate to and open your first data file (let's call it "Source A").
3. Once loaded, you will see your file in the data source pane. Tableau will automatically display its fields in the bottom section.
4. Now, to add your second data source, click the Add button next to Connections. Select the appropriate connector and open your second file ("Source B").

You should now see both data sources listed in the top-left pane.

Step 2: Use a Full Outer Join to Combine Your Data

This is the most critical technical step. How you join your data determines whether you'll find all your discrepancies. You need to combine the tables using your unique identifier.

Bring your "Source B" table to the canvas next to "Source A". Tableau will often try to create an inner join by default. You need to change this.

Click the join icon (the overlapping circles) and select "Full Outer" join.

Why a Full Outer Join? It's essential for reconciliation because it keeps all records from both tables, whether they have a match or not.

• An Inner Join would only show you records that already match in both sources - useless for finding what's missing.
• A Left Join would show all records from Source A and only the matching records from Source B. You would miss any records that exist in B but are missing from A.

The Full Outer Join ensures nothing gets left behind. Finally, in the "Join Clause" settings, select your unique key field from each table to tell Tableau how to match the records.

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

Step 3: Create Calculated Fields to Flag Discrepancies

Now that your data is joined, you’ll have a single table with columns from both sources. When a record from one source doesn’t have a match in the other, its corresponding fields will be NULL.

We’ll use calculated fields to make sense of these nulls and measure any variance. Go to any worksheet and navigate to Analysis > Create Calculated Field.

Calculated Field 1: Reconciliation Status

This field will categorize each record as either "Matched," "Missing in Source A," or "Missing in Source B." This adds clarity and allows for easy filtering.

Field Name: Reconciliation Status Formula:

IF ISNULL([Your Unique Key from Source A]) THEN "Missing in Source A" ELSEIF ISNULL([Your Unique Key from Source B]) THEN "Missing in Source B" ELSE "Matched" END

This formula checks if the unique key field from either source is blank (null). If the key from A is null, it means the record only existed in B. If the key from B is null, it only existed in A. If neither is null, they matched!

Calculated Field 2: Value Variance

This field calculates the difference between a key value you’re comparing, like a transaction amount or quantity. It instantly shows if matched records have different values.

Field Name: Amount Variance Formula:

ZN([Amount from Source A]) - ZN([Amount from Source B])

Quick Tip: The ZN() function stands for "Zero Null." It treats any null values as 0. This is crucial because performing math on a null value results in another null. Using ZN ensures your calculations work correctly even for unmatched records.

Step 4: Build the Reconciliation Worksheet

With your data structure and calculations ready, building the view is straightforward.

1. Drag your primary unique key and any other important dimensions (like Date, Customer Name, etc.) onto the Rows shelf.
2. Drag your key measure fields (e.g., Amount from Source A and Amount from Source B) onto the Text mark in the Marks card to create a table.
3. Now, drag your calculated field Amount Variance onto the Text mark as well. You should now see the difference side-by-side.
4. Drag your Reconciliation Status field to the Color mark on the Marks card. Tableau will assign a different color to each status, making it incredibly easy to see matched vs. unmatched records at a glance.
5. Finally, drag Reconciliation Status again onto the Filters shelf. This allows you to easily toggle your view to show only "Matched" records, only "Missing" records, or everything at once.

Visualizing and Analyzing the Discrepancies

You now have a functional report, but you can make it even more valuable by turning it into an interactive dashboard.

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

Create Summary KPI Cards

On a new dashboard, create a few summary worksheets to act as KPIs. You can create simple worksheets that only show a single number:

• Total Records Matched
• Total Records Missing
• Total Variance Amount ($)

Arrange these along the top of your dashboard for a high-level overview of the reconciliation health.

Use a Crosstab for Detailed Analysis

Place the detailed worksheet you built in Step 4 on the main part of the dashboard. This crosstab serves as your investigative tool. When you see a high variance amount in your KPIs, you can look to this table to find the exact transactions causing the issue.

Add Interactive Filters

Add filters to your dashboard for fields like Date or Product Category. This empowers users to drill down and investigate discrepancies within specific timeframes or business segments without needing to change the underlying report.

Final Thoughts

Building a reconciliation report in Tableau boils down to a few key principles: preparing clean data with a unique identifier, using a full outer join to capture all records, and leveraging calculated fields to flag and measure differences. Mastering this process moves you from simply looking at data to actively validating its accuracy, a critical skill for any data-driven role.

While powerful, building and maintaining these reports in tools like Tableau still involves manual setup and a considerable learning curve. We built Graphed because we believe getting answers from your data shouldn't be that complex. Instead of manually joining files and writing formulas, you can connect your sources like Salesforce, Shopify, and Google Analytics in a few clicks. Then, you can simply ask in plain English: "Compare my Shopify sales to my Stripe payments from last month and show me any discrepancies." Our AI data analyst builds the report in real-time, saving you the hours spent on tedious setup so you can focus on the insights.