top of page

Using ChatGPT in Excel and Google Sheets

Updated: May 3

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).


Example of GPT() function
Example of GPT() function

=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: Simpsons


Translate


=GPT_TRANSLATE("Good day, how are you?", "French")

Example of GPT_TRANSLATE() function
Example of GPT_TRANSLATE() function

Extract


=GPT_EXTRACT("I worked 5 years at Amazon.com and then 3 years at Apple.", "companies")

Example of EXTRACT() function
Example of EXTRACT() function

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")

Example of GPT_CLASSIFY() function
Example of GPT_CLASSIFY() function

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))

Example of GPT_CREATE_PROMPT() function
Example of GPT_CREATE_PROMPT() function

This collects all the unique letters mentioned iteratively from top to bottom.

Step 2 of a roll-up example
Step 2 of a roll-up example

Step 3 of a roll-up example
Step 3 of a roll-up example

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

Example of a GPT() function call which we will use to estimate costs
Example of a GPT() function call which we will use to estimate 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:


API Pricing of GPT-3.5 Turbo
API Pricing of GPT-3.5 Turbo

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:


This Tokenizer tool by OpenAI let's you count the number of tokens in a text
This Tokenizer tool by OpenAI let's you count the number of tokens in a text

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

Cost calculation
Cost calculation

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.


Interface to track your costs on OpenAI webstie
Interface to track your costs on OpenAI website


199 views

Recent Posts

See All

Kommentare


bottom of page