[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
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.<\/span><\/p>\n 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.<\/span><\/p>\n 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.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”why” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Writing formulas is one of the more tedious spreadsheet tasks. As such, leveraging AI to generate formulas provides several benefits:<\/span><\/p>\n 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.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”guide” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n First, sign up for an AI assistant. In this article, we’ll be using these AI tools to help us generate formulas: <\/span>ChatGPT<\/span><\/a>, <\/span>Google Bard<\/span><\/a>, and <\/span>Claude<\/span><\/a>.<\/span><\/p>\n Next, provide context about your spreadsheet data so the AI understands your goals. Explain what’s in each column of your sheet.<\/span><\/p>\n 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.<\/span><\/p>\n 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.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”tips” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Follow these tips when using AI to create formulas:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”use” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here are some common examples of formulas you can have AI write automatically:<\/span><\/p>\n The possibilities are endless when leveraging AI to produce formulas. Any operation you can normally do manually can be automated.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”examples” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 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.<\/span><\/p>\n 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.<\/span><\/p>\n Write a Google Sheets formula to calculate the average of a range of cells, from B22 to M22.<\/strong><\/p>\n Here are the answers:<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n ChatGPT:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/10\/How-to-generate-Google-Sheets-formulas-with-AI-1.png” alt=”How to generate Google Sheets formulas with AI 1″ title_text=”How to generate Google Sheets formulas with AI 1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Claude:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/10\/How-to-generate-Google-Sheets-formulas-with-AI-2.png” alt=”How to generate Google Sheets formulas with AI 2″ title_text=”How to generate Google Sheets formulas with AI 2″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Google Bard:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/10\/How-to-generate-Google-Sheets-formulas-with-AI-3.png” alt=”How to generate Google Sheets formulas with AI 3″ title_text=”How to generate Google Sheets formulas with AI 3″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 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.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 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.<\/span><\/span><\/p>\n 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.<\/b><\/p>\n <\/span>[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n ChatGPT:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/10\/How-to-generate-Google-Sheets-formulas-with-AI-4.png” alt=”How to generate Google Sheets formulas with AI 4″ title_text=”How to generate Google Sheets formulas with AI 4″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Claude:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/10\/How-to-generate-Google-Sheets-formulas-with-AI-5.png” alt=”How to generate Google Sheets formulas with AI 5″ title_text=”How to generate Google Sheets formulas with AI 5″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Google Bard:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/10\/How-to-generate-Google-Sheets-formulas-with-AI-6.png” alt=”How to generate Google Sheets formulas with AI 3″ title_text=”How to generate Google Sheets formulas with AI 6″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now let’s put these formulas to the test.<\/span><\/p>\n These are the formulas created by ChatGPT.<\/span><\/p>\n =ARRAYFORMULA((B22:M22\/B6:M6)*100)<\/b><\/p>\n =MAX((B22:M22\/B6:M6)*100)<\/b><\/p>\n =MIN((B22:M22\/B6:M6)*100)<\/b><\/p>\n 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.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/10\/How-to-generate-Google-Sheets-formulas-with-AI-7-1.png” alt=”How to generate Google Sheets formulas with AI 7″ title_text=”How to generate Google Sheets formulas with AI 7″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 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.<\/span><\/p>\n =ArrayFormula(IF(LEN(B6:M6), B22:M22\/B6:M6, ))\u00a0<\/b><\/p>\n =MAX(B26:M26)<\/b><\/p>\n =MIN(B26:M26)<\/b><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/10\/How-to-generate-Google-Sheets-formulas-with-AI-8-1.png” alt=”How to generate Google Sheets formulas with AI 8″ title_text=”How to generate Google Sheets formulas with AI 8″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 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.\u00a0<\/span><\/p>\n 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%).<\/span><\/p>\n =(B22-B6)\/B6*100<\/b><\/p>\n =MAX(B23:M23)<\/b><\/p>\n =MIN(B23:M23)<\/b><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/10\/How-to-generate-Google-Sheets-formulas-with-AI-9-1.png” alt=”How to generate Google Sheets formulas with AI 9″ title_text=”How to generate Google Sheets formulas with AI 9″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”conclusion” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n 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.<\/span><\/p>\nWhy use AI to Generate Google Sheets formulas?<\/span><\/h2>\n
\n
Step-by-step guide to generating Google Sheets formulas with AI<\/span><\/h2>\n
Tips for the best results with AI-generated formulas<\/span><\/h2>\n
\n
Common use cases for AI-generated Google Sheets formulas<\/span><\/h2>\n
\n
Examples of AI-generated formulas<\/span><\/h2>\n
Creating a more complex formula<\/h3>\n
Conclusion<\/span><\/h2>\n