🧑💻 Meet Shantanu – The Problem Solver Who Hated Lookup Errors
Shantanu was the go-to guy in his company when it came to Excel reports — but there was one thing he dreaded:
“VLOOKUP is not working.” “#N/A is showing again.” “How do I fetch values from another sheet?”
These questions not only came from his team but also popped up in his head during long hours at work.
One Monday morning, Shantanu had a typical problem: Two sheets. One had employee names, the other had bonus amounts. He needed to match names and pull bonuses.
As he began building his old VLOOKUP, he paused.
“What if I ask ChatGPT?”
💡 Lesson 1: Ask ChatGPT for a Basic Lookup
Shantanu typed:
🗣️ “I have names in column A and want to bring bonus from another sheet where names are in column B and bonus in column C. What’s the VLOOKUP?”
ChatGPT responded:
=VLOOKUP(A2, Sheet2!B:C, 2, FALSE)
And explained:
“This formula looks for A2 in column B of Sheet2 and returns the value from column C.”
Shantanu tried it. Boom. It worked! No guessing column numbers. No syntax doubts.
He smiled and whispered:
“Okay, that was fast.”
🔁 Lesson 2: Using INDEX + MATCH Instead of VLOOKUP
Later that day, he needed to look to the left of the lookup column. VLOOKUP couldn’t help.
So he asked:
🗣️ “How do I look up a value to the left of the lookup column?”
ChatGPT introduced a new hero:
=INDEX(C2:C100, MATCH(A2, B2:B100, 0))
“MATCH finds the row where A2 exists in column B. INDEX then fetches the corresponding value from column C.”
Shantanu paused. He had heard of INDEX-MATCH before, but now he understood it for real.
🧠 Lesson 3: Using LOOKUP for Approximate Matches
The next day, HR asked Shantanu to categorize employee scores into performance levels.
90+ = Excellent 75–89 = Good 60–74 = Average < 60 = Needs Improvement
He had a list of scores, and he wanted automated labels.
He asked ChatGPT:
🗣️ “How do I use a formula to label scores into 4 categories?”
Jov had a reputation in his office: the “Excel Wizard.” But lately, with increasing workloads, new data formats, and tight deadlines, even Jov’s fingers on Ctrl+C and Ctrl+V weren’t fast enough.
One evening, while sipping chai and wrestling with a nested IF statement, Jov’s colleague whispered, “Why not ask ChatGPT?”
1️⃣ Using ChatGPT with Excel Formulas
So Jov opened ChatGPT and typed: 🗣️ “Help me write an Excel formula that calculates a 10% bonus if sales exceed ₹1,00,000, else 0.”
Boom! In a second, ChatGPT replied:
=IF(A2>100000, A2*10%, 0)
Jov’s eyes lit up. Not only was the formula correct, but it also came with an explanation.
He realized: ChatGPT wasn’t just a chatbot — it was his new formula assistant.
Now Jov started doing more:
Extracting first names from full names → =LEFT(A2, FIND(" ", A2)-1)
Finding last day of a month → =EOMONTH(A2, 0)
Creating dropdowns using Data Validation (ChatGPT even explained where to click!)
2️⃣ Ask ChatGPT for a Simple Excel Formula
Jov didn’t overthink. He began typing casually:
🗣️ “Write a formula to calculate total with tax if tax is 18%”
ChatGPT returned:
=A2 * (1 + 18%)
But it also added context:
“This assumes A2 contains the base price. The formula multiplies it by 1.18 to add 18% tax.”
And if Jov asked: 🗣️ “Can you explain this like I’m new to Excel?”
ChatGPT would simplify it:
“Sure! This formula takes your number and increases it by 18%. It’s like saying: ‘Give me the price plus 18% more.’”
That’s when Jov understood — ChatGPT isn’t just a formula writer. It’s a trainer, tutor, and troubleshooter in one.
3️⃣ Using ChatGPT with Excel Tables
One Monday, Jov had a messy table: Sales data for 10 branches, each with quarterly sales across columns.
He typed:
🗣️ “How do I turn this data into a structured Excel table with filters and totals?”
ChatGPT replied with step-by-step instructions:
Select your data.
Press Ctrl+T to insert a Table.
Use the Table Design tab to enable Total Row.
Use built-in filters for any column.
Jov followed it and was shocked — no formulas, no fuss — just clean, smart data.
Then Jov asked:
🗣️ “How do I write a formula inside a table to calculate growth from Q1 to Q2?”
ChatGPT responded:
=[@Q2]-[@Q1]
“This formula subtracts Q1 from Q2 within the same row. The @ symbol refers to the current row.”
He smiled — structured references were no longer a mystery.
4️⃣ Getting More Advanced + Fixing Errors and Adding Comments
One day, Jov made a mistake. His VLOOKUP returned #N/A.
Launch Excel and open a workbook where you want to use ChatGPT.
2. Insert the ChatGPT Add-in
Go to Insert > Get Add-ins (or Home > Add-ins).
Search for “ChatGPT for Excel” or “GPT for Sheets and Docs” (some are cross-compatible).
Click Add to install it.
You may see several third-party add-ins that integrate ChatGPT. Choose one with high ratings, or GPT for Sheets and Docs by Talarian if you’re using Excel Online with Google integrations.
3. Configure the Add-in
Open the add-in side panel.
Paste your OpenAI API Key.
Test the connection to confirm it’s working.
4. Use GPT Functions
Once configured, you can use functions like:
=GPT("Explain the difference between VLOOKUP and XLOOKUP")
=GPT(A1) 'Where A1 contains a question'
Or structured prompts:
=GPT("Summarize the following: " & A1)
💻 Method 2: Using OpenAI API with Excel via VBA
This method gives you full control by integrating directly with OpenAI’s API.
📌 Prerequisites:
Excel 2016 or later
Internet access
OpenAI API Key
🪜 Steps:
1. Press ALT + F11 to open the VBA Editor
2. Insert a Module
Right-click on VBAProject (YourWorkbook)
Select Insert > Module
3. Paste the VBA Code
Function GetGPTResponse(prompt As String) As String
Dim http As Object
Dim JSON As Object
Dim apiKey As String
Dim body As String
apiKey = "sk-..." ' Replace with your API key
Set http = CreateObject("MSXML2.XMLHTTP")
Set JSON = CreateObject("Scripting.Dictionary")
body = "{""model"":""gpt-3.5-turbo"",""messages"":[{""role"":""user"",""content"":""" & prompt & """}]}"
With http
.Open "POST", "https://api.openai.com/v1/chat/completions", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Authorization", "Bearer " & apiKey
.send body
End With
Dim result As String
result = http.responseText
GetGPTResponse = ExtractContent(result)
End Function
Function ExtractContent(response As String) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = """content"":\s*""(.*?)"""
regex.Global = False
regex.IgnoreCase = True
regex.MultiLine = False
If regex.Test(response) Then
ExtractContent = regex.Execute(response)(0).SubMatches(0)
ExtractContent = Replace(ExtractContent, "\n", vbNewLine)
Else
ExtractContent = "Error parsing response."
End If
End Function
4. Use the Function in Excel
=GetGPTResponse("Write a short poem about rain.")
⚙️ Method 3: Office Scripts for Excel Online
If you use Excel Online, Office Scripts can be another way to call the API.
Steps:
Go to Automate > New Script
Use TypeScript code to call OpenAI API.
You’ll need to use fetch() to call the endpoint (like in a browser).
(Let me know if you’d like this code snippet too.)
💡 Tips for Better Usage
Use Case
Example
Text summarization
=GPT("Summarize: " & A1)
Data cleaning
=GPT("Correct spelling in: " & A1)
Code generation
=GPT("Generate Excel formula for: " & A1)
Insights & explanation
=GPT("Explain why this error occurs in Excel: " & A1)
Email drafts
=GPT("Draft a polite email: " & A1)
Translation
=GPT("Translate to French: " & A1)
🔒 Security & Limitations
Your API key should be kept private.
The API has usage limits depending on your OpenAI plan.
Responses are limited by token size (max ~4096 tokens for gpt-3.5).
VBA solutions may run slower than built-in add-ins.
📦 Bonus: Build a Custom Ribbon Button for GPT
You can add a macro button to call GetGPTResponse directly from the Ribbon. Let me know if you want help doing that!
Absolutely, Himanshu! Here’s a comprehensive and detailed list of everything you can do with ChatGPT in Excel, using plugins or API/VBA integration — complete with practical examples, formulas, and use cases across domains like data analysis, business, education, writing, programming, finance, and more.
💡 Complete List of Things You Can Do with ChatGPT Plugin in Excel
🧠 1. Natural Language Q&A
Ask questions in plain English and get direct answers.
🔸 Example:
=GPT("What is compound interest?")
📤 Output: “Compound interest is interest calculated on the initial principal and also on the accumulated interest of previous periods.”
📊 2. Data Analysis & Interpretation
Summarize data, extract insights, explain trends, or describe anomalies.
🔸 Example:
A
“Sales dropped in Q2, rose in Q3, peaked in Q4.”
=GPT("Summarize and suggest a strategy for: " & A1)
📤 Output: “Sales recovered after a Q2 dip. Focus on Q4 strategies such as promotions and bundle offers to maintain momentum.”
📚 3. Summarization
Summarize lengthy texts, emails, reports, or customer reviews.
🔸 Example:
=GPT("Summarize this feedback: " & A1)
Use Case:
Summarize customer support tickets
Executive summary of financial reports
Meeting notes into bullet points
✍️ 4. Text Generation
Generate creative or professional text.
🔸 Examples:
=GPT("Write a professional apology email for delayed shipment")
=GPT("Create a motivational quote about teamwork")
Use Case:
Email drafts
Social media posts
Taglines
SMS messages for marketing
🌐 5. Translation
Translate any text into multiple languages.
🔸 Example:
=GPT("Translate to Spanish: " & A1)
📤 Output: Input: “Welcome to our store” Output: “Bienvenido a nuestra tienda”
📝 6. Grammar & Spelling Correction
Fix common English grammar or spelling issues.
🔸 Example:
=GPT("Correct this sentence: " & A1)
📤 Input: “He go to office everydays” 📤 Output: “He goes to the office every day.”
📌 7. Paraphrasing / Rewriting
Rephrase for tone, clarity, or professionalism.
🔸 Example:
=GPT("Paraphrase this to be more professional: " & A1)
Use Case:
Make casual emails more formal
Avoid plagiarism in academic texts
Simplify complex sentences
💬 8. Explaining Excel Formulas or Errors
Get plain English explanations of Excel functions or errors.
📤 Output: “This formula searches for the value in B2 in the first column of D2:E10. If found, it returns the value from the second column. If not found, it displays ‘Not Found’.”
🧮 9. Generating Excel Formulas
Describe what you want, and let ChatGPT generate the Excel formula.
🔸 Example:
=GPT("Generate Excel formula to calculate percentage change from A1 to B1")
📤 Output: =(B1-A1)/A1
🔣 10. Converting Pseudocode to Excel Formula
🔸 Example:
=GPT("If score is over 90, return 'Excellent', else 'Improve'")
📤 Output: =IF(A1>90, "Excellent", "Improve")
🧾 11. Summarizing Financial Data
Give GPT raw financial data, and let it summarize or comment.
🔸 Example:
=GPT("Analyze this trend: Revenue = 10k, 12k, 9k, 15k over 4 quarters")
📤 Output: “Revenue was volatile but overall upward. Q3 drop may indicate seasonal weakness or market disruption.”
🔢 12. Creating Sample Data
Generate sample names, emails, numbers, cities, etc.
🔸 Example:
=GPT("Generate 10 fake Indian names with email addresses")
