Using an Excel agent to clean, validate, and reconcile data

For organizations to process large volumes of data daily, professionals often spend substantial time cleaning and preparing data before they can perform analysis, testing, or reconciliation.

Multiple Excel-integrated artificial intelligence (AI) tools have recently become available to enhance the auditor’s capabilities. They include ChatGPT for Excel, Claude for Excel, and specialized audit-centric tools like DataSnipper. These tools promise to reduce the manual burden of repetitive data preparation while supporting a clearer and more reviewable audit trail.

While different Excel-integrated AI tools exist, they are rapidly converging in functionality and design. For audit engagements, they must be deployed using enterprise-level licenses. We built an agent in ChatGPT for Excel that we used as a representative example in our demonstration, and the skills learned are transferable to other, similar platforms.

For many firms and companies, an Excel agent can serve as a practical, low-resistance starting point for AI adoption. Because the work is performed inside the familiar Excel environment, the approach fits naturally into existing workflows without requiring specialized coding skills or expensive external analytics platforms. To illustrate this process, we used a simulated dataset adapted from a case study published in the Journal of Emerging Technologies in Accounting. In our simulation, the full process of cleaning, validating, and reconciling 59,157 rows of transaction-level general ledger data to the trial balance took about 10 minutes on average. This same task would take many hours to complete manually, using traditional methods.

Although the simulation in this article focuses on an audit setting, the underlying approach has much broader relevance. Similar techniques can be used whenever messy data must be prepared for analysis. Potential applications include preparing journal entry populations for testing, organizing accounts receivable or accounts payable exports, standardizing fixed-asset listings, cleansing tax data for compliance or provision work, preparing financial reporting schedules, and supporting reconciliations across client- or company-generated reports.

In short, the value of this tool extends beyond audit and can improve efficiency and effectiveness across a wide range of accounting-related functions.

THE SIMULATED CASE: PREPARING GENERAL LEDGER DATA FOR AUDIT USE

Note: A video tutorial demonstrating the full process is available at the end of this article. We recommend reading the written steps below first to understand the framework before watching the video.

Audit teams routinely receive raw data that is not immediately usable. Files may contain mixed text-and-number fields, inconsistent date formats, blank rows, irrelevant headers, or other structural issues that make analysis difficult. Before auditors can perform substantive procedures, risk assessment analytics, or reconciliations, the data must first be transformed into a complete, standardized, and auditable format. That preparation work is essential, but it is often repetitive, time-consuming, and vulnerable to manual error. The primary challenge lies not solely in data cleaning but in ensuring that the process is transparent, auditable, and appropriate for professional use.

To illustrate this process, we used a simulated dataset that contains 59,157 rows of transaction-level general ledger data in a highly unstructured format, including mixed text-and-number fields, inconsistent date formats, irrelevant headers, and blank rows. The task was to clean and standardize the dataset and then validate and reconcile the cleaned data to the client-provided trial balance for audit purposes. This involved verifying the data’s completeness and accuracy in accordance with auditing standards. The validation step is critical, because transformed data is useful only if auditors can establish that it appropriately represents the population intended for further analysis.

STEP 1: TRAIN THE AGENT USING “SKILLS”

Excel agents function as largely autonomous assistants that carry out a sequence of tasks under the auditor’s close supervision. To improve the quality and consistency of their outputs, users can provide Excel agents with “skills” that guide their behavior. A skill is a text file containing detailed instructions, scripts, and resources that give an Excel agent specialized expertise and procedural knowledge for specific tasks. In this sense, providing a skill is similar to giving a staff accountant a written procedure manual before assigning a task.

Accordingly, the first step is to equip the Excel agent with the necessary skills. ChatGPT for Excel includes three generic built-in skills related to corporate finance, financial planning, and analysis. However, because the current version does not include a dedicated data-cleaning skill designed for audit purposes, we developed a customized skill by integrating data-cleaning procedures with relevant audit standards. To do so, we first provided the Excel agent with data analytics teaching materials and audit standards, then prompted it to draft the text for the skill file. We subsequently reviewed and refined the generated file to ensure its quality.

