As I am sure you now know, ChatGPT is an AI tool that understands and responds to text-based questions and tasks in a natural language.
You can use ChatGPT for Excel to ask the AI technology to explain Excel concepts and provide the syntax for formulas and functions. ChatGPT can also analyze Excel data and provide VBA code for macros.
This article walks you through specific examples of using the power of ChatGPT to help with everything from simple operations to complex data analytics tasks in Excel.
Let’s get started.
How to Use Chat GPT For Excel
If you don’t already have an account (it’s free!), follow these steps. If you have an account, scroll down, roll up your sleeves and let’s get into it.
Launch a browser and go to the Chat platform.
Click the signup button to create an OpenAI account.
Sign up with an email address, a Microsoft account, or a Google account.
Provide a phone number for verification and access.
When you sign in, you can immediately start sending messages to be answered. You are placed in a chat session that lets you ask the AI chatbot a series of questions.
How to Ask Chat GPT to Explain Excel Concepts
When you ask Chat GPT to explain a term or concept in Excel, always start your chat session by making clear you are referring to Excel, or a Microsoft Excel spreadsheet.
If you simply refer to a “spreadsheet”, it will give you answers that include other spreadsheet technologies e.g., Google Sheets.
You can start your session with a statement like “I’m working with Excel.” and ask your question in the next sentence.
To make it simpler, I typically use “In Excel, what is …?” when asking about a concept or term. Here is an example:
“In Excel, what is the difference between absolute and relative references?”
The output is a detailed explanation that explains both concepts and provides examples of how they differ.
You don’t need to keep prefacing your questions within the same chat session. Chat GPT will remember that you were asking specifically about Excel.
However, when you start a new chat session (or log out and re-enter), remember to tell the chatbot what technology you are working with.
How to Use Chat GPT to Write Excel Formulas And Functions
You can request ChatGPT to provide guidance on a specific Excel formula or function you want to use in your Excel spreadsheet.
It will explain the syntax of Excel functions and provide examples of how to use them. Here are some sample questions:
In Excel, what does the COUNTIF function do?
What’s the difference between IFNA and ISNA in Excel?
However, the real power with Chat GPT is when you ask it to solve a specific problem. Here’s an example I asked ChatGPT:
My Excel worksheet has values in column A and column B. How do I find values that are in both columns?
As you can see from the screenshot, Chat GPT has provided a formula that uses the column references I asked for.
The code is ready to be copied directly into Excel.
How to Use Chat GPT With Excel VBA And Macros
Excel VBA (Visual Basic for Applications) is a powerful tool to automate repetitive tasks and create custom functions.
If you need help creating Excel macros or are new to the VBA programming language, Chat GPT will guide you through the software and process.
As with when you create formulas, you can ask about syntax and usage. For example, you can ask how to trim spaces from a string of text.
You can also ask Chat GPT to create the macro for you. It doesn’t actually reach into your workbook, but it will give you all the steps you need.
Here’s an example prompt:
“Create an Excel VBA macro called “CalculateSales” that adds all the numbers in column B and multiplies the total by the currency conversion rate in cell C2. Put the result into cell D2.”
Note that the prompt specifies:
The name of the macro.
The task (add all the cells) and the specific column.
The specific cell where the result should appear.
Chat GPT starts with instructions on how to open the VBA editor and insert a new module for your macro. Then the magic happens!
The tool gives you a completed macro that you can copy-and-paste into the VBA editor.
4 Tips For Using Chat GPT For Excel Macros
Here are some tips that will ensure you get the correct answer from the AI tool when dealing with Excel Macros.
1. Be Specific About Each Task And Excel Formula
In the previous example, I asked Chat GPT to put the result into a specific cell. If I hadn’t, it would create a macro that displays the result in a message box.
With a bit of practice, you will learn to be very specific with your input.
2. Break Complex Tasks Into Shorter Pieces
I’ve noticed that the free version of Chat GPT will not produce very lengthy macros. Instead, the output stops at a point that could be halfway through what you requested.
If you have a complex set of requirements, you should break the set into multiple tasks. Give Chat GPT each task as a separate message.
The output will be multiple macros, but you can combine them into a single macro when complete.
3. Provide Variable Names For Consistency
ChatGPT chose the variable names in the example macro. They are perfectly reasonable, but you may want to apply a consistent naming standard across all macros you write.
In that case, you can give Chat GPT a style guide at the start of the session. Here’s an example:
“I will ask you to create an Excel VBA macro. Please prefix all variable names based on their data type as follows: int for Integer, lng for Long, dbl for Double, rng for Range.”
4. Always Test The Output
Chat GPT isn’t perfect. It’s important to run the macro and test it before you share it with others i.e. include quality control in your workflow.
If the results aren’t what you expected, the usual reason is that you gave ambiguous instructions. Go back to your request and make every sentence a clear, unambiguous task.
The tool has occasionally given me code that did not compile correctly. This happens when it offers new features and syntax that do not apply to the Microsoft Excel version I am using.
How to Use Chat GPT For Data Analysis
You can ask ChatGPT for advice on how to perform specific data analysis tasks like:
conditional formatting
pivot tables
You can even give the tool the data you are using.
Suppose you have a spreadsheet with sales data for different items of clothing. You want to calculate the sales for all hoodies that sold more than 10 units.
The data looks like this:
Here is an example of phrasing the task for ChatGPT:
“I have Excel data with items in column A and sales in Column B. I want to calculate the total sales of items containing the text “Hoodie” and with sales of over 10 units. Put the result into cell D7. Here is the data:”
The next step is to provide the data to the tool. You can simply copy and paste the data from Excel directly into the chat box. You lose the nice Excel formatting, but the tool understands the layout.
In this example, ChatGPT provided both:
a formula with the full syntax.
the actual answer based on the data.
Complex Data Analysis With Chat GPT
Now that you know how to provide data to the tool, you can ask for further analysis.
For example, you can get the tool to identify trends and patterns. It can also generate insights into your data.
Here is a follow-up question based on the clothing data used above:
“Using the data I provided, what type of item has better sales: Jeans, Hoodies, or T-Shirts?”
The output provided me with the steps and formulas I could use to get the answer. However, ChatGPT went further and answered the question as follows:
Hoodies have the highest total sales with 100 units, followed by T-Shirts with 60 units, and Jeans with 30 units. Therefore, Hoodies have better sales among the three item types.
As you can see, the AI tool performed analysis and provided insights and can do much more than write Excel formulas!
Of course, this is a relatively simple example with a small volume of data. The more complex your analysis requirements get, the more explicit you should be with your phrasing.
If you’re new to Excel, you may be wondering what kind of complex analytics can be done with large datasets in a spreadsheet. Check out this video on using Chi-Square tests, and then use ChatGPT to assist.
How To Integrate Excel And ChatGPT
So far, you’ve learned how to use the browser chatbot to answer questions and provide formulas and VBA code.
Let’s look now at integrating ChatGPT directly into your Excel workbook. Several vendors have provided Excel Add-Ins that let you work with the tool from your worksheet dashboard.
These Add-Ins are compatible with Excel 2016 and more recent versions. To review what’s available, follow these steps:
Go to the Insert tab
Click “Get Add-Ins” in the Addins section.
Put “ChatGPT Excel” into the search box.
Review your options.
Some of the add-ins may require payment for usage.
Troubleshooting Your Excel Spreadsheet With Chat GPT
You will occasionally get errors when working with Excel formulas, however, Chat GPT can help you troubleshoot the issues.
Your prompt for Chat GPT should include:
A description of what you are trying to do or achieve.
The specific formula you are using.
3. The contents or values of the cells involved (if possible).
For example, suppose you are trying to subtract dates in Excel but your formula results in the #NUM! error.
Here is the prompt that gets the best results from Chat GPT:
In Excel, I want to get the number of days between two dates. I get a #NUM! error when using the DATEDIF function like this: DATEDIF(B2, C2, “d”). Cell B2 has the value “10-Apr-23” and Cell C2 has the value “1-Apr-23”. Please explain the error.
The output from Chat GPT noted that the start date in the example is greater than the end date, which is the cause of the error. The next section of the output provided an amended formula.
3 Occasional Issues With Chat GPT
Chat GPT is a new technology, and you may encounter some issues in your usage.
1. Time-outs
The launch of Chat GPT was hugely successful, with millions of people signing up to use the AI platform. Unfortunately, sometimes the platform struggles to meet the demand.
When you are asking multiple questions in a single session, the chatbot may suddenly stop responding, it happens.
Try these solutions to continue working with the tool:
Refresh your browser.
Start a new chat session (the + sign).
Log out completely and log back in.
2. Latest Excel Features Are Missing
The rollout of Chat GPT in 2023 was based on a massive pool of information with a cut-off point in September 2021. This may be updated in the near future, but there will be a time lag until the tool starts operating in real-time.
This time lag means that Chat GPT does not have information about more recent Excel features or functions added by Microsoft.
If you’re not sure what may be missing, you can search the Microsoft Techcommunity website for the latest announcements of new Excel features.
3. Incorrect Answers Or Formulas
Chat GPT isn’t perfect. It will occasionally give you incorrect information. It may provide you with syntax or solutions that apply to older versions of Excel.
If the tool gives you a formula that produces an error or the wrong results, it’s worth rephrasing your prompt or question to be clearer.
However, the tool may keep giving you the same “answer” when you ask the rephrased question. In that case, there’s no point continuing.
Instead, you can ask it for an alternative solution with a different function or formula.
3 Future Developments for ChatGPT and Microsoft Apps
The integration of ChatGPT with Excel is only the beginning of a new era in the world of AI-powered productivity tools.
As technology continues to advance, both OpenAI and Microsoft are committed to enhancing the capabilities of ChatGPT, and there is significant potential for further expansion and customization of AI in various applications.
In this section, we will explore the future developments and trends that are expected to shape the landscape of AI integration in the coming years.
[wpforms id=”211279″]
1. Ongoing Improvements to ChatGPT’s Capabilities
OpenAI is working on enhancing the capabilities of ChatGPT in order to deliver an even more powerful and efficient AI assistant for Excel users. The ongoing improvements to ChatGPT focus on several key areas:
Enhanced natural language understanding and processing: ChatGPT’s ability to comprehend and interpret human language is going to be further enhanced, making it easier for users to interact with the AI in a more conversational and intuitive manner. This includes better recognition of user intent, more accurate responses, and a broader understanding of various linguistic nuances and contexts.
Improved data analysis techniques and predictive analytics: OpenAI is working on equipping ChatGPT with more advanced data analysis methods and algorithms to enable it to provide deeper insights and more accurate predictions. These improvements will help users uncover hidden patterns and trends in their data, ultimately leading to better decision-making and strategic planning.
Incorporation of machine learning for increased adaptability to user preferences and tasks: There are plans to integrate machine learning algorithms into ChatGPT, allowing the AI to learn from user interactions and adapt its behavior accordingly. This will result in a more personalized user experience, as ChatGPT will be able to tailor its suggestions, insights, and assistance to better align with individual user preferences and specific tasks.
These ongoing improvements are designed to ensure that ChatGPT remains at the forefront of AI-powered productivity tools, offering users an even more efficient and effective way to manage their data and unlock the full potential of Microsoft Excel.
2. Expansion of AI Integration in Other Microsoft Office Applications
The success of ChatGPT’s integration with Excel has opened the doors for the expansion of AI-powered features across the entire Microsoft Office suite.
Microsoft’s $10 billion investment in OpenAI shows that it’s dedicated to incorporating cutting-edge AI technologies in other Office applications to create a more cohesive and efficient user experience.
The key areas of AI integration in other Microsoft Office applications include:
Seamless collaboration between Excel, Word, PowerPoint, and other Office tools: By implementing AI-driven features, Microsoft aims to enhance cross-application collaboration and streamline workflows. This includes real-time data sharing and synchronization, context-aware document linking, and smart content transfer between applications.
AI-powered templates and wizards for document creation and formatting: There are plans to introduce AI-driven templates and wizards in Word, PowerPoint, and other Office applications. These tools will help users create professional documents and presentations with ease by providing intelligent suggestions for layout, design, and content based on their specific needs and preferences.
Smarter search and content recommendation based on user behavior and context: Microsoft is working on improving the search functionality within Office applications by integrating AI-powered content recommendations. By analyzing user behavior, context, and document metadata, the AI will provide more relevant and personalized search results.
Natural language processing for advanced editing and proofreading: Grammar suggestions will become more accurate once Microsoft expands the use of natural language processing to Office applications like Word and PowerPoint. Users will gain advanced editing and proofreading capabilities, including context-aware spell-checking and even content generation or summarization based on user input.
Voice assistance and dictation: Microsoft is exploring the potential of integrating voice-enabled AI assistants into Office applications, allowing users to control and interact with the software using voice commands. This feature would enable hands-free editing, document navigation, and content input, making the Office suite more accessible and user-friendly.
Once AI-powered features are integrated into the entire Microsoft Office suite, users can expect a more intelligent, efficient, and collaborative experience that harnesses the full potential of the latest advancements in AI technology.
3. Potential for Industry-Specific Customization
The integration of AI into Microsoft Office applications offers significant potential for customization tailored to the unique needs of various industries.
Industry-specific solutions will become increasingly relevant and valuable as the tech continues to evolve, driving efficiency and innovation across a wide range of sectors.
The key areas where industry-specific customization could make an impact include:
Tailored AI solutions for various sectors: Companies within industries such as finance, healthcare, manufacturing, and retail can benefit from AI models specifically designed to address their unique challenges and requirements. These customizations may include specialized data analysis techniques, industry-specific insights, and regulatory compliance assistance.
Integration with third-party tools and platforms: To provide a comprehensive suite of AI-powered solutions, ChatGPT and other AI technologies can be integrated with industry-specific tools and platforms to streamline workflows and enhance the overall user experience. For example, AI-driven features could be incorporated into project management tools for the construction industry or electronic health record systems for healthcare providers.
Development of specialized AI models to address unique industry challenges and requirements: By creating AI models tailored to specific industry needs, businesses can harness the full potential of AI-driven insights and recommendations. For instance, a ChatGPT model designed for the pharmaceutical industry could help researchers analyze complex datasets, identify potential drug candidates, and optimize clinical trial design.
Custom training and support for industry-specific AI applications: To ensure maximum benefit from industry-specific AI customizations, users may require specialized training and support to effectively integrate the technology into their workflows. This could include dedicated tutorials, case studies, and expert guidance for each industry that enable users to take full advantage of AI-driven features.
Ongoing collaboration with industry experts: In order to develop and refine industry-specific AI solutions, it is essential for AI developers to collaborate with experts from various sectors. This collaboration will help ensure that AI models are aligned with real-world needs and can effectively address the unique challenges faced by each industry.
The potential for industry-specific customization of AI technologies like ChatGPT is vast and could offer transformative solutions for a wide range of businesses and sectors.
Also, Companies can unlock new levels of efficiency, innovation, and competitive advantage by leveraging the power of AI to address unique industry challenges and requirements.
Our Final Thoughts on ChatGPT for Excel
Leveraging the power of ChatGPT for Excel can revolutionize the way users interact with spreadsheets and enhance their overall experience.
By enabling natural language processing, ChatGPT serves as a powerful assistant that simplifies tasks, boosts productivity, and makes data analysis more accessible to a wider audience.
But, there is a BUT, there is always a BUT, isn’t there?
Utilize ChatGPT to assist and expand, but don’t rely on it for everything. Nothing can compare to solid learning and understanding, well, for now, anyway.
