How to Integrate ChatGPT into Excel

How to Install the Add-in

When you download an add-in or macro file from the internet, Microsoft blocks its execution and shows that the source of this file is not trusted. You must follow the steps below to make it work.

1. Go to the folder where the downloaded Add-in file is located.
2. Right-click on the file and choose Properties from the menu.
3. At the bottom of the General tab, check the Unblock checkbox under the Security option, then click OK.

Refer to the following steps to install the ChatGPT add-in in MS Excel.

1. Open Excel and click the File tab on the ribbon.
2. Click Options and then select Add-ins from the left-hand menu.
3. In the Manage dropdown menu at the bottom of the screen, select Excel Add-ins and click the Go button.
4. Click the Browse button and locate your downloaded add-in file.
5. Select the add-in file and click the OK button.
6. You should see the add-in file name in the Add-Ins dialog box. Check the box next to the add-in name to enable it.
7. Once you’ve completed the above steps, a new tab named ChatGPT should be visible in your Excel workbook.

How to use ChatGPT Excel Add-in

Follow the steps below to use the ChatGPT add-in for MS Excel.

1. Open a new or existing MS Excel workbook
2. Enter the text you want to ask ChatGPT in any cell
3. Click the ChatGPT tab > AI Assistant
4. Enter your API key and select Model Type
5. Select the cell where you entered the text in step 2
6. The output will be generated and appear within seconds.
7. When you want ChatGPT to run on multiple cells, you can use the AIAssistant function. See details in the next section.

ChatGPT Excel Add-in: Functions

The main advantage of the Excel add-in is that it doesn’t depend on a file like a macro workbook and can be easily shared across different workbooks and users. This add-in has the following seven functions that you can use to get responses from ChatGPT and put them into Excel.

1. AIAssistant(text, [word_count]): Generates and provides the output for the search query using ChatGPT.
   – text: The text you want to search for.
   – word_count: Optional. Specify the maximum word count for the output generated by ChatGPT.
2. AIAssistant_Chat(text, [reset]): The website Chat like ChatGPT remembers previous conversations.
   – text: The text you want to search for.
   – reset: Optional. Start/Reset the chat session.
3. AIAssistant_FillData(rng_existingdata, rng_fill): Fills in incomplete data by training ChatGPT on existing data.
   – rng_existingdata: Training data range.
   – rng_fill: Specify the cell that needs to be filled.
4. AIAssistant_Extractor(prompt, keyword): Extracts key data from ChatGPT. Key data can be name, location, organization details, etc.
   – prompt: Specify the cell containing the text from which you want to extract key data.
   – keyword: The keyword can be name, location, organization, etc.
5. AIAssistant_Translator(text, language): Translates text from one language to another.
   – text: Specify the cell containing the text you want to translate.
   – language: Name of the language. E.g., Spanish, French, etc.
6. AIAssistant_Explain(cell_formula, [detail]): Helps you explain an Excel formula you don’t understand and need support for.
   – cell_formula: Cell containing an Excel formula you want ChatGPT to explain.
   – detail: Optional. Defaults to TRUE. Set to FALSE if you want a less detailed response.
7. AIAssistant_Insights(rng_data, [prompt]): Generates insights on data from the provided dataset.
   – rng_data: Specify the cells containing data (including the header).
   – prompt: Optional. Provide specific information you want about the data.

To type functions faster, type =ai first, then use the UP or DOWN arrow key to select the desired function, and finally press the Tab key.

Search with ChatGPT

The AIAssistant function uses ChatGPT to search for information and place the results into Excel. See the example below.

=AIAssistant("First President of United States")

If you want to limit ChatGPT’s response to a maximum of 2 words, specify “2” as the second argument to the function. For example, =AIAssistant("First President of US", 2).

Recall ChatGPT Conversations

By default, when we send API requests to OpenAI, it does not recall previous conversations. Consider every API request as a new chat. This means when it answers your current question, it doesn’t remember your previous query.

To enhance ChatGPT’s ability to remember previous conversations, we can use the AIAssistant_Chat(text, [reset]) function. Set the second argument reset to TRUE to start/reset the chat.

1. Note: Both these functions AIAssistant() and AIAssistant_Chat() generate output for the search query. However, AIAssistant_Chat() also allows ChatGPT to remember your previous conversations, which increases token usage and costs more. It is recommended to set the second argument of the function reset as TRUE when you don’t need ChatGPT to recall previous conversations.

Data Insights

Follow the steps below to generate data insights or find hidden patterns in your data with this plugin.

1. Click the Generate Insights button on the ChatGPT tab in the Excel ribbon.
2. In the message dialog box, select the cells containing the data (including the header) and then click “OK”. For example, A1:D101.
3. Wait for the insights generated by the ChatGPT API.
4. In the message dialog box, select a cell where the output will be saved. For example, F2.
5. Select “yes” or “no” whether you want the output to be populated on multiple rows or a single row.

You can also analyze data using the AIAssistant_Insights(rng_data, [prompt]) function. The second argument of the function is optional, and by default, it extracts key information from the data if you don’t specify anything in the second argument. See the syntax for the AIAssistant_Insights() function below.

=AIAssistant_Insights(A1:D101,"Generate top 5 insights")

Translate with ChatGPT

If you are looking to translate text using the Excel plugin, you should try the AIAssistant_Translator function. It uses ChatGPT to help you with accurate translations. Simply input the text you want to translate and the name of the language, and it will leverage ChatGPT’s language skills to provide you with the translations.

=AIAssistant_Translator("Hello, how are you?", "German")

Extract Key Information

The AIAssistant_Extractor function can be used to retrieve key information like name, location, etc. from text. For example, suppose you have the text “Dave went to New York for studies” in cell B2. You want to extract the name and location from the text. Enter the following formulas in cells C2 and D2.

=AIAssistant_Extractor(B2,"name")
=AIAssistant_Extractor(B2,"location")

Fill Missing Data

The AIAssistant_FillData function can be used for several purposes. See some of the real-world use cases below.

1. Sentiment Analysis You can label positive/negative/neutral sentiments on customer feedback data. You just need to provide a few labels in the first argument of the function.
2. Industry/Sector Classification You have a company name and want to find out their industries/sectors.
3. Text Pattern Extraction There is text data that contains both characters and numerical values. Using this function you can extract numerical values from text.

Learn Excel Formulas

The AIAssistant_Explain function helps you understand any Excel formula. For example, suppose you have a formula in cell B2: =VLOOKUP(B2,$B$2:$D$5,2,FALSE). If you enter this formula =AIAssistant_Explain(B2) in cell C2, it will provide an explanation for the VLOOKUP function.

Handling Non-English Characters

Sometimes non-English characters don’t display correctly in the response coming from the ChatGPT API. This issue is related to UTF-8 encoding. To fix this, you can use the CleanMsg(ChatGPT_response) function. Refer to the example below.

=CleanMsg(AIAssistant("Write an Instagram post about 'Good Morning' in Spanish"))

If you are satisfied with the add-in above, you don’t need to proceed to the other methods below to integrate ChatGPT into Excel. The methods below are just alternatives to using the add-in, but they are less effective and involve manual steps.