I run the Excel team at Microsoft. The experience you're describing sounds like it's from the earlier versions of Copilot in Excel that were genuinely limited.
Today, Excel Copilot takes a model-forward approach where we give the models full access to Excel's capabilities. We give customers the choice of the latest models from both OpenAI and Anthropic, and we encourage the models to iteratively explore the spreadsheet before taking action. It builds a full understanding of the semantics and structure of the spreadsheet, find issues in it, and ultimately gives you much better results.
Copilot can write formulas, build PivotTables, create charts, build multi-tab models, do multi-step analysis. The models are quite proficient at it, and they do a great job. We have an auto-mode which is the default where we pick the model for you, but you can also select specific models if you have a preference. I often see people switch between models to get the benefit of diverse perspectives, similar to how a diverse team approaches problems differently.
If you tried it a few months ago and walked away, it's worth another look.