How to generate Google Sheets formulas with AI

If you’re looking for ideas on how to generate Google Sheets formulas with AI, look no further. In this article, you’ll learn how AI assistants can help you write Google Sheets formulas for you in minutes.

Spreadsheets are powerful, but writing formulas can be time consuming and error prone. Fortunately, advances in artificial intelligence now allow anyone to generate Google Sheets formulas with plain English prompts. With the ability to describe what you want in natural language, AI makes using advanced spreadsheet formulas simple and easy.

Check out the step-by-step instructions to have AI write formulas for you, with some tips to get the best results, and common use cases that are perfect for automating with AI.

Why use AI to Generate Google Sheets formulas?

Writing formulas is one of the more tedious spreadsheet tasks. As such, leveraging AI to generate formulas provides several benefits:

  • It saves a tremendous amount of time compared to manually determining and entering the proper formula syntax. Rather than digging through documentation, you can describe the logic in plain English and instantly get working formulas.
  • AI allows those less skilled with spreadsheets to take advantage of formulas. If you understand what needs to be calculated but don’t know how to write the formula, AI can bridge that gap.
  • Formulas generated by AI are less prone to human error. AI won’t make careless syntax mistakes that break your formulas or reference the wrong cells.

For these reasons, using AI to generate formulas leads to greater productivity and can reduce frustration. Even spreadsheet experts can benefit from the automation and accuracy AI provides.

Step-by-step guide to generating Google Sheets formulas with AI

First, sign up for an AI assistant. In this article, we’ll be using these AI tools to help us generate formulas: ChatGPT, Google Bard, and Claude.

Next, provide context about your spreadsheet data so the AI understands your goals. Explain what’s in each column of your sheet.

Then, type a plain English description of the formula you want to generate, using simple logic like “Sum of the numbers in Column A”. The AI will suggest the proper Google Sheets formula syntax to match your description.

Finally, insert the AI-generated formula into your spreadsheet where needed. The AI handles the hard work of determining the correct formulas and translating your natural language prompts into spreadsheet syntax.

Tips for the best results with AI-generated formulas

Follow these tips when using AI to create formulas:

  • Provide plenty of clear context about your spreadsheet contents so the AI can interpret your goals accurately.
  • Use simple, non-technical language to describe what you want – like calculating sums or averages across a range.
  • If the initial formula doesn’t match your needs, refine your prompt and let the AI make adjustments.
  • Double check that the suggested formula references the correct cells before inserting.

Common use cases for AI-generated Google Sheets formulas

Here are some common examples of formulas you can have AI write automatically:

  • Use SUM to quickly total numbers in a column.
  • Generate AVERAGE formulas to calculate means across cell ranges.
  • Create COUNT formulas to get the number of cells containing data.
  • Use VLOOKUP and HLOOKUP to retrieve data from other sheets.
  • Build nested IF statements without worrying about syntax.
  • Concatenate text strings together with the CONCAT function.

The possibilities are endless when leveraging AI to produce formulas. Any operation you can normally do manually can be automated.

Examples of AI-generated formulas

Now let’s take a look at some examples of AI-generated formulas. We’ll ask ChatGPT, Claude and Bard to generate some formulas for an income statement spreadsheet.

First, we’ll use this prompt to ask these AI tools to generate a very simple formula to calculate the average of a range of cells.

Write a Google Sheets formula to calculate the average of a range of cells, from B22 to M22.

Here are the answers:

ChatGPT:

How to generate Google Sheets formulas with AI 1

Claude:

How to generate Google Sheets formulas with AI 2

Google Bard:

How to generate Google Sheets formulas with AI 3

As you can see, they have all created the correct formula. In addition, Claude and Google Bard have also provided a brief explanation of the formula syntax.

Creating a more complex formula

Now we’ll ask these 3 AI tools to write a more complex Google Sheets formula. We’ll use the following prompt to ask them to create a formula to calculate the profit margin percentage of each month and identify the maximum and minimum margins.

Create a Google Sheets formula to determine both the maximum and minimum profit margins. Begin by calculating the profit margin percentage of each month. Total revenue for each month is listed from cells B6 to M6, while net profit is recorded from cells B22 to M22. Note that Column B corresponds to January, Column C to February, and so on. Once calculated, identify the maximum and minimum profit margins.

ChatGPT:

How to generate Google Sheets formulas with AI 4

Claude:

How to generate Google Sheets formulas with AI 5

Google Bard:

How to generate Google Sheets formulas with AI 3

Now let’s put these formulas to the test.

These are the formulas created by ChatGPT.

=ARRAYFORMULA((B22:M22/B6:M6)*100)

=MAX((B22:M22/B6:M6)*100)

=MIN((B22:M22/B6:M6)*100)

After adding them to the spreadsheet, we can notice that it has calculated the profit margin percentage of each month. However, the MAX formula is inaccurate because it only returns the value calculated for each month.

How to generate Google Sheets formulas with AI 7

In comparison, Claude has not only created an ARRAY formula that calculates the profit margin accurately, but it has also generated MAX and MIN formulas to identify the maximum and minimum values.

=ArrayFormula(IF(LEN(B6:M6), B22:M22/B6:M6, )) 

=MAX(B26:M26)

=MIN(B26:M26)

How to generate Google Sheets formulas with AI 8

In contrast, Bard got it backwards. It has generated a formula to calculate only the profit margin of the first month. We had to apply the formula to the entire range by dragging it. 

However, there was another problem with the formula it generated. Instead of calculating the profit margin (43.29%), it calculated the difference between the revenue and the net profit (56,71%).

=(B22-B6)/B6*100

=MAX(B23:M23)

=MIN(B23:M23)

How to generate Google Sheets formulas with AI 9

Conclusion

Although AI tools can be a good spreadsheet assistant and help you save time, it’s important to take AI with a grain of salt. Use different AI tools, compare the answers and always check the formulas.

If you want to read more about the use of AI, check out this article about ChatGPT prompts for spreadsheet users.

You may also like…