.png){kind=small}
It's estimated that Excel is used by over 750 million people worldwide, a testament to its power and utility in organizing, analyzing, and visualizing data. However, even the most seasoned Excel user might not be aware of how emerging technologies like generative AI can significantly augment their spreadsheet experience. This article explores the fascinating intersection of ChatGPT and spreadsheet software like Excel and Google Sheets. We'll delve into how ChatGPT can assist with data manipulation, formula creation, and even offer analytics insights, all via natural language queries. This is not just a glimpse into the future of data analysis; it's a practical guide for anyone looking to supercharge their spreadsheet skills today.
Installation in Excel
A detailed installation guide for Excel can be found here:
Installation in Google Sheets
A detailed installation guide for Google Sheets can be found here:
The names of the functions of this extension are identical in Excel and Google Sheets. We will use Google Sheets in this tutorial.
Simple Use Cases
Generate
Let's start with a simple example. Here we use the generic GPT() function (see Docs).
=GPT("Create a blog post title for the following topic:", A2)This combines a prompt with a cell. So the resulting prompts will be:
Create a blog post title for the following topic: AI
Create a blog post title for the following topic: cars
Create a blog post title for the following topic: longevity
Create a blog post title for the following topic: SimpsonsTranslate
=GPT_TRANSLATE("Good day, how are you?", "French")
Extract
=GPT_EXTRACT("I worked 5 years at Amazon.com and then 3 years at Apple.", "companies")
The important part here is that you can basically extract anything. It can even be based on abstract concepts.
Classify
=GPT_CLASSIFY("I did not like this movie at all...", "positive, neutral, negative")
You can define the classes GPT needs to choose from yourself. It really depends on your specific setting which classes make sense.
If you analyse open-ended answers such as given in the example above we could also consider other dimensions:
1. Sentiment: Positive, Neutral, Negative
2. Complexity: Simple, Moderate, Complex
4. Clarity: Clear, Ambiguous, Confusing
5. Relevance: On-topic, Tangential, Off-topic
6. Detail: General, Specific
7. Certainty: Confident, Tentative, Uncertain
8. Emotion: Happy, Sad, Angry, etc.
9. Subjectivity: Objective, Subjective
10. Originality: Conventional, Novel
In company settings e.g. in the domain of customer support the dimensions could be:
1. Issue Severity: Critical, High, Medium, Low
3. Customer Satisfaction: Satisfied, Neutral, Dissatisfied
4. Solution Status: Resolved, Pending, Unresolved
5. Issue Type: Technical, Billing, General Inquiry, etc.
7. Script Adherence: On-script, Off-script, Mixed
9. Upsell Opportunity: High, Medium, Low, None
10. Feedback Quality: Positive, Constructive, Negative, Neutral
Advanced Use Cases
GPT_CREATE_PROMPT
Combines elements such as text, cells, or ranges to automatically construct a prompt by inserting spaces and newlines where appropriate. This enables you to effortlessly generate prompts for use in GPT functions without any additional cost, as the function itself does not invoke GPT.
Here is a more advanced example that utilizes this function:
=GPT("list all unique letters in a comma separated list that are mentioned in the following texts:", GPT_CREATE_PROMPT(A3, B2))
This collects all the unique letters mentioned iteratively from top to bottom.
This technique can be useful if you e.g. want to collect all the unique classes mentioned in a column (even if they don't match exactly) or you may want to iteratively refine a summary based more context from each cell. The bottom cell will contain the final output. This is conceptually similar to the refine method in langchain.
Estimating costs
Keep in mind that each cell will execute one API call to OpenAI. This is billed to the account of the API key used. If you plan to run hundreds or thousands of cells using the GPT() function it is advisable to do rough cost estimation first. By default the GPT-3.5 Turbo model is used:
The price is given not in terms of words but in terms of tokens. A rough rule to remember is that 100 tokens correspond to ~75 words.
You can use https://platform.openai.com/tokenizer to count the number of tokens your text contains:
If we do a calculation for the cost to execute one cell, we see that the cost is very low (~0.00425 Cents per cell). But this can still add up if you process larger amounts of data. It is worth noting that the cost increases by a factor of 20 when using GPT-4 instead of GPT-3.5.
Formula to calculate costs for GPT-3.5 Turbo (=standard ChatGPT):
Costs_in_USD = number_of_input_tokens * 0.0015 / 1000 + number_of_output_tokens * 0.002 / 1000
For up-to-date costs of the ChatGPT API consult: https://openai.com/pricing
You can track your API usage on the OpenAI website: https://platform.openai.com/account/usage.
