[Guite]

Originally Posted by Thad E Ginathom

I wonder if AI could analyse a heavily customised complex spreadsheet?

There is, or was, a lot of it about. Businesses run on customised excel with the original author gone. Of course, they were seldom documented. Can AI decode those messes?

If you paste all the codes in Copilot (or any other AI chat box) and ask it to analyse, it will. You can ask it to explain routine-wise, module-wise or on the whole workbook customisation. While on the chat you can ask any number of explanations you want and it will. You might get slightly different level of detail from different LLMs. I found Deepseek provides very detailed explanations, however I have not interacted with it regarding Excel macro/ VBA.

On the other hand, my experience with asking AI to refine existing codes has been more trouble than it is worth. In my case I did about 1,000 lines of codes across eight modules over multiple sessions, some on Copilot on web browser, some on Github Copilot (switching between GPT-4o and Sonnet). It probably is a mess of codes. Attempts at refinement and optimisation had resulted in errors and conflicts. As the old adage goes: "if it ain't broke, don't fix it".

[asingh1977]

Depends what the spreadsheet (read workbook) is doing.

Excel workbooks have two core components:

1:
Excel is a flat grid-view data approach, which is its beauty (or bane). Formula (or functions) can access any section of this flat structure (layered into tabs), pull in information and apply a function on it. In the programming world this would translate to a function (or method) applied to a dataset -- to give an output. The problem arises that references can be extremely complex, and this complexity goes up considerably, if the creator has chosen INDIRECT reference, which are not straight forward address lookups. One needs immense experience, and patience to decipher all these lookup references and then understand the function being applied. Charting is also available here.

2:
Visual Basic for Applications. This is an IDE available in most MS Office products. This allows for a developer (or Excel creator) to write programs (very close language syntax to Visual Basic), to do everything stated in [1] above, as well as perform interface tasks.
Example: cut paste, colors, movement of data, logical transpose of data, connect to external sources, custom format native charts, interact with other MS Object (Access, Outlook etc), and i.e. automate tasks. At the same time a lot of the Windows COM APIs are also exposed. "Functions" written here (for data), are subsequently available to [1] above. The VB forms and even objects are available here too, to create "front-ends", with event listeners.

All this would be too much for LLMs to digest and give accurate results, when prompted.

Now coming to your query:

Originally Posted by Thad E Ginathom

I wonder if AI could analyse a heavily customised complex spreadsheet?

If just raw data is being analyzed, an LLM should be able to do it. They can analyze .CSV files. But trying to rifle through all formulaic references, and then make changes based on a prompt, I do not think an LLM can do that. Unless a custom LLM just for spreadsheets has been setup. The LLM should be able to understand the VBA component in isolation, because it is "code" and the model would have those references, and be able to generate more code, or make corrections. Though the object model implementation in VBA is not that great. It is more functional in nature, and does the job well.

Originally Posted by Thad E Ginathom

I remember being asked by my boss to update one once. Granted, my 123/Excel has only ever been basic, and I didn't really understand the insurance-business mechanics it was doing but still --- it was incomprehensible.

Updating formulas in Excel worksheets is not straight forward, and takes a while to understand and successfully implement the logic. A leads to B, leads to C, leads to .... anywhere. There is logical progression (or propagation) of data, but it quickly becomes spaghetti logic per say, and difficult to follow through; start to end.

Originally Posted by Thad E Ginathom

There is, or was, a lot of it about. Businesses run on customized excel with the original author gone. Of course, they were seldom documented. Can AI decode those messes?

I really doubt it as of now.
The beast is just to large, and unstructured. LLMs will quickly hit their token limit when reading long functions, and/or large code blocks in tandem.
All business use Excel, but the logic quickly becomes a dark-science. Still Excel is an immensely powerful tool. I have seen full blown applications performing business analytics jobs, which have and ETL, data cleanser, business logic, and visualization all embedded in one workbook. It is a sorry and painful job trying to find a bug fix.