A Look at the Advantages and Disadvantages of Using AI Tools for Excel Tasks
Bottom Line: Gain insight into how AI tools like ChaptGPT can help with Excel. Also learn some of their limitations and drawbacks.
Skill Level: Beginner
Watch the Video
Artificial Intelligence Is Here
It's amazing how, seemingly overnight, AI tools have found their way into all sorts of professions and industries. These include journalism, customer service, computer programming, and blogging. AI (Artificial Intelligence) is now part of the Excel world as well, so let's take a look at how AI can help or possibly hinder your Excel tasks.
I find it interesting how these tools output high-quality text faster than a person can type, but about as fast as we can read. I believe the output is actually one of the reasons we are so fascinated with them, but that might just be my unpopular opinion. 😉
One of the most popular tools to use right now is ChatGPT by OpenAI.
I can ask ChatGPT, for example, to write a macro that creates a Table of Contents sheet. In seconds I receive something like this:
It's astounding how far technology has come. Let's take a look at some of the ways AI can be used with Excel.
Uses for AI Tools in Excel
Tools like ChatGPT can be used in several ways to help with Excel.
Maybe you're working in a file created by someone else and you come across a formula you don't understand. You can copy the formula, paste it into ChatGPT, and tell ChatGPT to explain it.
Here's an example where I asked the AI tool to explain an XLOOKUP formula that I copied from a spreadsheet.
It's important to note that every time you ask it a question, it will give you a different answer. Sometimes the answers are longer and more detailed; sometimes they are short. If you're not happy with the answer you're given, you can hit the Regenerate Response button and the AI will try again.
The example above is relatively simple, but I've asked ChatGPT to explain more complex and nested formulas, and it does a pretty good job of breaking down the various components as well. Again, if it doesn't give you the level of clarity you're looking for, you can regenerate the response or even ask the tool to explain it with more detail.
Important to Note
It's imperative you understand that the answers given by ChatGPT and other AI tools are not always 100% correct. The tool is pulling from information all over the internet to create its answer and sometimes the info it gathers can be outdated or false.
For example, one of the answers the AI gave me about the XLOOKUP formula above told me that the lookup column needs to be the first column in the table and that the table needs to be sorted in ascending order.
This simply isn't true and was probably retrieved from an internet source written about VLOOKUP before XLOOKUP ever existed.
OpenAI, the creator of ChatGPT, does warn that the tools can produce incorrect results in its list of limitations.
2. Solve Problems
ChatGPT can also give instructions to tell you how to do things you don't know how to do. For example, I asked it how to combine worksheets, which is a common question among Excel users, and it gave me guidance for how to go about it in three different ways.
Again, the answers may not always be completely accurate, but depending on what you need or already know, they can still be helpful in getting you where you want to go.
3. Other Ways to Use ChatGPT
Here are a few other ways to put AI tools to use.
a. Explain a Macro.
You can copy and paste a VBA macro into the tool and ask it for an explanation of what it does.
b. Be Creative.
You can ask the tool to write something in a specific tone to customize its response. Tell the tool you want it explained in technical terms or as if you were a small child, or ask it to insert some humor into the response and see what happens. I asked ChatGPT to explain VLOOKUP like an angry drunken sailor, and it rose to the occasion with a fun response.
c. Create Data
You can ask the tool to come up with a fake set of data in order to practice some of your Excel techniques. Here's an example of a data set it created for theoretical coffee sales.
d. Analyze Data
Copy and paste some data into the tool and ask it to analyze the numbers. It will probably give you some feedback about the highs, lows, averages, etc.
e. Write Macros
As mentioned above, you can ask ChaptGPT to write a macro for you. The response can range from simple to more complex, and most of the time these answers will work. However, I did get a response where some of the macro code was written outside of the box, while the rest was inside:
Obviously, something like that can be confusing, especially if you are relatively new to macros. But overall, the responses can give you a pretty good structure to start from.
Assistant, not Replacement
So will a robot be taking over our jobs anytime soon?
It's important to know that these Artificial Intelligence tools are here to stay and that they are only going to improve with time. The errors and mistakes were are seeing today will gradually get better, so I definitely encourage you to get familiar with using these tools.
However, at least for the foreseeable future, AI is more like an assistant than a replacement. AI tools can automate time-consuming tasks to improve your overall productivity. They also provide a great launching point that you can use your human brain to improve and enhance.
You don't want to blindly use these tools and trust the answers without checking, fixing, or improving them. Relying solely on AI tools to provide formulas, macros, or answers can cause you embarrassment when mistakes come to light.
So, as helpful as these tools are, you still need to be familiar with Excel and all of its features and functions—at least until the robots get smarter than us (and/or take over the world😉).
I hope this post has whet your appetite for seeing what AI tools can do for you and that you spend some time playing around with ChatGPT to get familiar with its abilities and limitations. Let us know in the comments if you've already started using AI to help with Excel, and if so, what you are using it for. We'd love to hear about it.
Live Webinar: Thursday February 2nd, 2023 @ 9 AM PST
Interested in learning more about AI and the future of Excel? Worried about how Excel could affect your job as a data analyst?
Join us for a free webinar on 2/2/23 where we discuss modern Excel tools, and how to stay relevant as a data analyst in 2023.
Seats are limited! Save your spot here.
Excellent overview of AI and chat GPT! It’s good to be aware that explanation results are not always consistent. I haven’t used chat GPT but will bear in mind your advice to be aware of its limitations. I appreciate your knowledge!
I heard about ChatGPT from a colleague just over a week ago. I have tried to register every day since, and every time, it says they are at capacity. It certainly looks cool, but I am frustrated I can’t try it.
Good job – thanks for that. The point that needs emphasis, is to validate the output.
Will the webinar be recorded?