Just like Copilot or its parent ChatGPT, the Copilot function in Excel use for analysis but not so much for getting hard facts. Our testing of the new Copilot() feature shows that no-one should trust what AI says is true.
We’ve taken Microsoft’s example and extended them a little to show the real-world pitfalls and tricks for using Copilot() in Excel. It wasn’t hard to find factual errors in Copilot() responses, some big, small or not understandable!
Some lessons we learned from Copilot()
- Copilot has a slippery and changing concept of ‘truth’.
- Carefully word the prompt and context.
- Carefully check results.
- Sorting has to be done as part of the Copilot prompt, but isn’t always correct.
- Filtering to exclude some results, individual or as a group, can be done in the prompts
- Copilot has trouble parsing first and last names with a middle initial.
- Headings for Copilot() lists may or may not appear. Better to be specific.
Airports
Microsoft’s Copilot() example shows how to get a list of airports.
Like most Microsoft carefully chosen examples, if you do a little digging the problems arise.
We add a filter by population and asked for more details “Airports in cities over half million people, show airport name and code”
As you can see, Copilot() returns a dynamic (spill) array which can include multiple columns.
However, there are problems:
- Gold Coast/Tweed Heads has a population of over 700k and it’s airport should be on the list.
- Canberra and Newcastle have populations just over 500k and should have been included.
- The proper name is “Sydney Kingsford Smith Airport”. Changing the prompt to ask for “full airport name” gives a more accurate result.
- Just one example of how careful wording of AI prompts is important.
Which only confirms what we’ve said about AI for some time:
Always check the facts and be careful about the wording of prompts.
Another factual error
Just another factual error we found in our testing. Asking for ” Airports in cities over half million people, show airport name and code” for the UK might seem correct but it’s not.
London has 5 or 6 airports (it depends). However, you define “London airports”, it should at least include Gatwick (LGW) and London City (LCY). Luton, Stansted and especially Southend are also called “London airports” with a certain generosity of spirit <g>.
Copilot makes the same mistake with New York, only listing JFK and not La Guardia (LGA).
But change the prompt to ask for distance from a location and suddenly Gatwick airport appears! LCY, which is even closer to Greenwich, is still missing.
This isn’t pedantic nit-picking, it’s examples of a common problem with the current AI systems. We rarely get a ‘factual’ result from Copilot or ChatGPT that doesn’t need some changes.
Different results on the same page!
For no explained reason, “Gold Coast Airport” appears in this Copilot() result (B17 result and below) even though it’s not shown in another result on the same page (Cell B3 and the spilled cells). All it took was small and irrelevant changes in the prompt.
Sorting and filtering
Copilot() results can’t be directly sorted or filtered in Excel. Any sorting or filtering has to be done in the prompt or context.
We changed the context to exclude a result and ask for sorting “Australia, exclude Perth – sorted alphabetically”
However even sorting can’t be relied on. We did some tests with US Presidents and all was well until we asked for the list to be sorted.
=COPILOT(“US Presidents”, “include headers, show first name and initial, last name, date of birth and date of first inauguration, show age in years at inauguration, sort by age at inauguration, youngest first”)
Suddenly the early presidents like George Washington disappeared. The list is sorted by the date of inauguration not age.
Names and initials
In earlier tests we asked for “show first name, last name” in separate columns. Copilot added initials to the start of the “Last Name” column as in “Ulysses” then “S. Grant”. Changing the prompt to “show first name and initial, last name” fixed that.
Multiple Contexts
Copilot() doesn’t support multiple contexts for a prompt but that’s OK because a context string can be built up from many strings in different cells. Cells B12 to B15 can be joined together to make a single context request to Copilot().
The TextJoin() function can create the “Full Context” easily. Cell B16 above reads =TEXTJOIN(“, “,,B12:B15)
Tip: this is the best way to try out and use Copilot(). Create cells for the prompt and context then join them together for Copilot(). Change any of the source cells and Copilot(() will update automatically.
Table headers or not?
The Copilot() function is inconsistent about whether it adds headings to results or not.
In our tests, headings would appear or disappear with even a minor unrelated change of prompt or context.
For consistency we add either “include headers” or “exclude headers” into the context.
Warning the term “show headers” will sometimes return only the headings and no other data. Better stick with the words “Include” and “Exclude”.
Just one more example of Copilot getting facts wrong
We asked Copilot() for a list of UN countries and immediately noticed something was wrong. There are 193 countries in the United Nations not 194 so where is the other country? The answer is complicated.
=COPILOT(“list of UN countries, include headings”,”show short name, full name,date of UN entry and continent”)
Copilot has added Vatican City which isn’t a member, only an “Observer” but at least there’s a note.
Even more curious is Palestine which is also an Observer member only but is listed by Copilot as a full member which joined on 29 Nov. 2012.
That means Copilot has two non-members listed and there must be one member country not listed.
If that wasn’t enough, all the Central American and Caribbean countries are lumped into the “North America” continent which isn’t what most people would expect.
