Testing CoPilot in Excel

I thought I would write tonight about some experiments that I did in using the CoPilot in Excel – not the function CoPilot() but the regular CoPilot embedded with Excel (only available for those who pay for it).

For a while I couldn’t think of any real use of the CoPilot and whenever I did think of something – more along the analytical vein – I found it meh.

But then I finally pulled up some old examples that I had to do at work, a long time ago.

CoPilot in Excel Test 1: Searching for missing invoices

There was an analyst who had to do the accrual for subcontractors working on the help desk call center. There were about 400 sbcontractors manning those calls. They worked for a third-party vendor so we did not directly employ them.

This meant we paid the third party for their work, and we had to do any expense accruals at the end of the month for any invoices not paid yet. We had multiple POs set up with this vendor and each PO had specific names of the subcontractors and the bill rate. This vendor religiously sent their invoices every week with an invoice date of Monday.

The analyst responsible for the accruals manually tracked the names and the hours worked each week. So, think of this, she had 400 subcontractors’ weekly timecards she slogged through – visualize 4 packs of 500-paper pack for the printers. During close, she would haul home those 4 packs of 500-paper to log into her spreadsheet. I know this because she showed me how she was doing it when that job was transferred to me as she was transferred elsewhere (I think – I’m faint on the memory).

Knowing myself, I knew I could not do this manual job, so I had to find another way. Fortunately, the solution was found in the fact that the vendor always invoiced each POs every Monday. Consistent patterns help. I didn’t know pivot tables, but I did know array formulas which I used to great effect. My solution was a beautiful thing.

My test for CoPilot included a list of “SAP” entries with entry date, invoice date, PO, description, and amount paid. I asked CoPilot to find any missing invoices, if there were any.

It successfully figured out the 3 missing invoices in the “SAP download” and it gave me its accrual estimate for those 3 missing invoices.

CoPilot in Excel Test 2: Change the hours in invoices

In the first test, I just assume the subcontractors all worked 40 hours a week but that was not the case at that time. For whatever reason, they actually worked odd number of hours.

In this new test, I varied the hours worked leading to varying monthly expenses. In addition, I incorporated two holiday periods: 4th of July and Labor Day weekend.

CoPilot still found the 3 missing invoices but its accrual methodology might not be what you wanted. It included the holiday weeks as part of the averaging and maybe you would normally throw out the holiday weeks.

The solution it offered was okay – it wasn’t drastically off – but you might have to tweak your prompt to get closer to what you want. But bad but maybe not optimal.

CoPilot in Excel Test 3: Find hard coded numbers

Another problem I would periodically face is somebody accidentally writing over formulas.

Maybe you notice numbers not adding up correctly, but you can’t find the hard coded number(s).

I have a way of finding it, but I wondered if CoPilot could find the hard coded number(s).

Alas, it was not to be. It gave me this long drawn-out gobbledygook that it couldn’t do it.

I was kind of surprised about that.

Tentative conclusions

CoPilot could help you, but you are going to have to test it on a case-by-case basis. In the example of missing invoices, it should be able to do that if there is a consistent pattern to be found. In that situation, you no longer have to be an Excel wizard to design formulas or techniques to find the missing invoices. What was my superpower in that situation was gone.

But…there is a caveat. It may not know how to develop the estimates. There may be some requisite judgement to develop the estimates.

Also, you may still need some Excel knowledge to overcome certain situations.

Now, I wonder if that new AI Agent can do those activities that the regular CoPilot couldn’t?