Google Gemini can help you create content and calculations for Google Sheets. Gemini can serve as a smart list generator that produces content in a table for you to export to a Google Sheet, or its responses can help you devise formulas and functions. These uses of Gemini offer capabilities that extend beyond the standard autofill and correction capabilities of Sheets.
SEE: ChatGPT vs Google Gemini: An in-depth comparison (TechRepublic)
For the actions below, you must be signed in to an active Google account authorized to use both Google Sheets and Google Gemini. If you use a Google Workspace account for work or school, you may need to contact an administrator to request access to Gemini.
Once you have access, open Gemini in any modern browser to get started. Enter a natural language prompt, and Gemini will respond. Responses may differ — two people who enter the same prompt may not receive the same response.
How to obtain content from Gemini for a Google Sheet
With Gemini, you can prompt the system for various comparisons and lists. For example, a request to compare two or more products might produce a table with a column for each product and each row with a different comparison feature. You might also prompt Gemini for lists of people, places, or things. Since Gemini accepts sequences, you might first prompt:
List the 10 cities in the United States with the most annual rainfall.
Then, after you receive the initial response, prompt again with:
Add a column for the population.
The tables Gemini can generate as a response vary much more than conventional autocomplete sequences available in Google Sheets with the Tools | Autocomplete | Enable Autocomplete Option enabled.
In Google Sheets, you enter a predictable series of alphabetical letters, numbers, days of the week, months, or other standard patterns in two or more cells. Then select the cells and select-and-extend the corner of the box to cover the entire range of to-be-filled cells. For example, if you type Mon in one cell and Tue in an adjacent cell, you may select those two cells and then drag the dot to fill another five cells with the remaining respective three-character descriptions of days of the week in English.
In contrast, the tables you can prompt Gemini to create may comprise a much wider range of listable data.
SEE: Google Gemini Cheat Sheet: What Is Google Gemini, and How Does It Work?
Choose Export To Sheets
When a response includes a table, select the Export To Sheets option at the lower-right corner of the table. This exports the table to a new Google Sheet. The system will use your prompt as both the name of the newly created file and the initial sheet within the file. The table’s contents will be placed in the cells of the sheet, with the titles of the columns in Row A.
Prompt “in a table”
Gemini will often automatically format comparisons and lists in a table. When the system doesn’t do this, add the phrase “in a table” to your request. This would make the prompt listed above to be:
In a table, list the 20 cities in the United States with the most annual rainfall.
View other drafts or reset the chat
Sometimes Gemini provides either a partial response or an oddly formatted response. For example, try the prompt:
List all 50 U.S. states sorted by population.
Gemini returned a table of 40 states, which is an incomplete response. In another example, a prompt for a:
List of elements by name and atomic weight.
In response, Gemini produced a list formatted as a code snippet, not a table. When a response isn’t what you expect, select the View Other Drafts button to access alternative drafts. Sometimes, one of those drafts will be formatted as a table rather than a code snippet.
Another option when this sort of error occurs is to start a new chat and then try again with a rephrased prompt. This may return a more complete or better-formatted response.
SEE: How to Use Google Gemini: A Comprehensive Guide
How to get help from Gemini with a Google Sheets calculation
You may prompt Gemini to explain and provide examples of Google Sheets formulas and functions. Unlike Google Sheets help pages, which provide details and a static set of examples, you may ask Gemini for various examples and a detailed description of how a feature works.
For example, if you want to learn a bit more about one of the new functions Google added to Sheets in March 2023, you might prompt:
How do I use the WRAPCOLS function in a Google Sheet? Can you give an example of how I might use it to group a list of employee names into groups of 4 people each?
The response from Gemini included, in sequence:
- A description of how the function is used.
- A sample formula to group a list into a set of four.
- An example with demo names.
- Additional tips, such as how to handle extra rows or combine WRAPCOLS with other functions.
To explore additional Google Sheets functions, you might then prompt:
Are there other Google Sheets functions that achieve something similar?
Gemini tends to offer a few other functions that help with related aspects of your initial task.
In addition, Gemini can help you create the exact formula you need, but it may be a bit of a process of trial and error. For example, say you want to analyze weather data to identify how many days the wind was predominantly from the west. First, try an initial detailed prompt.
I have a Google Sheet with data in cells F2 through F367. The data is all numbers, from 0 to 359, and represents wind direction, with 0 being the north and 270 being the west. I would like a formula to indicate the percentage of days that the wind is from the west, where the value is anywhere between 240 and 270 degrees. Can you provide that?
The response returned an =COUNTIF formula that threw an error. After a quick review of the function, prompt again, essentially asking Gemini to try again:
I think the range indicator portion needs to be different. Maybe a logical AND not a text field?
This time, the response included a =COUNTIFS formula. Select the Copy Code button, switch to the Google Sheet, navigate to the intended destination cell, and then choose Edit | Paste to add the formula. This time, with some tweaking, the code snippet worked as desired.
And that’s a good snapshot of how you need to work with Gemini. If the initial response meets your needs, that’s great! But always take the time to check the results for accuracy and be prepared to prompt again — and prompt differently — to evoke a more relevant, useful, or accurate response
Can Gemini create tables in Google Sheets?
Yes! As described above, if the Gemini app creates a table, it is accompanied by an Export to Sheets button that opens the table in a spreadsheet.
Alternatively, while in Google Sheets, you can click the ‘Ask Gemini’ button in the top right of the screen, which appears as a white flash in a blue circle. A panel will then open with some suggestion prompts, including a ‘Create a table’ button. After clicking this, you can replace the example text with your own to construct your prompt. You can also write a prompt from scratch without using a suggestion.
Pressing the small diagonal arrow underneath the table, Gemini will generate it and move it into the spreadsheet.
How do I access Gemini in Google Sheets?
If you are logged into a Google account with Gemini enabled, you will see the ‘Ask Gemini’ button next to your profile picture in the top-right of the screen. Clicking will open a panel with a box where you can write a prompt related to your sheet.
Is Gemini available in all Google Sheets versions?
Gemini is a paid add-on for anyone with a Google Workspace account.
If you have a Business edition, it costs $24 per month, per user, if you pay monthly, and $20 per month, per user, if you pay annually.
If you have an Enterprise edition, it costs $36 per month, per user, if you pay monthly, and $30 per month, per user, if you pay annually.
This is paid on top of the price of your Google Workspace account:
- Business Starter: $7.20 per user, per month.
- Business Standard: $14.40 per user, per month.
- Business Plus: $21.60 per user, per month.
- Enterprise: Price individual to organization
SEE: Gmail vs Google Workspace: Key Differences for Users & Businesses
Can Gemini convert Google Docs content into Google Sheets?
No, Gemini cannot directly move data across Google Workspace apps. If you have a table in a Google Doc and you want to move it into a Sheet, you can highlight and copy it, click on a cell in the Sheet, and then paste it. If the pasted data appears in a single column, click ‘Data’ and ‘Split Text to Columns’ to split based on a delimiter, such as a comma or space. You could also use ‘Ask Gemini’ to generate a table in the same format as the one in the document by describing its structure.