How to use ChatGPT with Excel?

Let's be honest; not everyone loves formulas, even if it's in an Excel spreadsheet. 

If you are someone who needs to use Excel daily and wants to know more about how you can easily write Excel formulas via ChatGPT, this one’s for you.

In this article, we will give you a step-by-step guide on how to use a powerful tool like ChatGPT to write Excel formulas, the drawbacks of Excel, and problems with current ChatGPT add-ons for Excel.

How to Use ChatGPT to Write Excel Formulas in 2023

Before we dive in further, make sure you have:

• An OpenAI account

• Access to MS Excel (desktop or online version)

• A dataset to work with formulas

In our example, we'll create a sample dataset on Excel and then show you how to use ChatGPT for Excel formulas.

Step 1: Create a Dataset on Excel 

Open Excel and choose the dataset you want to add formulas to. For example, we have created a sample dataset citing a business's monetary and fiscal changes over 10 years.

Our motive here is to calculate Gross Profit and Net Profit using formulas generated by ChatGPT.

Step 2: Open ChatGPT and Copy the Dataset

Go to Open AI's ChatGPT website and log in to your account. You'll see a New Chat Screen after login.

Now copy and paste the entire dataset into the text box at the bottom. Once done, press 'Shift + Enter' to change lines and write one of the below commands:

"Please use this data for the next commands."

or

"Please look for this data as a reference for the next instructions."

You may ask why?

If we simply copy-paste the data into ChatGPT, it will be considered the final input, and the AI chatbot will generate insights regarding the same. We tried copy-pasting the data without instructions, and it did this 👇

So, to counter this issue, we added a simple command. And bravo! It worked smoothly.

Step 3: Enter Your Command

Next, we'll use ChatGPT to calculate Gross Profit for our dataset. Remember, we must be precise and specific in telling the AI chatbot what we want to calculate. 

ChatGPT Prompt for Gross Profit Calculation: Write an Excel formula to calculate Gross Profit. E is the Gross Profit column, and I want to calculate it for the E2 cell first.

Note: E2 is the first cell of the Gross Profit column.

The AI chatbot not only generates an Excel formula for the asked calculation, but also explains the working of the formula and how to use it in Excel efficiently.

Step 4: Copy-Paste the Formula to Excel

Your next step is to copy the code from ChatGPT and put it in Excel. To do so, simply click on the Copy Code instruction that you see on the top right corner.

And then paste it into the Excel cell 'E2.' The cell will directly show you the value when you paste it in.

To see the formula, double-click on the E2 cell.

Step 5: Fill the Entire Column at Once

We know it would be tedious to change the formula for every cell. 

So for that, simply hover over the cell (in this case E2), then when you see a black Plus sign icon appear at the bottom right corner of the cell, long press and drag the cursor to select columns cells until the last row of data.

As you release the cursor, the selected row cells automatically get filled with correct answers. 

Next, we'll calculate the Net Profit. 

ChatGPT Prompt to Calculate Net Profit: Write an Excel formula to calculate Net Profit. F is the Net Profit column, and I want to calculate it for the F2 cell first.

From here, we'll follow the same process for calculating net profit. Copy and paste the formula in F2, drag the cursor to the last row of data, and it's done.

Step 6: Complete the Table

We were curious if the AI chatbot could help us write a complex formula in Excel, like calculating net profit margin as a percentage. So, we took a shot!

Spoiler Alert: ChatGPT was able to do it! 😉

We reopened our chat screen and requested ChatGPT to calculate the net profit margin from the above data as a percentage. 

In this case, we tried to clarify that we wanted the output in a percentage format.

ChatGPT Prompt: Write an Excel formula to calculate Net Profit Margin in percentage. G is the Net Profit Margin column, and I want to calculate it for the G2 cell first.

The AI chatbot provided us with the exact formula and also explained to us it's working.

We copy-pasted the formula and dragged it into cells G3 through G11, and Voila! Our Excel table was complete without even knowing a single formula.

Drawbacks of Using ChatGPT with Excel

• Limited Integration: ChatGPT and Excel are poles apart; while one uses natural language processing, the latter works on structures and formulas. Achieving a complete integration between the two would require some level of technical and coding expertise. Until then, you will have to do some things manually.

• Inability to Perform Advanced Calculations: We learned this while preparing the dataset for this article. Certainly, the AI chatbot is trained for text automation, but it struggles when put into doing complex math calculations. As a result, ChatGPT seldom provides you with incorrect results.

• Lacks Compatibility & Security: Excel is widely used in the corporate space but under strict security protocols. Integrating a third-party solution like ChatGPT with confidential numbers and files can be against company policies or lead to data breaches. 

• Difficulty with Structured Data: ChatGPT is trained on natural language processing models. You might find it challenging to get answers of structured data in Excel if it is not presented in a format it can understand. 

• Lacks Flexibility: The AI solution isn't perfect; it requires you to write precise and specific prompts to drive desired results. While using ChatGPT with Excel, you would sometimes get incorrect results. And for most, you can just rephrase prompts.

💡Industry Insight: You can now opt for a ChatGPT plus version, where your data is not used to train the ChatGPT model, taking care of the security point we mentioned above.

