Artificial Intelligence (AI)
The spreadsheet that only opens once a year
I reopened my annual budgeting workbook after a year and found formulas and VBA hiding logic nobody had written down. Here is how AI helped me read and repair it, and why a human still has to approve the result.
Published
When planning season came round again, I opened the budgeting workbook I had last touched a year earlier. I had one change to make and assumed it would take an afternoon.
Within minutes I was following formulas that pointed at other formulas, which pointed at other worksheets. Then I found some VBA I had completely forgotten writing. The logic that drives the whole budget was all there, but none of it was visible, and the only documentation was whatever I could remember from twelve months ago.
It felt like arriving at a footpath signpost that has stood through a few winters. The route still exists and most of the signs still point somewhere, but one has faded so far that nobody can read it any more.
This blog covers what I did next, how AI helped me read and repair the workbook, and the part it cannot do for you, which is deciding that the result is right.
Why hidden spreadsheet logic is a business risk
A budgeting workbook that only one person understands is a single point of failure, whether or not anyone has called it that. If that person is away during planning season, or has moved on, the organisation is left with a file that produces numbers nobody can fully explain.
An annual spreadsheet makes the problem worse. A workbook you use every week stays in your head. One you open every twelve months does not, so each return starts close to scratch and the cost of rediscovering the logic is paid again every year.
What hidden logic looks like in practice
My workbook showed all four of the usual signs. Formulas referred to other worksheets with no explanation of why. Macros had been written for a purpose I could no longer recall. A validation cell that should have returned zero did not. And several worksheets had been abandoned years earlier because they were so out of date and broken that repairing them never seemed worth the time.
Should you patch a legacy spreadsheet or rebuild it?
I had two options. I could spend an unknown amount of time untangling the existing workbook, find where the change needed to go and hope I broke nothing on the way. Or I could put the same effort into modernising it, moving the logic somewhere more visible, testable and easier to maintain.
I have worked with old and new technology for long enough to know what the modern approach looks like, and I knew this workbook was overdue for it. Even so, patching still felt quicker and cheaper, because I knew the spreadsheet. Familiarity makes the old way look safer than it is. The cost of a patch has no upper limit, and every patch adds to the pile for whoever opens the file next, including me.
Planning season does not wait for a rebuild, so in practice I am doing both. I am keeping the existing workbook alive for this year's budget while working out what should replace it. Patching a faded sign keeps one walker on the right path today. Repainting it means the next person does not have to guess.
How AI helped me understand the workbook
Finding out why a check cell would not return zero
Every good model has a check cell that should return zero when everything reconciles. Mine did not. Two numbers that should have matched were different, and the cause was buried in interlinked formulae across several worksheets. Tracing it by hand would have meant hours of following precedents from tab to tab.
Instead I asked Copilot why the two cells were different. It followed the formulae through the worksheets and told me exactly where the problem was.
Moving to Claude for the larger changes
The version of Copilot I was using was unlicensed, which limited how much I could do each day. This year I had significant additions to make to the budget and did not want to wait a day to carry on, so I installed the Claude add-in for Excel.
- With my guidance, which matters a great deal, Claude:
- validated my cross-check values
- created new sheets giving different views of the budget
- built further sheets from content held in other spreadsheets and documents
- added a Scenario sheet to support what-if analysis
- improved the layout of the sheets so that headline figures are easier to spot and the workbook tells a clearer story
- made the annual summary sheets easy to roll forward, so adding a new year is now a one-line request
It also saved enough time that I got round to repairing worksheets I had abandoned several years ago.
Writing the documentation that never existed
I asked Claude to document the workbook as well, setting out the purpose of each sheet and explaining the key calculations. The logic that started this whole exercise now exists outside my head, which is what will make next year's visit shorter.
Why a human still has to stay in the loop
Everything the AI produced for me, whether a formula, a new sheet, a layout change or a paragraph of documentation, had to be checked, validated and approved by me before I relied on it. I directed the work, reviewed the output and signed it off. The accountability for the budget stays with me and cannot be handed to a tool. The Scenario sheet is a good example. It makes what-if questions quick to ask, but a person still has to decide which assumptions are realistic and which results belong in a budget discussion.
There is a practical side to this as well. The check cell was what told me something was wrong in the first place, and the AI explained why. That order matters. Reconciliations, totals that must agree with other systems and a sense of what last year's figures looked like are the controls that let a person judge whether an AI-built sheet is right. If a workbook has none of them, AI can produce something that looks convincing and is wrong, and nothing will flag it.
Keeping sensitive financial data safe when using AI tools
Budgets are sensitive, so where the AI runs matters as much as what it does. At PTR, both Claude and Copilot work inside our Microsoft 365 environment, which means the data is protected in the same way as any other PTR data.
If you want to try this yourself, check which AI tools your organisation has approved before you open a workbook. Never upload sensitive workbooks or documents to free, cloud-based AI tools, because you could lose control of where that data ends up. If your organisation has no AI policy yet, that is worth raising before the first workbook goes anywhere.
Where Microsoft Fabric fits when a spreadsheet outgrows Excel
Using AI to understand and document a workbook makes a legacy spreadsheet safer to live with. It does not change where the logic sits. The longer-term fix is to move calculations that must be repeatable out of individual cells and macros and into a platform where they are visible, tested and shared.
Microsoft Fabric is the platform we use for this at PTR, because it brings the components of a business intelligence solution into a single environment. A typical pattern is to hold the data in a lakehouse or warehouse, apply the business rules there once, and report on the results in Power BI. Excel can stay in the picture for planning and what-if work, but the logic that drives the headline numbers no longer depends on one person's memory of a workbook.
That will not suit every spreadsheet. A small, stable model owned by one team may be perfectly well served by a documented workbook. The sensible first step is to look at which spreadsheets your organisation actually relies on and how much hidden logic each one carries. PTR's Fabric consultancy starts with a review of your current data estate to decide whether Fabric is the right fit.
How PTR can help with legacy spreadsheets and Microsoft Fabric
PTR was working with clients on PowerPivot and Power Query in Excel before Power BI launched in 2015, so spreadsheets that have outgrown themselves are familiar territory for us. As a Microsoft Solutions Partner for Azure, Data and AI, we combine consultancy and training so that your team understands whatever we help you build.
Depending on where you are starting from, these services may help:
- Microsoft Fabric consultancy, including a review of your data estate and migration from legacy or Azure-based systems
- Power BI consultancy, for reporting that replaces spreadsheet summaries
- Data platform consultancy, if you are deciding where your data should live
- Microsoft Fabric training, including the one-day Creating a Microsoft Fabric Data Warehouse workshop and an introduction to Microsoft Fabric Data Agents, which lets people ask questions of business data in plain English
For more on the platform side, see our blogs on upgrading to Microsoft Fabric, why Microsoft Fabric needs agentic AI to deliver business value and reviewing your data platform.
If you have a workbook that only one person understands, email info@ptr.co.uk or call 0118 979 4000 and we can talk through which of the options above would suit it.
MD
Mandy Doward
Managing Director
PTR’s owner and Managing Director is a Microsoft certified Business Intelligence (BI) Consultant, with over 35 years of experience working with data analytics and BI.
Frequently Asked Questions
Couldn’t find the answer you were looking for? Feel free to reach out to us! Our team of experts is here to help.
Contact Us