Introduction
Google Sheets is a powerful tool for organizing information, tracking expenses, managing projects, and analyzing data. However, creating formulas can sometimes be confusing, especially if you are unfamiliar with spreadsheet functions.
The good news is that you don’t have to memorize complicated formulas anymore. With the help of Artificial Intelligence (AI), you can generate Google Sheets formulas simply by describing what you want to accomplish.
Tools like ChatGPT can help you create formulas, explain how they work, fix errors, and improve your spreadsheets. Whether you’re managing a budget, tracking employee performance, or organizing business information, AI can make working with Google Sheets much easier.
In this guide, you’ll learn how to use AI to create Google Sheets formulas step by step.
1. Choose an AI Tool to Help You
Before creating formulas, you need an AI tool that can understand your instructions and generate spreadsheet formulas.
Some popular options include:
- ChatGPT: Helps generate, explain, and troubleshoot Google Sheets formulas.
- Google Gemini: Can assist with spreadsheet tasks and formula suggestions.
- Google Sheets’ built-in AI features: Depending on your account and available features, you may be able to use AI assistance directly within Google Sheets.
For beginners, ChatGPT is a simple place to start because you can describe your spreadsheet task using everyday language.
2. Describe What You Want Your Formula to Do
You don’t need to know the exact formula before asking AI for help. Simply explain what you want your spreadsheet to calculate.
For example, instead of searching for a SUM formula, you can ask:
“Create a Google Sheets formula that adds all the numbers from cells B2 to B20.”
ChatGPT might provide:
=SUM(B2:B20)
You can copy the formula and paste it into your Google Sheets document.
Tip: Be specific when describing your request. Include the column letters, row numbers, and the result you want.
3. Use AI to Create Common Google Sheets Formulas
Here are some useful formulas you can ask AI to generate.
A. Calculate the Total
If you want to add numbers from multiple cells, use the SUM function.
Example formula:
=SUM(B2:B10)
AI prompt:
“Create a Google Sheets formula that calculates the total of all values in cells B2 through B10.”
This is useful for calculating monthly expenses, sales totals, and business revenue.
B. Calculate the Average
If you want to find the average of a group of numbers, use the AVERAGE function.
Example formula:
=AVERAGE(C2:C20)
AI prompt:
“Write a Google Sheets formula that calculates the average of the numbers in column C, from row 2 to row 20.”
This can help you evaluate average sales, employee performance, or monthly spending.
C. Count Completed Tasks
If you’re managing a task tracker, you may want to count how many tasks have been completed.
Example formula:
=COUNTIF(D2:D50,"Completed")
AI prompt:
“Create a Google Sheets formula that counts how many times the word Completed appears in column D, from D2 to D50.”
This is especially useful for project management and employee task tracking.
D. Calculate Percentages
You can also ask AI to calculate percentages automatically.
Example formula:
=B2/C2
Format the resulting cell as a percentage.
AI prompt:
“Create a Google Sheets formula that calculates the percentage of completed tasks in B2 compared to the total tasks in C2.”
E. Identify Overdue Tasks
If you manage deadlines, AI can help you create formulas that automatically identify overdue tasks.
Example formula:
=IF(AND(C2<TODAY(),D2<>"Completed"),"Overdue","On Track")
This formula checks whether the deadline in C2 has passed and whether the task status in D2 is not Completed.
AI prompt:
“Create a Google Sheets formula that displays Overdue if the deadline in column C has passed and the task status in column D is not Completed. Otherwise, display On Track.”
4. Copy and Paste the Formula Into Google Sheets
Once AI generates your formula, the next step is to add it to your spreadsheet.
Follow these steps:
- Open your Google Sheets document.
- Select the cell where you want the result to appear.
- Copy the formula provided by ChatGPT.
- Paste the formula into the selected cell.
- Press Enter.
- Check whether the result is correct.
If the formula works, you can drag the small blue square at the bottom-right corner of the selected cell to apply it to other rows when appropriate.
Always check the results, especially when using formulas for important business or financial information.
5. Ask AI to Explain Complicated Formulas
Sometimes AI generates a formula that works, but you don’t understand what it does.
Instead of guessing, ask ChatGPT to explain it in simple language.
For example, you can enter:
“Explain this Google Sheets formula step by step: =IF(B2>=75,”Passed”,”Failed”).”
AI can explain that:
- IF checks whether a condition is true or false.
- B2>=75 checks whether the value in B2 is at least 75.
- “Passed” appears if the condition is true.
- “Failed” appears if the condition is false.
This is a great way to learn spreadsheet functions while completing your work.
6. Use AI to Fix Google Sheets Formula Errors
Not every formula works perfectly on the first attempt. You might encounter errors such as #VALUE!, #REF!, #NAME?, or #DIV/0!.
Instead of spending hours trying to fix the problem, you can ask AI for help.
For example, use this prompt:
“My Google Sheets formula is showing a #VALUE! error. Here is my formula: [paste your formula]. Please explain what is causing the error and provide a corrected version.”
AI can help identify possible problems, such as:
- Incorrect cell references.
- Missing quotation marks.
- Incorrect formula syntax.
- Numbers stored as text.
- Incorrect range selections.
- Division by zero.
Remember to test the corrected formula before using it throughout your spreadsheet.
7. Use AI to Automate Your Spreadsheet Tasks
AI can also help you create formulas that make everyday spreadsheet tasks easier.
For example, you can ask ChatGPT to:
- Automatically calculate employee commissions.
- Track monthly income and expenses.
- Identify duplicate entries.
- Calculate project completion rates.
- Highlight overdue deadlines.
- Categorize expenses.
- Calculate sales performance.
- Track inventory levels.
Example AI prompt:
“Create a Google Sheets formula that calculates a 10% commission based on the sales amount in cell B2. If the sales amount is empty, leave the result blank.”
Possible formula:
=IF(B2="","",B2*10%)
This formula automatically calculates commission while keeping empty rows clean.
8. Tips for Getting Better Results From AI
To get accurate formulas, make sure your instructions are clear and specific.
Here are some helpful tips:
1. Provide your spreadsheet layout. Tell AI which columns contain dates, amounts, names, or task statuses.
2. Explain the expected result. Describe exactly what you want the formula to display.
3. Share an example. Providing sample data helps AI understand your requirements.
4. Ask for a Google Sheets formula. Some formulas work differently in Excel and Google Sheets.
5. Request an explanation. Ask AI to explain the formula so you understand how it works.
6. Test before applying. Always check the formula using sample data before applying it to your entire spreadsheet.
Conclusion
Learning how to use AI to create Google Sheets formulas can save time and make spreadsheet management easier. You don’t need to be an expert in Excel or Google Sheets to perform calculations, organize information, or automate repetitive tasks.
By using tools like ChatGPT, you can describe what you need, generate formulas, troubleshoot errors, and learn new spreadsheet functions along the way.
Start with simple formulas like SUM, AVERAGE, and COUNTIF, then gradually explore more advanced functions as you become comfortable.
Remember: AI is a helpful assistant, but always review and test its formulas to make sure your spreadsheet calculations are accurate.