Problem with Current Add-Ons Integrating ChatGPT with Excel

As of today, there are no natively built-in add-ons in the market that integrate ChatGPT with Excel. While third-party add-ons are present in abundance, users always encounter problems, such as:

• Double Manual Work: Being an AI, users expect ChatGPT integration with Excel to be fully automated. However, without a native integration, users have to rely on third-party add-ons or manually copy and paste formulas generated by ChatGPT into Excel. This can be time-consuming and cumbersome, reducing overall efficiency of the process.

• Limited Support: Since ChatGPT has no built-in integration with Excel, users have no option but to rely on third-party resources for troubleshooting. The resources, however, are limited in use cases and don't help you utilize the full potential of ChatGPT.

• Compatibility Issues: ChatGPT doesn't function in real-time data. It has a cut-off date of September 2021. This means that the chatbot does not have information about more recent Excel features or functions added by Microsoft. Therefore, even with a third-party add-on, you may often encounter unexpected errors or reduced functionalities.

• Security Concerns: Relying on a third-party integration may pose a threat to security. Firstly, we're unsure if these add-ons collect user data. Secondly, the add-ons themselves could become a target of cyberattacks. Lastly, most companies feel unsafe integrating third-party add-ons due to data breach risks.

• High Learning Curve: Users from non-tech backgrounds may find it difficult to set up and use ChatGPT and Excel simultaneously using add-ons. You might need coding knowledge frequently to set and use these add-ons with ChatGPT.

While we reviewed some of the add-ons, we found that none were good enough to help you utilize this language model to the fullest. Add-one in the market lets you simply "copy-paste" suggestions from ChatGPT into Excel and do nothing more.

Some do have several drawbacks, as we discussed above. So what's the solution for this? Is there a better add-on that you can use to transform your Excel use and make the process much more efficient? Do it with Rows 👇

Integrate Rows with OpenAI and ChatGPT inside a spreadsheet

Rows is the easiest way to use data on a spreadsheet: from importing data directly from tools like databases and ads platforms to transforming and sharing it beautifully and error-proof. Among the 50+ integrations that are natively built in Rows, OpenAI plays a key role in strengthening Rows capabilities in handling and transforming data.

Connecting the OpenAI integration in Rows

You can find the OpenAI integration by browsing the integrations gallery or the Actions wizard and searching for "OpenAI".

To connect the integration, all you need is your OpenAI API Key. You can get your API key by going to the API Keys option on your OpenAI account. If you don't have an account yet, sign-up here. All free accounts have API access.

The API Key panel on the OpenAI dashboard

Now simply copy the API key, go back to the OpenAI integration page, press Connect, paste it and click Connect. Your Rows workspace is now connected to your OpenAI account and you're ready to go.

The OpenAI integration page

Now you can start using the power of ChatGPT directly inside the spreadsheet, and use specific AI formulas to automate a lot of your work.

Using the OpenAI functions

The OpenAI integration comes with five functions that are designed to leverage generative AI to address specific needs: 

• ASK_OPENAI(), which aims at leveraging the power of GPT to solve general tasks. Similar to how you use ChatGPT, you can use this function to ask OpenAI any question.

• CREATE_LIST_OPENAI(), which is designed specifically to create tables and list of dummy data, for testing purposes.

• CLASSIFY_OPENAI(), which is designed specifically to classify texts into a given set of tags.

• TRANSLATE_OPENAI(), which translates texts from/into a wide range of languages.

• APPLY_TASK_OPENAI(), which is designed specifically to clean up or apply logic rules to data.

You can use them via the Autocomplete in the editor, 

=ASK_OPENAI on the editor

or via the Actions wizard:

List of OpenAI actions inside Rows

There are many use cases of ChatGPT in Rows, such as:

• Clean Up Data: Capitalize text, remove unnecessary text, parse email domains, and more. Use APPLY_TASK_OPENAI() to easily perform this action, specifying the task i the prompt

• Text Classification: Tag emails, classify emails or classify roles from LinkedIn profiles. Use CLASSIFY_OPENAI() to easily perform this action, specifying the categories you want your text to fit in as a second argument.

• Sentiment Analysis: This is a subset of Text Classification to identify and extract subjective information from text. It can be used to determine the overall sentiment of a piece of text, as well as to identify specific opinions and emotions within the text. Discover more here

• Translation: Translation into other languages with our TRANSLATE_OPENAI() function.

• Summarization: Condense news articles, messages, legal documents, research papers, and more. Use APPLY_TASK_OPENAI() to perform this action, with the following prompt, e.g. "Summarize in 3 bullet points".

• Create Tables with Data: Create tables with dummy or public data.

• Find and Enrich Data: Generate dummy data or public data points for things you already have on your spreadsheet, like countries and companies. For both this and the above task, use CREATE_LIST_OPENAI() through our Actions wizard: specify the content you want to generate in the first argument and the number of items in the list as the second one.

Using the ASK_OPENAI formula inside Rows

Discover these use cases of ChatGPT for growing your business and streamlining your marketing tasks. Get started with 10 pre-built examples in our template of ChatGPT in Rows.