Our data-cleaning skill instructed the Excel agent to follow these principles:

1. Preserve the original data: Never clean the source file directly; always work from a copy.
2. Inspect before acting: Review the Excel data for quality issues before making any changes.
3. Create a cleaning plan: Identify the issues found, explain the proposed cleaning steps, and ask the user to approve the plan.
4. Use only native Excel tools: Rely on Excel formulas, filters, tables, formatting, and PivotTables.
5. Document every change: Maintain a “Modifications_Log” as an audit trail.
6. Validate completeness and accuracy: Reconcile the cleaned data to control totals, such as the general ledger, trial balance, or subledger.
7. Avoid overstating compliance: The output may support audit evidence, but final audit judgment remains the responsibility of the user.

Developing a custom skill is a process of enhancing generic AI models with firm-specific expertise and the firm’s standard procedures. Any texts, such as the company’s policies and standardized audit procedures, can be transformed into a skill. Before sharing a custom skill with the wider engagement team, it should undergo a structured testing process:

• Control run: A technical leader runs the skill on a small control dataset with known data flaws and a pre–calculated clean output.
• Formula check: The tester verifies that the Excel formulas generated by the agent are accurate and trace correctly to source cells.
• Refinement: The instructions inside the skill are iteratively adjusted until the agent achieves 100% accuracy on the control dataset.

This skill-building also creates a continuous improvement loop: As staff auditors use the Excel agent on diverse client datasets, they note any anomalies or formatting limitations, reporting them to managers who then update and refine the skill guidelines to improve performance for future engagements. Partners and managers can enhance the overall audit quality by actively participating in this skill testing and validation process before distributing the approved skill files to the engagement team.

STEP 2: A STEP-BY-STEP PLAN BEFORE EXECUTION

The skill we built forces the Excel agent to present a plan and ask for formal approval before altering the data. This planning step requires the Excel agent to analyze the active dataset and document its proposed methodology in a separate worksheet before making any changes. This step serves as an essential engagement-level check, allowing the human auditor to verify that the agent’s planned formulas and rules are correct for the client’s file before execution. While this human-in-the-loop checkpoint reduces, but doesn’t eliminate, the risk of inappropriate or unauthorized data alterations, it also acts as a feedback mechanism: If any undesirable agent behavior is observed during the planning phase, it can be reported to the technical leader in charge of the skill and used as input to refine and improve the custom skill. (Download a PDF showing the full “Data Cleaning and Reconciliation Planning.“)

STEP 3: CLEAN AND STANDARDIZE THE GENERAL LEDGER DATA

Once the plan is approved, the AI can execute the data preparation steps inside Excel using standard, reviewable spreadsheet formulas. This is important for practitioner acceptance, because the work remains inspectable by the audit team.

In our demonstration, AI performed three core tasks:

• Row scrubbing: The Excel agent identified and removed blank rows and irrelevant headers, so the general ledger became a continuous block of usable data.
• Reconciliation–key construction: The Excel agent inspected the available columns and determined how to combine relevant fields, so the cleaned ledger could be matched to the identifier structure used in the control dataset.
• Date standardization: The agent standardized all date formats, correcting the raw data’s mix of text strings and inconsistent entries that would otherwise prevent Excel from sorting the data chronologically.

STEP 4: VALIDATE THE CLEANED DATA THROUGH RECONCILIATION

Cleaning data is only part of the task. Reconciliation is a critical follow-up procedure. Using the context developed in the earlier steps, the Excel agent can aggregate the cleaned general ledger by account number and compare those balances with the ending balances in the client’s trial balance. It can then highlight any non-zero differences for follow-up.

This step is valuable for two reasons. First, it helps verify completeness and accuracy. Second, it creates a structured bridge between data preparation and substantive audit use.

