At a glance
Excel and ChatGPT
ChatGPT is a generative AI web tool that was released in November 2022. Generative AI can generate new content, including text, images and music.
The type of generative AI that ChatGPT is built with is generative pre-trained transformer (GPT) technology, which underpins several popular generative AI tools.
Many other types of generative AI tools are available for use, and some have specific areas of expertise.
ChatGPT has a browser-based text chat interface, so that users can simply type in their question or prompt. ChatGPT accepts natural English questions and responds with text-based, long-form responses.
It’s currently free to use but there is a subscription version. ChatGPT’s welcome screen is shown in Figure 1.
You can use your Google login details to create an account. It will ask for a phone number to enable two-factor authorisation.
The companion video shows me interacting with ChatGPT and testing the results.
In terms of testing ChatGPT as a possible Excel assistant or help desk, the immediate attraction of the interface is that, instead of wading through search engine results to find a solution, the AI provides the “right” answer.
I compiled a list of questions to ask, split between:
- Fact questions – simple responses expected
- Skill questions – formulas expected
- Opinion questions – long-form answers expected
Fact questions
ChatGPT answered the two fact questions I entered correctly.
For one, I asked what the keyboard shortcuts are for specific tasks.
The only problem with this answer is that it doesn’t specify that you need to press the keys in sequence rather than hold them down – see Figure 2.
Skill questions
Next, I asked ChatGPT to create formulas.
In most cases, ChatGPT eventually gave the correct answer.
I inserted a typing error in one question. When I corrected it, the answer provided was correct.
This suggests that the “garbage in, garbage out” rule applies as much to AI as any other digital technology.
The responses enclosed the formulas in single quotation marks. However, you need to know to omit those quotation marks when copying the formulas into Excel – see Figure 3.
One formula required the file to be saved for the formula to work, and ChatGPT didn’t mention that.
In another question, I didn’t mention Excel at all but ChatGPT figured out it was Excel related.
However, ChatGPT got confused between the VLOOKUP and XLOOKUP functions, and it struggled with the XLOOKUP syntax. You can respond to the AI and correct it, which I did.
It’s apologetic and admits it has made a mistake and corrects its errors, but you need prior knowledge of the topic you are asking about to be able to identify those errors.
Opinion questions
While facts and skills questions are always right or wrong, opinion questions call for interpretation and speculation.
The first opinion question I keyed was “what are the top ten Excel functions that accountants should learn?”.
ChatGPT replied that it doesn’t have opinions or give recommendations.
Instead, it responded with 10 functions that it said accountants use a lot. I agree with some of these, but a lot of popular functions were missing – see Figure 4.
Note: Clicking “Regenerate response” provides a different list of Excel functions as the answer.
The next question related to the top features for accountants in Excel – see Figure 5. This list is promising. The features listed are all worthwhile.
As a recommended list of features, I would swap out Solver and Scenario Manager to replace them with Power Query and Power Pivot.
Range names
A topic that divides the Excel community is whether to use range names. Range names are a powerful feature of Excel.
Many people avoid them because they believe they add an extra level of complexity to a file.
I use and advocate range names because they make using formulas easier to create and maintain. They also work well with many of Excel’s other features and functions.
My question was, “Should you use range names in Excel?”. The AI listed the advantages, which seems to support their use.
INDIRECT function
My last question related to a function that also divides the Excel community – the INDIRECT function.
While the INDIRECT function is poorly named, it can be useful. It converts the text between the parentheses into either a cell reference or a range reference.
This makes it possible to build powerful solutions that otherwise can’t be achieved using Excel’s other functions.
The problem with INDIRECT is that it stops the ability to track where a value is coming from using the auditing dependents and precedents features in the Formulas ribbon.
I use INDIRECT as a function of last resort. If I can’t achieve a solution with Excel’s other functions, then I may resort to using the INDIRECT function.
ChatGPT responded that you should use the INDIRECT function sparingly, which is the perfect answer.
Conclusion
Overall, I am impressed by ChatGPT’s knowledge of Excel. While it is still a developing technology, it seems to be a useful Excel helper for basic questions.
For intermediate or advanced topics, ChatGPT is still learning so you cannot rely on it alone. While an answer it gives might not be the solution you want, it could point you in the right direction.
Generative AI answers are more like search engine results than normal computer responses. They can change from day to day and rewrite the answer if you click “Regenerate response”. As the software develops, this may improve.
I believe it may one day be possible that a generative AI tool could become your go-to for Excel queries, rather than a search engine.
