ChatGPT Plugins
September 12, 2023

9 Interesting Things You Can Try with ChatGPT and Excel Sheets

You may already know ChatGPT as a game-changer for content creation, but did you know it can also supercharge your data analysis?

Integrating ChatGPT with Excel

There are 3 ways in which you can integrate ChatGPT with Excel or work on your Excel sheets using the ChatGPT interface: 

  1. Using the AI-powered add-on for your MS Excel offered by Microsoft - ChatGPT for Excel.
  2. Using the ChatGPT plugin “Chat With Your Data” with the ChatGPT Plus account.
  3. Using the ChatGPT plugin Code Interpreter to upload the files.
  4. Directly adding content of the CSV file data in the interface. However, this would work for limited data entries, as there is a character limit.

How can ChatGPT simplify your Excel experience?

1. Extracting information from PDF/DOC and populating Excel Sheets

Automating manual, repetitive work is like putting processes on autopilot mode and keeping an eye on the quality control of the output. 

To initiate the process with ChatGPT, you can use a prompt like:

Can you help me extract specific details from resumes and organize them into a spreadsheet? I'd like to capture the following information from each candidate: name, email address, phone number, university, and year of graduation. Additionally, please provide a brief summary for each resume.
[Upload the PDF files or share URLs of cloud documents or insert the content of the PDF]

2. Automate data standardization to a specific format

Ensure that the data meets your specific criteria, is consistent, and is standardized. Some ChatGPT prompts that you can use:

Can you review the Excel file to identify any missing values and ensure the date format is consistent with MM/DD/YYYY?
[Upload the Excel file using the Code Interpreter plugin]

You can even control data quality and improve data integrity with prompts like:

I have a list of new leads in a spreadsheet. Can you cleanse the 'Email' column by eliminating extra spaces and ensuring each email adheres to a valid format?
[Upload the Excel file using the Code Interpreter plugin]

3. Make your marketing report more effective

Get deeper insights for indicators like ROI and audience engagement, generating recommendations for future campaigns with ChatGPT. 

Prompt example: 

Based on the provided data, can you evaluate the success of our recent marketing campaigns and produce an in-depth report? This report should cover ROI analysis, a thorough examination of audience engagement metrics, and specific suggestions to enhance future marketing efforts.

4. Getting a deeper analysis of the sales funnel

Use ChatGPT to go beyond the basic metrics like total sales and monthly averages to explore more nuanced questions about customer lifetime value (LTV), seasonality, ROI, and product bundling from your sales datasheets. 

See this example prompt:

Can you auto-generate a detailed sales analysis in Excel comparing last year's data to this year's? The report should highlight total sales, average monthly sales, and top sellers. I'm also interested in insights on:
1. The product with the highest Lifetime Value (LTV)
2. Seasonal trends impacting specific product sales and their occurrence.
3. Products yielding the highest ROI or margin.
4. Products that are commonly purchased together.

5. Generate in-depth customer feedback reports

Customer feedback is a goldmine of insights that can drive business improvements and enhance customer satisfaction. You can categorize comments by sentiment, identify recurring themes, and even generate actionable recommendations with ChatGPT. These are just a few ways in which you can work with ChatGPT to improve customer engagement. 

Try this prompt:

I possess customer feedback datasheets from the last 5 years. Can you evaluate this feedback, categorize comments based on sentiment, pinpoint recurring themes or concerns, and suggest ways to enhance our customer service?

6. Generate inventory reports in seconds

Automating inventory generation reports can keep you updated in real-time on your stocks as well as make timely business decisions. You can use ChatGPT to help you build Macros and VBA code that you can use to automate the report generation.

Try this prompt: 

Can you guide me on setting up an automated inventory report in Excel that aligns with real-time database updates and features a summary dashboard?

7. Keep your investment portfolio updated with the latest stock market trends and data

Leverage ChatGPT’s ability to fetch real-time data and perform time series analysis to make smarter and more informed investment decisions.  

Try this prompt to gather the latest stock prices and update your Excel data: 

Can you refresh my Excel portfolio tracker with the most recent stock prices from the given websites and recompute my portfolio's current worth? [Please provide the website URLs.]

To conduct a time series analysis on your portfolio, try this prompt: 

With two years of historical data for over 100 stocks in an Excel file, can you perform a time series analysis to predict future stock values, spot trends, and gauge volatility?

8. Conduct an instant analysis of your loan repayment scenarios

You can make use of ChatGPT’s language model to analyze different repayment scenarios and even ask for suggestions to save a significant amount in the long run. 

Try this the next time you’re running a repayment scenario: 

Can you craft a loan repayment analysis in Excel that delves into different scenarios? I'm keen on understanding how adjustments in the interest rate, loan duration, and monthly installments influence the cumulative interest over the loan's tenure. [Upload the worksheet.]

9. Troubleshooting error messages

For errors in a formula, ChatGPT can assist in identifying syntax mistakes, referencing errors, or incorrect logic in the formula. It can suggest corrections or alternative formulas to achieve the desired outcome.

ChatGPT logo
Prompt: I'm trying to calculate the weighted average of product ratings in the 'Reviews' worksheet. I'm using the formula =SUMPRODUCT(B2:B100, C2:C100)/SUM(B2:B100, C2:C100), where column B contains the ratings and column C contains the weights. However, I'm getting a #DIV/0! error. Can you help me identify and correct the mistake?

Response: The error you're encountering is likely due to a division by zero in the formula. It appears that the issue is with the denominator of your formula, where you're using SUM(B2:B100, C2:C100) instead of summing only the weights. The correct formula to calculate the weighted average should be =SUMPRODUCT(B2:B100, C2:C100)/SUM(C2:C100).

This will multiply each rating by its corresponding weight and then divide it by the sum of the weights, giving you the weighted average.

From automating tedious tasks to generating insightful reports, ChatGPT's integration with Excel offers a powerful toolkit to enhance your analytical productivity. Whether you're looking to streamline your marketing efforts, delve deeper into sales metrics, or make informed investment decisions, ChatGPT can assist you with any queries that you would have. However, tread carefully with sensitive data by reading the privacy policy.

Give these prompts a try the next you are working on any data. You never know what new dimension of data-driven decision-making you would unlock. If you’ve tried it out, let us know about your experience here.

Want to cut the clutter and get information directly in your mailbox?