For example, the Excel agent can sum cleaned transaction amounts by account, pull trial balance amounts from a separate sheet, calculate the variance, and flag accounts where the difference is not zero. If the cleaned general ledger does not reconcile to the trial balance, the file may require additional investigation before it is used for further audit procedures.

STEP 5: DOCUMENT THE LOGIC FOR REVIEWER INSPECTION

Another practical benefit of this approach is that the Excel agent can generate plain-English documentation in a separate tab, explaining what it did and why. This automated summary serves as a draft that the auditor can review, customize, and approve, creating a robust, human-verified audit trail. (Download a PDF showing the “Completed Cleaning Checks and Modification Log.”)

This kind of documentation does not eliminate the need for professional judgment; rather, it enables effective human validation and quality control. In our simulation, human validation of the Excel agent’s work involves a three-step process:

1. Summary reconciliation review: The auditor verifies that all distinct general ledger accounts listed on the trial balance reconcile with the aggregated ledger data.
2. Spot-checking and formula tracing: The auditor traces the generated formulas for high-volume accounts back to their sources, ensuring that the aggregation logic is mathematically sound and pointed to the correct worksheets.
3. Final touches: The auditor resolves any issues that require professional discretion, such as rounding adjustments, by instructing the agent to apply standard rounding formulas to achieve a clean tie-out.

This structured validation ensures that the final data is both complete and accurate before it is utilized for substantive testing.

In our simulation, the full process of cleaning, validating, and reconciling 59,157 rows of transaction-level general ledger data to the trial balance took about 10 minutes on average. Once the Excel agent is equipped with the necessary skill, the user leads the process through an interactive workflow: prompting the agent to analyze the data, reviewing the proposed plan, and approving execution after verifying its logic.

For firms and companies exploring AI adoption, this is a practical starting point, because it is relatively easy to implement, low-cost, and embedded in a familiar tool. However, appropriate guardrails remain essential. To use these tools responsibly, firms should:

• Preserve raw data.
• Carefully review AI–generated outputs.
• Prefer transparent Excel–based logic and documentation.
• Use structured prompts.
• Ensure compliance with firm privacy and data–governance policies.

---

About the authors

Gang (Ernest) Pan, Ph.D., is an assistant professor of accounting, and Li Wang, CPA/ABV, Ph.D., CMA, is a professor of accounting, both at the University of Akron’s George W. Daverio School of Accountancy in Akron, Ohio. To comment on this article or to suggest an idea for another article, contact Jeff Drew at Jeff.Drew@aicpa-cima.com.

---

LEARNING RESOURCES

Navigating the Next AI Landscape: Generative AI — From Chatbots to AI Agents for Accounting Professionals

Learn how to use ChatGPT to streamline your workload, save time, and boost efficiency.

10–11:30am, Oct. 8

WEBCAST

Advanced Excel: Practical Applications for Accounting Professionals

Methods for developing an effective Excel toolkit, including data preparation and analysis through final report review, are examined for accounting and finance professionals.

CPE SELF-STUDY

For more information or to make a purchase, go to aicpa-cima.com/cpe-learning or call 888-777-7077.

---

MEMBER RESOURCES

Engage365 Communities

Engage365 communities bring together AICPA members to support one another in their careers, participate in discussions, and share resources. Use your aicpa-cima.com login to get started.

Corporate Accounting & Finance community 

Make new contacts and join interesting discussions at the Engage365 Corporate Accounting & Finance community: a dedicated space to connect with experts, peers, and thought leaders as you navigate the complexities of the modern business world.

Articles

“How Are Finance Teams Really Using AI and Automation?” JofA, April 1, 2026

“How AI Is Transforming the Audit — And What It Means for CPAs,” JofA, Feb. 1, 2026

“Creating an AI Agent in ChatGPT,” JofA, Nov. 1, 2025

“Agentic AI Poised to Change the Way CPAs Work,” JofA, June 1, 2025

Podcast

“Differentiating Agentic and Generative AI — and More with a Tech Q&A Author,” JofA, Feb. 5, 2026