Checking large Excel spreadsheets for errors takes a great deal of time. In our test, we looked at how this process could be automated with AI. The surprising finding: the most efficient approach was not direct AI analysis, but an Excel script created with the help of AI.
The challenge: checking thousands of data points for errors
In our case, the dataset contained several thousand rows and more than 20 columns, with the data in each row needing to be logically consistent across all fields. The task therefore involved not only checking completeness and compliance with specific rules, but also assessing whether each row was plausible and internally consistent.
Whenever an error was identified, the type of error had to be described in a comments column and the relevant row marked. Until now, a manual review by a research analyst had taken many hours. Basic Excel filters could make parts of the task easier, but did not significantly speed up the overall review.
Our approach: first Copilot, then an AI-generated script
We first turned the validation rules into a prompt and passed it to Copilot in Excel. Editing permissions were also clearly defined so that Copilot could not make unintended changes. In the first few runs, numerous supposed errors were flagged, some of which turned out to be false positives.
After several rounds of refinement, we were able to improve the results considerably. The AI-assisted analysis took around five minutes per run. Although this was significantly faster than manual checking, it came with one limitation: every employee using the function would need a Copilot licence.
We therefore tested a second approach that offered greater independence: using ChatGPT 6.1 Sol, we created an Office Script for Excel. This required the rules to be defined even more precisely and unambiguously so that the script could implement them correctly.
The result: the final script identified the same errors as the optimised AI prompt, but took only two to three seconds to run. Compared with a manual review lasting several hours, this approach proved far more efficient, even when allowing for subsequent manual checking of the automated results.
What to keep in mind
The main challenge is not the programming itself, as the AI can handle that, but defining clear and unambiguous validation rules. In our test, this step took several hours and numerous iterations. The benefit is that we now have a reproducible workflow that can be adapted relatively easily.
A script will also only detect errors covered by its rules. The original spreadsheet should therefore still be spot-checked for issues the script may have missed. Final assessment and correction should remain with a human reviewer.
Our conclusion: Scripts are particularly well suited to analysing Excel spreadsheets. They are especially useful for checks that need to be repeated in the same way, as the script can be refined over time. It can also be run as often as required and at extremely high speed. This not only saves processing time, but also makes repeated use independent of AI subscriptions and individual AI providers.
By Marcel Ohrenschall