Integrating ChatGPT into MS Excel
Popularity of ChatGPT is growing day by day, with a variety of use cases ranging from healthcare to banking domain. OpenAI recently released a public API, making it easier to embed ChatGPT in any web application or software. Benefits of using ChatGPT in Excel include insight generation, automation, easy editing, error detection and troubleshooting, formatting capability, and structured output.
The first step to accessing the ChatGPT API is to get an API key from platform.openai.com, and to use a free $5 grant (which will expire after 3 months) to test the ChatGPT API. Three methods to use ChatGPT in Excel include an Excel plugin for ChatGPT, VBA code, and an Excel macro. Installing the plugin requires downloading and unblocking the file in the properties. The plugin provides a tab in the Excel workbook, allowing users to enter text to ask ChatGPT, enter the API key, select the model type, and select the cell in which text was entered. Output will be generated and appear within a few seconds.
Excel ChatGPT Add-in – Functions
The main benefit of the excel add-in is that it is not dependent on one file like a macro workbook, and can be easily shared between different workbooks and users. This add-in has four functions which can be used for getting responses from ChatGPT:
- AIAssistant(text, [word_count]): Generates and provides the output of the search query using ChatGPT
- AIAssistant_Chat(text, [reset]): Converse like ChatGPT website remembers prior conversations
- AIAssistant_FillData(rng_existingdata, rng_fill): Fill incomplete data by training ChatGPT on existing data
- AIAssistant_Extractor(prompt, keyword): Extract key data from ChatGPT. Key Data can be Name, Place, Organization details etc.
- AIAssistant_Explain(cell_formula, [detail]): Help explain Excel Formula which you don’t understand and need support.
By default, when API requests are sent to OpenAI, it does not recall prior conversations. To improve ChatGPT’s ability to keep prior conversations, the function AIAssistant_Chat(text, [reset]) can be utilized. Set the second argument, reset, as TRUE to start/reset chat.
The uses of AIAssistant_FillData( ) are numerous. Examples include sentiment analysis (to label positive/negative/neutral sentiments on customer feedback data), industry/sector classification (to find out companies’ industries/sectors), and text patterns extraction (to extract numeric values from text data).
Method II – Excel Macro for ChatGPT
The VBA code below fetches responses from ChatGPT using API and puts it into Excel. It also cleans the response from ChatGPT and puts it in a structured format so that formatting is maintained and the response can be easily edited and reused in any other document or spreadsheet. Please note that this macro only works on Windows OS.
In order to use the ChatGPT, the API key must be entered in cell F3. Additionally, ChatGPT can be run as an Excel function. The syntax of the function is “text – prompt. Question you want to ask. word_count – optional. Maximum number of words in the response that will be generated.” An example of this is extracting the capital city of different countries, with the maximum number of words being specified in the second argument.
To find out other ways ChatGPT can interact with spreadsheets, check out our free automated tools which run via Google Sheets!
comments: 0
