When it comes to writing Excel formulas, ChatGPT can be a great helper. Not everyone knows how to write complicated formulas for Excel, write a Vlookup, or create pivot tables.
Thanks to ChatGPT, anyone can now generate Excel formulas and analyze essential KPIs for their business.
In this article, I will tell you how to carry out the process of formula generation step-by-step, right from setting an objective to providing feedback to ChatGPT. At the end, we discuss the drawbacks of using ChatGPT to generate Excel formulas and introduce a better alternative.
Table of contents
How to Use ChatGPT for Excel Formulas?
This section will have six steps on how to use ChatGPT for Excel formula generation:
Step 1. Define your objective
Step 2. Gather and organize data
Step 3. Upload your Excel sheet into ChatGPT
Step 4. Request formulas
Step 5. Test and validate formulas
Step 6. Provide feedback and refine
Let’s cover each of them one by one. 👇
Step 1. Define Your Objective
Your initial step should be focused on identifying your goal with Excel formulas. You must have objective clarity because it will directly influence the effectiveness of formulas you get from ChatGPT.
Start by understanding your data and end goal.
Before prompting ChatGPT to spit out formulas, take a step back and understand your data and the things you can get done with it.
Ask yourself – are you looking to perform basic arithmetic operations with the data? Or do you want to go much deeper?
You must be specific with the objective (end goal) you set for your Excel formulas.
What do I mean by this?
Instead of setting objectives like: ‘Need a formula for sales data,’ specify what you want to do with the sales data: ‘I need a formula to calculate the average monthly sales for the first quarter.’
Next, consider different scenarios.
Brainstorm different scenarios where your data might vary. Here’s one example: if you’re creating a formula to calculate bonuses for employees, consider factors like a minimum threshold, varying bonus percentage rates, or caps (if any).
Lastly, specify any constraints or assumptions that you want ChatGPT to consider when generating formulas for Excel.
Step 2. Gather and Organize Your Data
This is one of the most important steps, as gathering and organizing data can directly impact the effectiveness of ChatGPT’s outputs.
If you want functional and accurate answers from ChatGPT, ensure that your data is correctly formatted.
Here are a few things to keep in mind when gathering and organizing Excel data:
- Begin with collecting all the data you might need to build your Excel table. This could contain compiling data from different sources, databases, or CRM.
- Arrange data logically and clearly in Excel. Place related data in the same columns or rows, use consistent data formats, and format data properly.
- To make your data more understandable and manageable, convert your data ranges into Excel Tables. Also, use named ranges for specific datasets as it makes referring to them in formulas much easier and cleaner.
- Remove any irrelevant or redundant data. Rectify any discrepancies in your data, such as incorrect entries, duplicates, or missing values.
- Label columns and rows clearly. If the sheet has multiple types of data, segregate them into different sections or sheets.
Step 3: Upload Your Excel Sheet into ChatGPT
With the latest ChatGPT updates, it’s now able to analyze Excel sheets when you upload them on ChatGPT.
For this, you must have an active GPT-4 subscription. Here’s how to get started:
Open GPT-4 and click the paperclip icon ‘📎’ you see on the left end of the chatbox.
Upload the CSV file with the data.
Give GPT-4 all the specific instructions you have.
Step 4: Request Formulas
When requesting ChatGPT to generate Excel formulas, make it as direct as possible. State the action you want the formula to perform clearly.
Here’s an example prompt:
I need a formula that calculates the weighted average of these sales figures based on different regional weights.
If you have any special conditions or requirements that the formula must follow, mention it to ChatGPT in the same prompt.
This could include what to do with empty cells, rounding off rules, or conditional logic.
If you don’t understand why ChatGPT gave you a specific formula, ask it for reasoning. You can also ask for an alternative approach to calculate the same KPI in your sheet.
Lastly, ask ChatGPT about any limitations or potential issues with the formula it suggests, such as performance concerns with large datasets or compatibility issues with different Excel versions.
Here’s how I requested an Excel formula from ChatGPT for calculating Monthly sales revenue per product.
Prompt:
Let’s start with the KPI: Monthly sales revenue per product. Give me the formula to calculate it.
Step 5: Test and Validate Formulas
Copy the formula from ChatGPT and paste it into the Excel cell you want to populate with the results, making any necessary edits.
Confirm the results are correct before populating the rest of your cells.
Go on and track all the KPIs you want by asking ChatGPT Excel formulas.
Step 6. Provide Feedback and Refine
After testing and validating the formula, your next step should be to provide feedback and refine the solution if needed. You can do this by asking several follow-up questions based on the formal ChatGPT gives you.
This step involves a feedback loop with ChatGPT, where you communicate any issues, discrepancies, or additional requirements and receive improved or adjusted formulas in response.
You can start the process by summarizing the results. The summary will have an analysis of how the formula performed during testing, whether it meets the expected outcomes, and if there are any discrepancies or errors.
If the formula didn’t work as expected, identify specific issues. This might include incorrect results, errors in certain scenarios, or performance issues with large datasets.
Most importantly, communicate these issues to ChatGPT in detail. The more specific your feedback, the better ChatGPT can understand the problem and provide an improved solution.
For example, rather than saying, ‘The formula doesn’t work,’ explain the exact nature of the problem, like ‘The formula fails to exclude zero values in the calculation.’
Based on the issues identified, ask ChatGPT for specific adjustments. This could be a request to modify the current formula or to explore alternative solutions that might circumvent the identified issues.
Understand that refining a formula can be an iterative process. It might require several rounds of feedback and adjustments to get it right, especially for complex formulas and tasks.
Once you receive the adjusted formula, repeat the testing and validation process (Step 5). Ensure that the refined formula now meets all your requirements and performs correctly across all scenarios.
Drawbacks of Using ChatGPT with Excel
Using ChatGPT with Excel certainly has some drawbacks. The biggest of them, I think, is that it doesn’t allow you to onboard your team and analyze data. Here are all the drawbacks you might encounter. 👇
Drawback 1. Limited Understanding of Specific Context
While ChatGPT has a vast knowledge base, it doesn’t fully understand the specific context or unique requirements of your Excel task. It relies on the information provided by the user and might not grasp the nuances of a particular dataset or business need.
Drawback 2. Complexity and Advanced Features
ChatGPT might struggle with highly complex or advanced Excel functions and features, especially those that are less commonly used or very new.
Its training includes a wide range of common scenarios, but it’s not adept at handling very specialized or advanced Excel tasks.
Drawback 3. Can’t Onboard Your Team
When analyzing Excel data, it’s good to have your team with you.
ChatGPT lets you generate Excel formulas individually, and you can share a chat with your teammates, but there’s no way of collaborating with your team in real-time.
This can only be done if you’re using Team-GPT (more on this later).
Drawback 4. Error-Checking and Validation
ChatGPT can suggest formulas, but it can’t verify their accuracy within the context of your specific Excel file. Users need to manually check and validate the formulas to ensure they work as intended and produce correct results.
Boost Your Productivity with Team-GPT
When dealing with Excel sheets, working in a team leads to better productivity. But OpenAI doesn’t have the feature to onboard your team when you leverage ChatGPT to generate Excel formulas.
So what do you do?
Introducing Team-GPT: A powerful tool that lets you have conversations with ChatGPT, but it also lets you onboard your team members and share a chat with them in real-time.
Here are some of the features that can help resolve drawbacks when using ChatGPT to write Excel formulas:
- Folders and subfolders: Organize your sheets, formulas, and prompts in folders and subfolders, and keep your workspace clutter-free.
- View-only feature: To avoid accidental edits to the formulas, give only view access to your team members.
- Comments and threads: Ability to comment on specific threads to highlight different parts of the text. The feature will let your team ask questions and share their insights via comments.
Sounds interesting? Sign up for our free plan and leverage Team-GPT to create Excel formulas.
