ChatGPT in Excel Data Analysis
Introduction
ChatGPT is a large language model chatbot developed by OpenAI based on GPT-3.5. It has a remarkable ability to interact in conversational dialogue form and provide responses that can appear surprisingly human. ChatGPT can also follow instructions in a prompt and provide detailed responses. We shall see how ChatGPT can help in excel application in data analysis.
How to Integrate ChatGPT with Excel
There are different ways to integrate ChatGPT with Excel, depending on your needs and preferences. One way is to use the OpenAI API, which allows you to access ChatGPT directly from Excel using VBA code. Another way is to use third-party tools that provide a user interface for ChatGPT within Excel.
To use the OpenAI API, you will need to sign up for an account and get an API key. Then you can write VBA code that sends your query to ChatGPT and receives the response. You can also customize the parameters of ChatGPT, such as temperature, engine, and frequency penalty.
To use third-party tools, you will need to install them on your computer and follow their instructions. Some examples of these tools are Sheetaki, Forensic Data Tool, and Power Query. These tools can help you generate formulas, summaries, charts, and more using ChatGPT.
You need to enable Developer mode in Excel to access the Visual Basic Editor and create a new module to add the VBA code. Here’s how to enable Developer mode in Excel:
- Open Microsoft Excel and click on the “File” menu.
- Click on “Options” at the bottom of the left-hand menu.
- In the Excel Options window, select “Customize Ribbon” on the left-hand side.
- Check the box next to “Developer” in the right-hand list of Main Tabs.
- Click “OK” to save the changes and close the Excel Options window.
After enabling Developer mode, you can access the Visual Basic Editor by clicking on the “Developer” tab in the ribbon and selecting “Visual Basic” in the Code group.
Integrating ChatGPT into Excel using VBA requires an internet connection and access to the ChatGPT API. You will also need to have a ChatGPT API key, which can be obtained from OpenAI.
Assuming you have already obtained a ChatGPT API key, here is some sample VBA code to integrate ChatGPT into Excel:
Option Explicit
Sub AskChatGPT()
Dim question As String
Dim response As String
Dim url As String
Dim objHTTP As Object
Dim jsonObject As Object
Dim accessToken As String
'Enter your access token here
accessToken = "your_access_token_here"
'Prompt the user to enter a question
question = InputBox("Ask a question:", "ChatGPT")
'Construct the API endpoint URL with the question and access token
url = "https://api.openai.com/v1/engines/davinci-codex/completions?prompt=" & _
Replace(question, " ", "%20") & "&max_tokens=150&n=1&stop=%5B%22%3F%22%2C%22%21%22%2C%22.%22%5D&model=davinci-codex"
'Send the API request and parse the response JSON
Set objHTTP = CreateObject("MSXML2.XMLHTTP")
objHTTP.Open "POST", url, False
objHTTP.setRequestHeader "Content-Type", "application/json"
objHTTP.setRequestHeader "Authorization", "Bearer " & accessToken
objHTTP.send ("")
Set jsonObject = JsonConverter.ParseJson(objHTTP.responseText)
'Extract the response text from the JSON object
response = jsonObject("choices")(0)("text")
'Display the response in a message box
MsgBox response, vbInformation, "ChatGPT"
End SubTo run this code in Excel, you need to follow these steps:
- Open Microsoft Excel and create a new workbook.
- Press Alt + F11 on your keyboard to open the Visual Basic Editor.
- In the Visual Basic Editor, click on the “Insert” menu and select “Module” to create a new module.
- Copy the VBA code provided in my previous response and paste it into the new module.
- Save the module by clicking on the “File” menu and selecting “Save”.
- Close the Visual Basic Editor by clicking on the “X” button in the top-right corner of the window.
- Now you can run the macro from Excel by pressing Alt + F8 on your keyboard to open the Macro dialog box.
- Select the “AskChatGPT” macro from the list and click the “Run” button to execute the macro.
You can download the VBA-JSON library from its official GitHub repository: https://github.com/VBA-tools/VBA-JSON/releases
Here are the steps to download and install the library:
- Go to the GitHub repository page I linked above.
- Click on the “Latest release” tag to see the latest version of the library.
- Download the latest release in ZIP format by clicking on the “Source code (zip)” button.
- Extract the downloaded ZIP file to a folder on your computer.
- In Excel, open the Visual Basic Editor by pressing Alt + F11 on your keyboard.
- In the Visual Basic Editor, click on the “Tools” menu and select “References”.
- In the References window, click the “Browse” button and navigate to the folder where you extracted the VBA-JSON files.
- Select the “JsonConverter.bas” file and click “Open”.
- The VBA-JSON library should now appear in the list of available references. Check the box next to it to add it to your project.
- Click “OK” to close the References window and save your changes.
After adding the VBA-JSON library as a reference, you should be able to use its functions in your VBA code without encountering any errors.
That’s it! The macro will prompt you to enter a question, send the question to the ChatGPT API, and display the response in a message box. Note that you will need an internet connection and a ChatGPT API key for this to work.
Pivot Tables
ChatGPT might be able to help in using pivot tables of Excel for data analysis by answering general or technical questions about how to create, modify, or interpret pivot tables. For example, you could ask ChatGPT something like:
- How do I create a pivot table from a range of data?
- How do I add filters or slicers to a pivot table?
- How do I change the aggregation function of a pivot table?
- How do I refresh or update a pivot table?
- What are some benefits of using pivot tables for data analysis?
ChatGPT might also be able to help by generating examples of pivot tables based on some criteria or data that you provide. For example, you could ask ChatGPT something like:
- Show me an example of a pivot table that summarizes sales by product and region.
- Show me an example of a pivot table that compares monthly revenue and expenses.
- Show me an example of a pivot table that calculates the average rating and number of reviews for different categories of books.
However, ChatGPT might not be able to help with more complex or specific tasks that require accessing your data source, applying custom formulas, creating charts or dashboards, etc. For those tasks, you might need to use Excel itself or other tools that integrate with it.
Here are some possible scenarios:
Scenario 1: You want to create a pivot table that summarizes sales by product and region. You ask ChatGPT: “Show me an example of a pivot table that summarizes sales by product and region.” ChatGPT responds with something like:
An example of a pivot table that summarizes sales by product and region is:
| Product | Region | Sales |
| — — — — -| — — — — | — — — -|
| A | North | $5000 |
| A | South | $3000 |
| A | East | $4000 |
| A | West | $6000 |
| B | North | $7000 |
| B | South | $5000 |
| B | East | $6000 |
| B | West | $8000 |
To create this pivot table, you need to have a data source that contains columns for product, region, and sales. Then you can follow these steps:
1. Select any cell in the data source and click Insert > PivotTable.
2. In the Create PivotTable dialog box, choose where you want to place the pivot table (a new worksheet or an existing worksheet) and click OK.
3. In the PivotTable Fields pane, drag Product to the Rows area, Region to the Columns area, and Sales to the Values area.
4. Adjust the formatting and layout of the pivot table as desired.
Scenario 2: You have created a pivot table that compares monthly revenue and expenses. You want to add filters or slicers to it so that you can easily see the data for different categories or months. You ask ChatGPT: “How do I add filters or slicers to a pivot table?” ChatGPT responds with something like:
To add filters or slicers to a pivot table, you can follow these steps:
1. Select any cell in the pivot table and click Analyze > Insert Slicer (or Insert > Slicer in Excel 2013 or earlier).
2. In the Insert Slicers dialog box, check the fields that you want to use as filters or slicers (such as Category or Month) and click OK.
3. Resize and move the slicers as desired on your worksheet.
4. To filter or slice your data, click on one or more items in each slicer.
Alternatively, you can also use report filters instead of slicers. To do this:
1. Select any cell in the pivot table and click Analyze > Field List (or Options > Field List in Excel 2013 or earlier).
2. In the PivotTable Fields pane, drag one or more fields that you want to use as report filters (such as Category or Month) to the Filter area.
3. To filter your data, use the drop-down arrows on each report filter.
Scenario 3: You have created a pivot table that calculates the average rating and number of reviews for different categories of books You want to change the aggregation function of the rating column from average to median You ask ChatGPT: “How do I change the aggregation function of a pivot table?” ChatGPT responds with something like:
To change the aggregation function of a pivot table, you can follow these steps:
- Select any cell in the value field that you want to change (such as Rating)
and click Analyze > Field Settings (or Options > Field Settings in Excel 2013 or earlier).
2. In the Value Field Settings dialog box, click on the Summarize Values By tab.
3. Choose the aggregation function that you want to use (such as Median) from
the list of options.
4. Click OK.
Data Model
A data model in Excel is a type of data table where two or more tables are in a relationship with each other through a common or more data series. A data model allows you to integrate data from multiple sources and use it in pivot tables and pivot charts.
ChatGPT might be able to help in using data models of Excel for data analysis by answering general or technical questions about how to create, modify, or interpret data models. For example, you could ask ChatGPT something like:
- How do I create a data model from multiple tables?
- How do I create relationships between tables in a data model?
- How do I use a data model to create a pivot table or chart?
- How do I refresh or update a data model?
- What are some benefits of using data models for data analysis?
ChatGPT might also be able to help by generating examples of data models based on some criteria or data that you provide. For example, you could ask ChatGPT something like:
- Show me an example of a data model that integrates sales and customer data from different sources.
- Show me an example of a data model that analyzes product performance by category and region.
- Show me an example of a data model that forecasts revenue and expenses based on historical trends.
However, ChatGPT might not be able to help with more complex or specific tasks that require accessing your data source, applying custom formulas, creating charts or dashboards, etc. For those tasks, you might need to use Excel itself or other tools that integrate with it.
Okay, here is an example of a pivot table created by ChatGPT.
| Region | Color | Sales |
|--------|-------|-------|
| East | Red | 3,956 |
| East | Blue | 2,004 |
| East | Green | 1,064 |
| West | Red | 2,496 |
| West | Blue | 4,400 |
| West | Green | 1,960 |
| North | Red | 4,800 |
| North | Blue | 2,800 |
| North | Green | 2,880 |Pivot table from this data set is inserted and selected Region for rows, Color for columns, and Sales for values. This is the result:
Pivot Table
Color
Region Blue Green Red Grand Total
East $2,004 $1,064 $3,956 $7,024
North $2,800 $2,880 $4.800 $10.480
West $4.400 $1.960 $2.496 $8.856
Grand Total$9.204$5.904$11.252 $26.360It can interact in conversational dialogue form and provide responses that can appear surprisingly human. Data models in Excel are a way of integrating data from multiple tables, enabling extensive analysis using PivotTables, Power Pivot, and Power View.
Power Query
Data power query is a data transformation and data preparation engine that comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations67. You can use data power query to import data from any number of sources, clean it, transform it, then reshape it according to your needs8.
To critically analyze how ChatGPT can help in the unique feature of pivot tables of excel for data power query, you would need to consider the following aspects:
- How well can ChatGPT understand natural language queries related to pivot tables and data power query?
- How well can ChatGPT generate accurate and relevant responses based on the available data sources and transformations?
- How well can ChatGPT handle complex or ambiguous queries that require multiple steps or clarifications?
- How well can ChatGPT present the results in a user-friendly and interactive way?
These are some possible questions you could ask yourself when evaluating ChatGPT’s performance for this task.
Analyzing Data
ChatGPT can help in the unique feature of analyzing data of Excel for data analysis in several ways:
- ChatGPT can generate Excel formulas based on natural language instructions or questions12. This can save time and effort for users who are not familiar with Excel syntax or functions. For example, if a user wants to calculate the mean of each store in a data set, they can simply ask ChatGPT for a formula that does that, and ChatGPT will provide one.
- ChatGPT can provide insights and summaries from data sets using natural language generation34. This can help users understand their data better and identify trends, patterns, outliers, or anomalies. For example, if a user wants to know how their sales performance compares to their competitors, they can ask ChatGPT for a summary of their data and ChatGPT will generate one.
- ChatGPT can create charts and graphs from data sets using natural language commands15. This can help users visualize their data and communicate their findings more effectively. For example, if a user wants to see how their revenue changes over time by region, they can ask ChatGPT for a chart that shows that and ChatGPT will create one.
Here are some real time examples of how ChatGPT can help in the unique feature of analyzing data of Excel for data analysis:
- ChatGPT can create charts and graphs from data sets using natural language commands. For example, if a user has a data set of monthly average temperatures for different cities, they can ask ChatGPT to create a line chart of the mean temperature over the month for each city. ChatGPT will then generate a code snippet that uses the plot() function in Excel to create the chart.
- ChatGPT can provide insights and summaries from data sets using natural language generation. For example, if a user has a data set of customer feedback for different products, they can ask ChatGPT to test different prompts and analyze the output to see how it can generate hypotheses, insights and suggestions. ChatGPT will then use natural language processing and machine learning to find patterns, correlations, outliers or anomalies in the data and provide relevant information.
- ChatGPT can generate Excel formulas based on natural language instructions or questions. For example, if a user has a data set of sales figures for different regions and products, they can ask ChatGPT to create a formula that will calculate the total sales for each region. ChatGPT will then generate a code snippet that uses the SUMIF() function in Excel to create the formula.
Scalability
ChatGPT can help scalability of excel application in data analysis by providing natural language queries that allow you to ask questions about your data without having to write complicated formulas. ChatGPT can also provide high-level visual summaries, trends, and patterns of your data using tables, charts or PivotTables.
Reproducibility
Feature reproducibility in Excel refers to the ability to reproduce the same results from a measurement process under different conditions. For example, if different operators measure the same parts using different equipment, they should obtain consistent results. Feature reproducibility can be assessed using Gage R&R (Reproducibility & Repeatability) analysis for attributes.
ChatGPT can help feature reproducibility in excel application for data analysis by providing natural language queries that allow you to set up and interpret a Gage R&R analysis for attributes in Excel using the XLSTAT software. ChatGPT can also provide high-level visual summaries, trends, and patterns of your data using tables, charts or PivotTables.
Accuracy
Feature accuracy in Excel refers to how close the experimental values are to the true values. It helps us to avoid significant data loss and minimize unnecessary errors that occur during calculation1. There are different methods to calculate accuracy in Excel, such as using IF function, ABS function, or MAX & MIN functions.
ChatGPT can help feature accuracy in excel application for data analysis by providing natural language queries that allow you to calculate accuracy percentage in Excel using different formulas or functions. ChatGPT can also provide high-level visual summaries, trends, and patterns of your data using tables, charts or PivotTables.
Flexibility
Feature flexibility in Excel refers to how easily you can adapt your data analysis to different scenarios, conditions, or preferences1. It helps you to handle data in meaningful ways, including reporting and presenting. There are different features that enhance flexibility in Excel, such as Power Query, PivotTables, What-If Analysis, Custom Lists, Fuzzy Matching, and more.
ChatGPT can help feature flexibility in excel application for data analysis by providing natural language queries that allow you to use different features to manipulate your data according to your needs. ChatGPT can also provide high-level visual summaries, trends, and patterns of your data using tables, charts or PivotTables.
Conclusion
Integrating ChatGPT into Excel can be a powerful tool for data analysis. By leveraging the capabilities of ChatGPT, users can extract insights from their data in a natural and conversational way, without the need for complex programming or SQL queries.
Using VBA code to integrate ChatGPT into Excel is a relatively straightforward process, and can be accomplished with the help of the VBA-JSON library to facilitate communication between Excel and the OpenAI API.
By incorporating ChatGPT into Excel, data analysts can streamline their workflow and make data analysis more accessible to non-technical stakeholders. This can result in faster and more accurate decision-making, as insights are surfaced more quickly and effectively.
Overall, the combination of Excel and ChatGPT can be a powerful tool for data analysis, and is definitely worth exploring for anyone looking to take their data analysis skills to the next level.
Thank you for reading! I would love to hear from you and will do my best to respond promptly. Thank you again for your time, and have a great day! If you have any questions or feedback, please let us know in the comments below or email me.
Subscribe, follow and become a fan to get regular updates.
